开发同学有一个需求,如下这张表:
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.数据删除策略,可以选择新建备份表,缩小删除数据范围的方法,一是保存现有数据,二是执行时间有保证。