DATEDIFF函数过滤条件改写经验

1、问题之前分享过在列上套用函数会无法使用普通索引影响性能所以对于此类场景优化经常是想办法去掉函数在实际项目上经常遇到一般是对时间条件计算会套用函数今天主要是datediff函数转换思路的分享原始例子如下selectcount(1)from(selectt1.*,DATEDIFF(DAY, TRUNC(t1.ctime), TRUNC(CURRENT_DATE()))AS dayNum fromtestt1,test2 t2 wheret1.idt2.plan_id)t where t.dayNum IN(1,3)计划计划做成hash join被驱动表全表扫描看语句有过滤性较好的条件t.dayNum IN (1, 3)这里是时间计算而来根据普通索引使用原则需要去掉列上的函数即需要把DATEDIFF(DAY, TRUNC(T1.ctime), TRUNC(CURRENT_DATE())) 转换成T1.ctime匹配计算。这里求当前时间-T1.ctime在(1, 3)范围那么相当于当前时间-(1, 3, 7, 15)T1.ctime意味着T1.ctime与当前值比较差值在1天内3天内即((t1.ctimetrunc(CURRENT_DATE)-interval ‘1’ DAY AND t1.ctimetrunc(CURRENT_DATE)-interval ‘3’ DAY AND t1.ctimetrunc(CURRENT_DATE)-interval ‘2’ DAY)然后为了不受or参数影响改写成union 差值1和3没有存在重复值即可用union all2、改写selectcount(1)from(selectt1.*,DATEDIFF(DAY, TRUNC(t1.ctime), TRUNC(CURRENT_DATE()))AS dayNum fromtestt1,test2 t2 wheret1.idt2.plan_id and(t1.ctimetrunc(CURRENT_DATE)-interval1DAY AND t1.ctimetrunc(CURRENT_DATE)-interval0DAY)union allselectt1.*,DATEDIFF(DAY, TRUNC(t1.ctime), TRUNC(CURRENT_DATE()))AS dayNum fromtestt1,test2 t2 wheret1.idt2.plan_id and(t1.ctimetrunc(CURRENT_DATE)-interval3DAY AND t1.ctimetrunc(CURRENT_DATE)-interval2DAY))t计划原始语句1.3s改写完后0.5s这里模拟的数据较少实际上在项目中的原始语句跑1分钟以上改写完秒级执行完。3、小结还是老话如果套用函数的列条件过滤性较好数据量占总数30%以内即要想办法去掉函数。4、测试数据构造create table test(id int primary key,ctime timestamp,c1 varchar2(20));insert intotestselectlevel,SYSDATE-INTERVAL1SECOND * TRUNC(DBMS_RANDOM.VALUE(-100000,1000000)),A||to_char(round(dbms_random.value(1,1000),0))from dual connect by level800000;commit;create table test2(id int primary key,plan_id int,ctime timestamp,c1 varchar2(20));insert into test2selectlevel,round(dbms_random.value(1,100000),0),SYSDATE-INTERVAL1SECOND * TRUNC(DBMS_RANDOM.VALUE(-100000,1000000)),A||to_char(round(dbms_random.value(1,1000),0))from dual connect by level200000;commit;create index IDX_DM_TEST_CTIME on TEST(CTIME);create index IDX_DM_TEST2_PLAN_ID on TEST2(plan_id);dbms_stats.gather_table_stats(USER,TEST,null,100);dbms_stats.gather_table_stats(USER,TEST2,null,100);