基於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@]