晚上到公司加班，需要生成一张中间表数据，脚本如下：

call P_DROP_TABLE_IF_EXIST('TB_FT_lan_ofr', 'bdw');--- 删除的存储过程

Create Table TB_FT_lan_ofr as

select prd_inst_id,ofr_inst_id,crt_date,OFR_INST_STAS_ID,exp_date,eff_date from TB_FT_HOLIDAY_2013_ofr_bak

where (to_char(exp_date,'yyyymmdd')>='20151231' /*and exp_date is not null*/)

and to_char(eff_date,'yyyymmdd')<='20160101'

and ofr_detail_type_id='A1' and prd_id in (1307,1309,1310) and OFR_NET_NUM=1

and OFR_INST_STAS_ID not in(1098);

create index idx_lan_ofr_old on TB_FT_lan_ofr(prd_inst_id,ofr_inst_id);

语句硬是跑了40分钟还在执行中，时间被耗光了，本来想偷个懒，就没想到优化语句，但是如果继续等待，

晚上睡觉就遥遥无期啦。还是看下能不能优化吧。

TB_FT_HOLIDAY_2013_ofr_bak是个比较大的表，大概四百万条记录，查看执行计划，原谅我是用F5查看的：

执行计划走索引了，进一步查看索引列prd_id ，重复度很高，索引的IO次数远大于全表扫描的IO的次数，明显不适合建立索引。我删除索引，执行计划走上全表扫描了。

这里还有另外一个优化点：where提交的to_char函数，会造成递归调用，于是改下类似eff_date <=date'2016-01-01' 。

改写后的语句如下：

先删除索引：drop index idx_holiday_2014_ofr

call P_DROP_TABLE_IF_EXIST('TB_FT_lan_ofr_test', 'bdw');

Create Table TB_FT_lan_ofr_test as

select prd_inst_id,ofr_inst_id,crt_date,OFR_INST_STAS_ID,exp_date,eff_date from TB_FT_HOLIDAY_2013_ofr_bak

where exp_date>=date'2015-12-31'

and eff_date <=date'2016-01-01'---- 修改了语句

and ofr_detail_type_id='A1' and prd_id in (1307,1309,1310) and OFR_NET_NUM=1

and OFR_INST_STAS_ID not in(1098);

执行计划走上全表扫描了。

原先执行需要40分钟的语句，改写后只需要18秒就搞定了。

ITPub博客

一条SQL建表语句的优化