sql调优1
sql语句为：
select count(*) over() TOTALROWCOUNT,
count(*) as ROW_NUMBER,
row_number() over(order by t1.Y_CODE) ROW_NUMBER2,
count(distinct t1.r_id) NUMBER3,
t1.Y_CODE,
t1.Y_NAME
from T1
where (t1.G_TYPE = 'LEVEL_ONE')
and (t1.D_STATUS = 'ACTIVE')
and t1.PUBLICATION_DATE >= TO_DATE('01/23/2013', 'MM/dd/yyyy')
and t1.PUBLICATION_DATE <= TO_DATE('12/31/2013', 'MM/dd/yyyy')
group by t1.Y_CODE, t1.Y_NAME
ORDER BY t1.Y_CODE
执行计划为：
执行时间为75s。
调整如下：
create index ind_t1PUBLICATION_DATE on t1(PUBLICATION_DATE );---创建索引
再次执行，查看执行计划依然为全表扫描。
原因：
由于这里的时间范围太长了，所以oracle认为全表扫描要更快一些，修改sql的内容：
select count(*) over() TOTALROWCOUNT,
count(*) as ROW_NUMBER,
row_number() over(order by t1.Y_CODE) ROW_NUMBER2,
count(distinct t1.r_id) NUMBER3,
t1.Y_CODE,
t1.Y_NAME
from T1
where (t1.G_TYPE = 'LEVEL_ONE')
and (t1.D_STATUS = 'ACTIVE')
and t1.PUBLICATION_DATE >= TO_DATE('10/23/2013', 'MM/dd/yyyy')
and t1.PUBLICATION_DATE <= TO_DATE('12/31/2013', 'MM/dd/yyyy')
group by t1.Y_CODE, t1.Y_NAME
ORDER BY t1.Y_CODE
执行时间为10s。由于这个表有1500w行记录，所以我觉得10s还可以了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24500180/viewspace-1065586/ ，如需转载，请注明出处，否则将追究法律责任。
