基于sql优化，rule的使用小改动大变化

业务部门抱怨报表执行慢，查看了下该报表生成的执行计划：

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=71)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TOAF_ORDEREDPRODUCT' (Cost=18 Card=53 Bytes=901)

3 2 INDEX (RANGE SCAN) OF 'I_COLOURCODE' (NON-UNIQUE) (Cost=1 Card=19766)

4 0 SORT (AGGREGATE)

5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TOAF_ORDEREDPRODUCT' (Cost=18 Card=53 Bytes=901)

6 5 INDEX (RANGE SCAN) OF 'I_COLOURCODE' (NON-UNIQUE) (Cost=1 Card=19766)

7 0 SORT (AGGREGATE)

8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TOAF_ORDEREDPRODUCT' (Cost=18 Card=53 Bytes=901)

9 8 INDEX (RANGE SCAN) OF 'I_COLOURCODE' (NON-UNIQUE) (Cost=1 Card=19766)

10 0 TABLE ACCESS (BY INDEX ROWID) OF 'TSCM_PRODUCT' (Cost=2 Card=1 Bytes=37)

11 10 NESTED LOOPS (Cost=16 Card=1 Bytes=71)

12 11 HASH JOIN (Cost=14 Card=1 Bytes=34)

13 12 TABLE ACCESS (FULL) OF 'TVWS_PRODUCTQTY' (Cost=6 Card=4212 Bytes=67392)

14 12 TABLE ACCESS (FULL) OF 'TSCM_PRODUCTCOLOURSTYLE' (Cost=6 Card=4160 Bytes=74880)

15 11 INDEX (RANGE SCAN) OF 'IDX_TSCM_PRODUCT_1' (NON-UNIQUE) (Cost=1 Card=1)

统计信息
----------------------------------------------------------
8327 recursive calls
0 db block gets
184362743 consistent gets
23 physical reads
180 redo size
170200 bytes sent via SQL*Net to client
2183 bytes received via SQL*Net from client
279 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4160 rows processed

大量的 consistent gets 184362743

性能消耗过大的原因：

TABLE ACCESS (FULL) OF 'TSCM_PRODUCTCOLOURSTYLE' (Cost=6 Card=4160 Bytes=74880)

TABLE ACCESS (FULL) OF 'TVWS_PRODUCTQTY' (Cost=6 Card=4212 Bytes=67392)

2个大表进行的TABLE ACCESS (FULL)

关联字段都存在索引，为何oracle没有基于CBO使用最小性能代价的优化没有想懂。

在sql中添加/*+ RULE */ 重新执行sql：

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TOAF_ORDEREDPRODUCT'

3 2 AND-EQUAL

4 3 INDEX (RANGE SCAN) OF 'I_ORDEREDPRODPRODID' (NON-UNIQUE)

5 3 INDEX (RANGE SCAN) OF 'I_STYLECODE' (NON-UNIQUE)

6 3 INDEX (RANGE SCAN) OF 'I_COLOURCODE' (NON-UNIQUE)

7 0 SORT (AGGREGATE)

8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TOAF_ORDEREDPRODUCT'

9 8 AND-EQUAL

10 9 INDEX (RANGE SCAN) OF 'I_ORDEREDPRODPRODID' (NON-UNIQUE)

11 9 INDEX (RANGE SCAN) OF 'I_STYLECODE' (NON-UNIQUE)

12 9 INDEX (RANGE SCAN) OF 'I_COLOURCODE' (NON-UNIQUE)

13 0 SORT (AGGREGATE)

14 13 TABLE ACCESS (BY INDEX ROWID) OF 'TOAF_ORDEREDPRODUCT'

15 14 AND-EQUAL

16 15 INDEX (RANGE SCAN) OF 'I_ORDEREDPRODPRODID' (NON-UNIQUE)

17 15 INDEX (RANGE SCAN) OF 'I_STYLECODE' (NON-UNIQUE)

18 15 INDEX (RANGE SCAN) OF 'I_COLOURCODE' (NON-UNIQUE)

19 0 TABLE ACCESS (BY INDEX ROWID) OF 'TSCM_PRODUCT'

20 19 NESTED LOOPS

21 20 NESTED LOOPS

22 21 TABLE ACCESS (FULL) OF 'TSCM_PRODUCTCOLOURSTYLE'

23 21 TABLE ACCESS (BY INDEX ROWID) OF 'TVWS_PRODUCTQTY'

24 23 INDEX (RANGE SCAN) OF 'IND_PRODUCTID' (NON-UNIQUE)

25 20 INDEX (RANGE SCAN) OF 'I_PRODUCTID_1' (NON-UNIQUE)

统计信息
----------------------------------------------------------
8332 recursive calls
0 db block gets
8781478 consistent gets
0 physical reads
0 redo size
172229 bytes sent via SQL*Net to client
2183 bytes received via SQL*Net from client
279 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4166 rows processed

consistent gets由原来的184362743 减少到8781478

关联字段也走了索引

TABLE ACCESS (BY INDEX ROWID) OF 'TVWS_PRODUCTQTY'

[@more@]

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

转载于:http://blog.itpub.net/7417660/viewspace-1056981/