海量数据库优化问题

最近处理了接近300万条的测试记录，优化前根据测试，出报表的速度很慢，使用了ORACLE的Explain Plan分析，发现建立的索引失效，ORACLE索引失效是比较常见的问题。有的是数据本身的构成（比如索引列存在大量的重复数据）问题，有的是对索引列施加了运算（比如函数运算）引起的，有的是SQL语句的写法不当造成的。从网上搜索了不少关于优化的文章，解决的问题也多种多样，够学习一段时间了！当然，SQL语句的写法关系也很大，下面从SQL语句入手以一个例子来分析下：

环境：
OS:Windows XP sp-2
CPU:Intel(R) Core2 Duo CPU 2.0GHz
物理内存：2G
ORACLE 10g
PL/SQL 7.0

优化前执行大概需要60s时间：
Select AGE,PERSON,nvl(ROUND((RATIO_TO_REPORT(Sum(Person)) OVER ())*100,2),'') pct From(

Select age ,person
From(
Select '00--16' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) < 16 and district like :disCode Union All
Select '16--25' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 16 And Round(months_between(Sysdate,birthday)/12) < 25 and district like :disCode Union All--16-25
Select '26--35' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 25 And Round(months_between(Sysdate,birthday)/12) < 35 and district like :disCode Union All
Select '36--45' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 35 And Round(months_between(Sysdate,birthday)/12) < 45 and district like :disCode Union All
Select '46--55' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 45 Round(months_between(Sysdate,birthday)/12) < 55 and district like :disCode Union All
Select '55--++' As age,Count(*) As person From cca1 Where Round(months_between(Sysdate,birthday)/12) >= 55 and district like :disCode
)

)
Group By age,person
优化后大概需要1.57s时间：
Select AGE,PERSON,nvl(ROUND((RATIO_TO_REPORT(Sum(Person)) OVER ())*100,2),'') pct From(

Select age ,person
From(
Select '00--16' As age,Count(*) As person From cca1 Where birthday>add_months(Sysdate,-16*12) and district like :disCode Union All
Select '16--25' As age,Count(*) As person From cca1 Where birthday>=add_months(Sysdate,-25*12) And birthday<add_months(Sysdate,-16*12) and district like :disCode Union All--16-25
Select '26--35' As age,Count(*) As person From cca1 Where birthday>=add_months(Sysdate,-35*12) And birthday<add_months(Sysdate,-25*12) and district like :disCode Union All
Select '36--45' As age,Count(*) As person From cca1 Where birthday>=add_months(Sysdate,-45*12) And birthday<add_months(Sysdate,-35*12) and district like :disCode Union All
Select '46--55' As age,Count(*) As person From cca1 Where birthday>=add_months(Sysdate,-55*12) And birthday<add_months(Sysdate,-45*12) and district like :disCode Union All
Select '55--++' As age,Count(*) As person From cca1 Where birthday<add_months(Sysdate,-55*12) and district like :disCode
)

)
Group By age,person

因此，少对索引施加复杂运算可以利用到索引的优势。

此外，索引的like也很容易引起失效，比如 districtCode like '35________'不能利用索引失效，而使用 districtCode like '3501________'却又能使用上索引，根据推测这可能是因为在大量数据中，以第一种方式基本上实施全表搜索，优化器可能不使用索引。