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

时间: 2024-10-16 01:26:38

TO_DATE函数索引报错ORA-01743的相关文章

mbstowcs-高手指教,为什么这个简单的函数会报错呢??

问题描述 高手指教,为什么这个简单的函数会报错呢?? #include #include #include using namespace std; wstring w2chs3(const char s1) { size_t len = strlen(s1); // wchar_t *ws2 = new wchar_t[len]; wchar_t *ws2 = (wchar_t)malloc(len*sizeof(wchar_t)); unique_ptr wstr(ws2); mbstowc

android-新手求助:安卓sql查找的函数,报错Nullpointerexception

问题描述 新手求助:安卓sql查找的函数,报错Nullpointerexception 代码如下 public User search(String username){ SQLiteDatabase sdb = helper.getReadableDatabase(); Cursor cursor =sdb.rawQuery("select * from user where username=?", new String[]{username}); if(cursor.moveTo

c-这个非常简单的代码,为啥用了函数就报错呢

问题描述 这个非常简单的代码,为啥用了函数就报错呢 题目是:输入n,在输入n个数<(2∧31-1)求这n个数的平方和 我的代码: #include #include #include #include #include using namespace std; long long sum(long long data,int n) { long long ans; ans=0; for(int i=0;i<n;i++) ans+=data[i]*data[i]; return ans; } i

delphi调用HttpQueryInfo函数编译报错,说参数类型不符合

问题描述 delphi调用HttpQueryInfo函数编译报错,说参数类型不符合 procedure Get(url,heads: string;res: TStream); var hInt,hUrl:HINTERNET; buffer:PChar; dwRead:cardinal; hBuf:PChar; flag:Boolean; len,index:Integer; var value: DWORD; begin GetMem(buffer, 65536); GetMem(hBuf,

java-Java函数substring()报错

问题描述 Java函数substring()报错 解决方案 substring是String类的方法,当然会报编译错误了.你可以用自动提示ctrl+1就能知道错误原因了. 看你是想用字符数组a来创建str,可以直接用: String str = new String(a); 解决方案二: a是字符数组,没有substring这个方法,你是不是想获取s的字串? 解决方案三: a是一个数组,不难这样玩儿,substring属于String. 解决方案四: 1.将光标移到小红叉的地方,能看到错误的描述

hp函数setcookie()报错:Warning: Cannot modify header

快要下班的时候,看到php讨论学习群中有朋友说设置cookie的时候.向他要了代码看了原因!报错 Warning: Cannot modify header information – headers already sent by (output started at cookie1.php:4) in cookie1.php on line 5 <?php ob_start(); setcookie("username","宋岩宾",time()+3600

android-构造函数intent报错 undefined

问题描述 构造函数intent报错 undefined 使用fragment中有一个下拉列表,我想在选择下拉列表项目时启动一个新activity.结果报错: The constructor Intent(UserHomeActivity Class) is undefined UserHomeActivity.java /SwipeyTabs/src/com/recscores/android line 28 Java Problem 实现代码: public class UserHomeAct

vs2012配置opencv后,函数报错,求大神解释

问题描述 vs2012配置opencv后,函数报错,求大神解释 我在配置好opencv2.4.10之后,找了个程序测试一下,但是程序中所有的opencv库中的函数都报错,说是为定义的标示符,但是opencv的库函数我已经链接输入了.搞了好久,求大神解答额 解决方案 opencv的lib库是否也用vs2012编译的,其次,link的时候路径等是否设置争取

SQL 2000自定义函数调用GETDATE()报错

在SQL 2000数据库里的自定义函数中调用GETDATE()时,结果编译时报错,提示错误如下:Invalid use of 'getdate' within a function 我给个测试例子如下所示,   --================================================================================================ -- Author : Kerry -- CreateDate : 2011-03-23