TO_DATE函数索引报错ORA-01743

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> TO_DATE函数索引报错ORA-01743

开发同学有一个需求,如下这张表:

CREATE TABLE TBL_EFFDATE (ROUTID NUMBER(20,0) NOT NULL, EFFDTE CHAR(7), EDDATE CHAR(7), ICID CHAR(1), FREQ CHAR(7) );

其中EFFDTE保存的是DDMONYY格式的日期,由于表之前未有归档机制,因此产生了许多垃圾数据,现在需要根据EFFDTE删除16年以前的所有数据,表的数据量在百万级,16年以前的占了绝大部分。

对于这需求来说,SQL的条件很明确,就是根据EFFDTE来做过滤删除,这里日期字段EFFDTE是字符型,不是DATE型,因此就不能使用判断符直接操作。很容易想到的是使用如下SQL: 
TO_DATE函数索引报错ORA-01743
鉴于数据量比较大,需要使用索引,那么很容易想到的是建立to_date(effdate,’DDMONYY’)的一个函数索引, 
TO_DATE函数索引报错ORA-01743
创建索引报错了,ORA-01743, 
TO_DATE函数索引报错ORA-01743
提示:“只能对纯粹的函数创建索引,SQL表达式不能使用任何依赖于当前session状态的信息”。从这SQL看,没有使用SYSDATE、USER、USERENV()这些函数,为什么还提示这错误?

TOM的书中其实给出了关于ORA-01743的原因,

the YYYY format will return May 1, in June it will return June 1, and so on. It turns out that TO_DATE, when used with YYYY, is not deterministic! That is why the index cannot be created: it would only work correctly in the month you created it in (or insert/updated a row in). So, it is due to the user environment, which includes the current date itself. To use TO_DATE in a function-based index, you must use a date format that is unambiguous and deterministic—regardless of what day it is currently.

原因就是TO_DATE函数中使用了表示年份的YY,对于格式相同的输入,返回的则是不同的结果,有些抽象,结合例子看下,假设今天是1月份,则如下SQL返回的是2010年1月1日(1月1日是因为未指定月日,则做了类似trunc的操作): 
TO_DATE函数索引报错ORA-01743
假设今天是2月份,则如下SQL返回的是2010年2月1日: 
TO_DATE函数索引报错ORA-01743
之所以是结果不同,是因为TO_DATE使用了SYSDATE作为基准来输出的日期,因此SQL其实还是基于了session,所以TO_DATE函数索引会有ORA-01743。

如何解这个问题,换种思路,现在要删除16年以前的数据,或者说是保存16年的数据,那么DDMONYY的YY其实是一个定值,可以创建这个索引, 
TO_DATE函数索引报错ORA-01743
现在用where substr(eddate,-2)=’16’作为条件时就可以用索引了, 
TO_DATE函数索引报错ORA-01743

但如果是使用delete from tbl_effdate直接删除,就可能不是很合适了,一是数据直接删除,万一需要再用,就需要其他备份恢复手段了,另一方面,这要遍历非16的所有年份来做,比较LOW,可以这么做:

create table tbl_effdate_16 as select * from tbl_effdate where substr(eddate,-2)=’16’;

rename tbl_effdate to tbl_effdate_p;

rename tbl_effdate_16 to tbl_effdate_16;

好处就是,所有数据未真正删除,还可直接恢复,执行时间基本可以控制在秒级。

总结: 
1.TO_DATE函数索引的创建是有前提条件,不能依赖现有的session,像YY这种格式,隐含依赖了session,所以还是会报错。 
2.数据删除策略,可以选择新建备份表,缩小删除数据范围的方法,一是保存现有数据,二是执行时间有保证。

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> TO_DATE函数索引报错ORA-01743


 上一篇
alter table新增字段操作究竟有何影响?(上篇) alter table新增字段操作究竟有何影响?(上篇)
很久以前就有个疑问,见过一些表设计时会留出几个reverse的字段,目的是为了以后扩展,但此时设计的字段类型、长度等都是预计的,未来是否可用,不好说,那为什么会这么做呢?可能的原因是:“我现在设定好字段,需要的时候直接用就行了,不需要新增字
下一篇 
sosi脚本改造 sosi脚本改造
  最近有一个应用需求,就是在一个shell脚本中,通过一些逻辑得到一系列表名,然后作为参数传给著名的sosi脚本,产出物是每张表对应的一个分析结果文件。 sosi原始脚本中对于接收表名是用类似如下的语法: COLUMN 1 new_va