【sql調優之執行計劃】使用hint(三)Hints for Query Transformations

版本：9208或1024

Hints for Query Transformations

查詢轉換的hints

下面列出來的這些hint建議進行sql查詢轉換

No_query_transformation(10g 增加)

Use_concat

No_expand

Rewrite

Expand_gset_to_union

Norewrite

Merge

No_merge

Star_transformation

Fact

No_fact

No_query_transformation(10g)

這個引數告訴優化器跳過查詢轉換，但是不限制or，檢視合併，子查詢unnesting，星型轉換，物化檢視重寫，句法為: /*+ No_query_transformation */

Use_concat

使用use_concat hint用來是where後面的or條件轉換為union all的集合操作，一般來說，這個轉換隻在使用concatenation的cost要比其他的低時使用。

No_expand

這個hint阻止優化器對or或者in-list條件進行擴充套件。

示例：

SQL> select /*+ */* from t_policy a

2 where a.policy_id = 123

3 or a.send_code = '1111111111';

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=17 Bytes=8058

)

1 0 CONCATENATION

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_POLICY' (Cost=1 Card

=1 Bytes=474)

3 2 INDEX (RANGE SCAN) OF 'IDX_POLICY__HEAD_SEND' (NON-UNI

QUE) (Cost=2 Card=1)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_POLICY' (Cost=1 Card

=1 Bytes=474)

5 4 INDEX (UNIQUE SCAN) OF 'PK_T_POLICY' (UNIQUE) (Cost=2

Card=1)

SQL> select /*+ NO_EXPAND */* from t_policy a

2 where a.policy_id = 123

3 or a.send_code = '1111111111';

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=19 Card=17 Bytes=805

8)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_POLICY' (Cost=19 Card=

17 Bytes=8058)

2 1 BITMAP CONVERSION (TO ROWIDS)

3 2 BITMAP OR

4 3 BITMAP CONVERSION (FROM ROWIDS)

5 4 INDEX (RANGE SCAN) OF 'PK_T_POLICY' (UNIQUE) (Cost

=2)

6 3 BITMAP CONVERSION (FROM ROWIDS)

7 6 SORT (ORDER BY)

8 7 INDEX (RANGE SCAN) OF 'IDX_POLICY__HEAD_SEND' (N

ON-UNIQUE) (Cost=4)

SQL>

Rewrite和 no_rewrite

用來選擇使用物化檢視重寫或者不適用物化檢視重寫

Merge和No_merge

示例（版本1024）

SQL> select /*+ */

2 a.empno

3 from scott.emp a,

4 (select b.deptno, sum(b.sal) sal from scott.emp b group by b.deptno) bb

5 where a.deptno = bb.deptno;

Execution Plan

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

Plan hash value: 269884559

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

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

| 0 | SELECT STATEMENT | | 14 | 280 | 6 (34)| 00:00:01 |

|* 1 | HASH JOIN | | 14 | 280 | 6 (34)| 00:00:01 |

| 2 | VIEW | | 3 | 39 | 3 (34)| 00:00:01 |

| 3 | HASH GROUP BY | | 3 | 24 | 3 (34)| 00:00:01 |

| 4 | TABLE ACCESS FULL| EMP | 14 | 112 | 2 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | EMP | 13 | 91 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."DEPTNO"="BB"."DEPTNO")

5 - filter("A"."DEPTNO" IS NOT NULL)

SQL>

使用merge合併：

SQL> select /*+ merge(bb)*/

2 a.empno

3 from scott.emp a,

4 (select b.deptno, sum(b.sal) sal from scott.emp b group by b.deptno) bb

5 where a.deptno = bb.deptno;

Execution Plan

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

Plan hash value: 4115741206

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

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

| 0 | SELECT STATEMENT | | 28 | 616 | 6 (34)| 00:00:01 |

| 1 | HASH GROUP BY | | 28 | 616 | 6 (34)| 00:00:01 |

|* 2 | HASH JOIN | | 66 | 1452 | 5 (20)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| EMP | 13 | 247 | 2 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL| EMP | 13 | 39 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("A"."DEPTNO"="B"."DEPTNO")

3 - filter("A"."DEPTNO" IS NOT NULL)

4 - filter("B"."DEPTNO" IS NOT NULL)

SQL>

上面可以看到，使用了merge提示以後強制進行了檢視合併，延遲了group by的時間，注意到兩個執行計劃中的rows和bytes的數量。這在有些時候有些用處，比如說，可以通過關聯條件來減少做group by的資料行，這個需要結合具體情況來看。

No_merge則是禁用一些檢視的合併

Star_transformation和No_Star_transformation

使用星型查詢轉換和不使用星型查詢轉換的hint。

Fact和no_fact

結合星型轉換一起使用。

Unnest和no_unnest

子查詢非巢狀和巢狀的提示。如下示例：

SQL> select a.owner

2 from test.t_test_clusterfactor1 a

3 where a.object_id in

4 (select /*+ unnest*/

5 b.object_id

6 from test.t_test_clusterfactor2 b, test.t_test_clusterfactor3 c

7 where c.object_id = b.object_id);

Execution Plan

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

Plan hash value: 578563338

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

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

| 0 | SELECT STATEMENT | | 1 | 22 | | 216 (32)| 00:00:01 |

|* 1 | HASH JOIN SEMI | | 1 | 22 | 1136K| 216 (32)| 00:00:01 |

| 2 | TABLE ACCESS FULL | T_TEST_CLUSTERFACTOR1 | 55006 | 483K| | 94 (19)| 00:00:01 |

| 3 | VIEW | VW_NSO_1 | 55005 | 698K| | 57 (51)| 00:00:01 |

|* 4 | HASH JOIN | | 55005 | 537K| | 57 (51)| 00:00:01 |

| 5 | INDEX FAST FULL SCAN| IND_TEST_CLUSTERFACTOR2 | 55007 | 268K| | 22 (37)| 00:00:01 |

| 6 | INDEX FAST FULL SCAN| IND_TEST_CLUSTERFACTOR3 | 55010 | 268K| | 22 (37)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."OBJECT_ID"="$nso_col_1")

4 - access("C"."OBJECT_ID"="B"."OBJECT_ID")

SQL>

SQL> select a.owner

2 from test.t_test_clusterfactor1 a

3 where a.object_id in

4 (select /*+ no_unnest*/

5 b.object_id

6 from test.t_test_clusterfactor2 b, test.t_test_clusterfactor3 c

7 where c.object_id = b.object_id);

Execution Plan

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

Plan hash value: 2597140566

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

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

| 0 | SELECT STATEMENT | | 55006 | 483K| 97 (19)| 00:00:01 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL| T_TEST_CLUSTERFACTOR1 | 55006 | 483K| 95 (19)| 00:00:01 |

| 3 | NESTED LOOPS | | 1 | 10 | 2 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR3 | 1 | 5 | 1 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR2 | 1 | 5 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "TEST"."T_TEST_CLUSTERFACTOR3"

"C","TEST"."T_TEST_CLUSTERFACTOR2" "B" WHERE "C"."OBJECT_ID"="B"."OBJECT_ID" AND

"B"."OBJECT_ID"=:B1 AND "C"."OBJECT_ID"=:B2))

4 - access("C"."OBJECT_ID"=:B1)

5 - access("C"."OBJECT_ID"="B"."OBJECT_ID")

filter("B"."OBJECT_ID"=:B1)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-672998/，如需轉載，請註明出處，否則將追究法律責任。

【sql調優之執行計劃】使用hint(三)Hints for Query Transformations

相關文章