SQL优化实例

示例一：hint滥用

select /*+ ordered use_nl(b a c d)*/
       *
  from b,a,c,d
 where b.col1 = a.col1
   and a.col2 = c.col2
   and a.col3 = d.col3
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt <   to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

分析：
1）表之间的关系：
2）数据量，索引情况   b 200万，a 200万 ，过滤后50行，c 200行， d 1000行
3）优化器模式  RBO  or  CBO
4）不同的优化器模式采用不用的优化策略

优化措施：---》
对于CBO，把添加的hint去掉就可以了，Oracle会根据数据量和索引情况生成高效的执行计划
select *
  from b,a,c,d
 where b.col1 = a.col1
   and a.col2 = c.col2
   and a.col3 = d.col3
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt <   to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

--------------------------------------------------------------

示例二：多余的表或列访问

select a.col1,b.col2,a.dt
  from a,c,b
 where a.col1 =  c.col1
   and c.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

分析：
1）表之间的关系 a,c 1:1  c,b 1:1
2）select里没有出现c表的列，此时可以考虑c表是否是必须的
3）如果分析表之间的关系及数据的特点发现c表不是必须的，可以去掉c表的访问
4）说明：当然也有些情况下c表可能是必须的，具体情况具体分析

优化措施：--》
如果经过分析发现c表的访问确实是多余的，那么上述语句可以改为：
select a.col1,b.col2,a.dt
  from a,b
 where a.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

select col1,col2,dt
  from
(
select a.*,b.col2,a.rownum rn
  from a,b
 where a.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')
   order by a.col1
)
where rn between 10 and 20

分析：
1）内层查询使用a.*，而外层只需要a的col1这一列，所以把内层查询的c.*具体化，减少资源和时间的消耗

优化措施：--》
select col1,col2,dt
  from
(
select a.col1,b.col2,a.rownum rn
  from a,b
 where a.col1 = b.col1
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')
   order by a.col1
)
where rn between 10 and 20

--------------------------------------------------------

示例三：索引使用的灵活处理

select a.dt,b.timezone,a.col1,a.col2,a.col3
  from a,b
 where a.col1 = b.col1
   and a.dt   >= to_date('20010101 00:00:00','YYYYMMDD 24:MI:SS') - b.timezone
   and a.dt   < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS') -b.timezone
保证同一时间段统计

分析：
1）表之间的关系：
2）数据量，索引情况 a 100万  b 200    a.dt有索引， a.col1，b.col1有索引
3）条件中a.dt列的比较范围是变化的，所以导致dt列的索引无法使用
4）分析b.timezone的数值范围，在-12：00～12：00之间
5）为了使用a.dt列的索引，考虑可以把a.dt的范围条件放大到固定值，然后再对结果集进行过滤。

优化措施：--》

select *
from
(
select a.dt,b.timezone,a.col1,a.col2,a.col3
  from a,b
 where a.col1 = b.col1
   and a.dt   >= to_date('20010101 00:00:00','YYYYMMDD 24:MI:SS') -1
   and a.dt  < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')+1
) ab

   where ab.dt   >= to_date('20010101 00:00:00','YYYYMMDD 24:MI:SS') -ab.timezone
   and ab.dt  < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')- ab.timezone

----------------------------------------------------------

示例四：有没有合适的索引可用

select a.col1,a.col2,a.col3,b.col4,c.col5
  from a,b,c
 where a.col1 = b.col1
   and a.col2 = b.col2
   and a.col3 = c.col3
   and a.dt  >= to_date('20010101 00:00:00','YYYYMMDD HH24:MI:SS')
   and a.dt < to_date('20010102 00:00:00','YYYYMMDD HH24:MI:SS')

分析：
1）表之间的关系
2）数据量，索引情况   a.dt, b.col1, b.col2, c.col3列分别有索引
3）但是单独使用b.col1列的索引或者b.col2列的索引，数据筛选效果都不好
4）这样考虑为b表的col1,col2创建联合索引

优化措施：--》
为b表的col1,col2列创建联合索引

------------------------------------------------------

示例五：不必要的外连接

select a.col1,b.col2,b.col3
  from a,b
 where a.col1 = b.col2(+)
   and b.col3 > 1000

分析：
1）条件b.col3>1000意味着原本使用外连接多出来的列要被排除掉，所以此处外连接是不需要的

优化措施：--》

select a.col1,b.col2,b.col3
  from a,b
 where a.col1 = b.col2
   and b.col3 > 1000

-----------------------------------------------------

示例六：with子句的使用

select ab.col1,ab.col3,c.col2
  from c,
(
select a.col1,b.col3
  from a,b
 where a.col1 = b.col1
   and b.col3 > 1000
) ab
where c.col1 = ab.col1
union
select ab.col1,ab.col3,d.col2
  from d,
(
select a.col1,b.col3
  from a,b
 where a.col1 = b.col1
   and b.col3 > 1000
) ab
where d.col1 = ab.col1

分析：
1）该语句中出现了多处相同的子查询，可以使用with子句来进行简化，减少数据访问，提高效率

优化措施：--》
with ab as
(
select a.col1,b.col3
  from a,b
 where a.col1 = b.col1
   and b.col3 > 1000
)
select ab.col1,ab.col3,c.col2
  from c,ab
where c.col1 = ab.col1
union
select ab.col1,ab.col3,d.col2
  from d,ab
where d.col1 = ab.col1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/750077/viewspace-2125872/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/750077/viewspace-2125872/