(2)
1. 通常的调整方法
-- 通常来说都先把update语句中的where子句提取出来进行调整。
A．调整前的执行过程：
SQL> select *from pa
2 where exists (select 1
3 from p,c
4 where p.id=pa.id and p.cid=c.cid
5 )
6 and not exists (select 1
7 from d
8 where id=pa.id)
9 /
已选择200000行。
已用时间: 00: 00: 36.06
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15347 Card=2500 Byte
s=60000)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=2500 Bytes=60
000)
3 1 NESTED LOOPS (Cost=4 Card=1 Bytes=36)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'P' (Cost=3 Card=1 By
tes=24)
5 4 INDEX (UNIQUE SCAN) OF 'PK_P' (UNIQUE) (Cost=2 Card=
1)
6 3 INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1
Bytes=12)
7 1 INDEX (UNIQUE SCAN) OF 'PK_D' (UNIQUE) (Cost=2 Card=1 By
tes=12)
Statistics
----------------------------------------------------------
340 recursive calls
0 db block gets
6517005 consistent gets
7 physical reads
0 redo size
6182538 bytes sent via SQL*Net to client
546928 bytes received via SQL*Net from client
13335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200000 rows processed
B．对sql语句进行加提示调整
-- 鉴于用于连接的字段均存在NOT NULL约束,可以考虑使用hash_aj提示调整查询
-- 注意:如果d.id/pa.id没有not null约束,hash_aj将不起作用.可以加d.id is not null/pa.id is not null条件避免这种情况
SQL>
SQL> select *from pa
2 where exists (select 1
3 from p,c
4 where p.id=pa.id and p.cid=c.cid
5 )
6 and id not in (select /*+hash_aj*/ id
7 from d)
8 /
已选择200000行。
已用时间: 00: 00: 32.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=963 Card=1 Bytes=36)
1 0 FILTER
2 1 HASH JOIN (ANTI) (Cost=959 Card=1 Bytes=36)
3 2 TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=50000 Bytes
=1200000)
4 2 INDEX (FAST FULL SCAN) OF 'PK_D' (UNIQUE) (Cost=284 Ca
rd=1000000 Bytes=12000000)
5 1 NESTED LOOPS (Cost=4 Card=1 Bytes=36)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'P' (Cost=3 Card=1 By
tes=24)
7 6 INDEX (UNIQUE SCAN) OF 'PK_P' (UNIQUE) (Cost=2 Card=
1)
8 5 INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1
Bytes=12)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2906545 consistent gets
3 physical reads
0 redo size
6275882 bytes sent via SQL*Net to client
546928 bytes received via SQL*Net from client
13335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200000 rows processed
SQL>
虽然从执行时间时间上来看，不加提示和加提示分别为00: 00: 36.06和00: 00: 32.00，相差不大，但从统计信息上来看，由原来的6517005 consistent gets变为2906545 consistent gets，调整收到一定效果。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21645/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/6906/viewspace-21645/
