于节点2上发现一个sql，其结构如下：
select distinct t.attr, t.item
from a t
where attr = :1
and (pro = :2 or exists
(select 1
from b hps
where hps.pro = :3
and t.pro = hps.sub_pro))
鉴于以往的经验，一开始就觉得子查询中的连接谓词or有问题。
注:
表a有50多万条记录，attr和pro上分别有索引；表b有10万条记录，pro上有单独索引，同时(pro,sub_pro)组成unique索引；
同时表a上���attr,pro字段的分布情况如下,对于表a，pro字段的选择性比attr高出了很多
SQL> select count(distinct pro),count(distinct attr) from a;
COUNT(DISTINCT pro) COUNT(DISTINCT attr)
------------------------- ---------------------------
164067 716
先通过set autotrace traceonly查看一把其执行计划和consistent gets
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63 | 945 | 83 (2)| 00:00:01 |
| 1 | HASH UNIQUE | | 63 | 945 | 83 (2)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| a | 1268 | 19020 | 82 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | idx_a_attr | 1268 | | 8 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | idx_b_un_pro_sub | 1 | 12 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("pro"=TO_NUMBER(:B) OR EXISTS (SELECT 0 FROM "b" "HPS" WHERE
"HPS"."SUB_pro"=:B1 AND "HPS"."pro"=TO_NUMBER(:C)))
4 - access("attr"=TO_NUMBER(:A))
5 - access("HPS"."pro"=TO_NUMBER(:C) AND "HPS"."SUB_pro"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4690 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从执行计划看出，访问表a的时候选择了attr上的索引，实际效果不佳，逻辑读有4690；
通过等价改写后的sql如下，去除了谓词or，改用了union
select distinct t.attr, t.item
from a t
where attr = :a
and pro = :b
union
select distinct t.attr, t.item
from a t
where attr = :a
and exists
(select 1
from b hps
where hps.pro = :c
and t.pro = hps.sub_pro)
改写后的sql的���行计划如下
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 150 | 16 (75)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 150 | 16 (75)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| a | 1 | 15 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | idx_a_pro | 3 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| a | 1 | 15 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 5 | 135 | 10 (10)| 00:00:01 |
| 7 | SORT UNIQUE | | 5 | 60 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | idx_b_un_pro_sub | 5 | 60 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | idx_a_pro | 3 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("attr"=TO_NUMBER(:A))
4 - access("pro"=TO_NUMBER(:B))
5 - filter("attr"=TO_NUMBER(:A))
8 - access("HPS"."pro"=TO_NUMBER(:C))
9 - access("T"."pro"="HPS"."SUB_pro")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
访问表a时选择了pro上的索引，虽然增加了一次访问次数，但是逻辑读却下降到了24.
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-1 21:36:25 |
赞(
操作
