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(effdate,’DDMONYY’)的一个函数索引，

这里写图片描述

创建索引报错了，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的操作)：

这里写图片描述

假设今天是2月份，则如下SQL返回的是2010年2月1日：

这里写图片描述

之所以是结果不同，是因为TO_DATE使用了SYSDATE作为基准来输出的日期，因此SQL其实还是基于了session，所以TO_DATE函数索引会有ORA-01743。

如何解这个问题，换种思路，现在要删除16年以前的数据，或者说是保存16年的数据，那么DDMONYY的YY其实是一个定值，可以创建这个索引，

这里写图片描述

现在用where substr(eddate,-2)=’16’作为条件时就可以用索引了，

这里写图片描述

但如果是使用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.数据删除策略，可以选择新建备份表，缩小删除数据范围的方法，一是保存现有数据，二是执行时间有保证。