TO_DATE函数索引报错ORA-01743
来源:互联网 发布:c语言初学者百题大战 编辑:程序博客网 时间:2024/05/20 04:48
开发同学有一个需求,如下这张表:
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.数据删除策略,可以选择新建备份表,缩小删除数据范围的方法,一是保存现有数据,二是执行时间有保证。
- TO_DATE函数索引报错ORA-01743
- 使用to_date创建函数索引的时候经常会遇到ORA-01743错误
- ORA-01502: 索引报错问题
- ora-29861 全文索引异常报错
- maximo报错:ORA-20000,DRG-10599,中文索引错误
- trouble shooting:ORA-01502:失效索引导致数据库报错
- TO_DATE()函数
- TO_DATE()函数?
- to_date函数
- TO_DATE函数
- 建立索引失败,删除索引报错ora-08104错误,测试
- Ora-03113 报错
- 报错ora-32035
- ORA-3136报错
- 数据库报错ORA
- ORA-报错解释
- ORA-09817,ORA-01075报错解决方法
- 转 -- ORA-02068,ORA-03113 报错
- 如何JS Math.random方法取得真在平均概率
- 用background-image:url();属性设置背景图片,为什么总是显示不出来?
- android studio Session 'app': Error Installing APK解决方案
- clamp.js 的使用---超出省略 (翻译)
- 13个有用的渗透测试资源博客
- TO_DATE函数索引报错ORA-01743
- java-基础-虚拟机jvm
- unity lightmapping “黑丝” 错误
- const的用法,特别是用在函数后面
- HTML5本地存储——Web SQL Database
- c++中的前向声明
- Mysql—explain的参数详解及用法
- 如何快速入门Windows编程
- Java的多线程:Thread类的Join()方法