【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
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 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
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 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
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 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
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 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 JoinSQL
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- 【sql調優之執行計劃】query transformerSQLORM
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 【sql調優之執行計劃】獲取執行計劃SQL
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- 【sql調優之執行計劃】temp table transformationSQLORM
- 【sql調優之執行計劃】in相關的operationSQL
- 【sql調優之執行計劃】merge sort joinSQL
- mysql調優之——執行計劃explainMySqlAI
- 使用hint改變執行計劃
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- Oracle調優之看懂Oracle執行計劃Oracle
- 加hint改變執行計劃訪問順序優化sql優化SQL
- 【sql調優之執行計劃】merge join cartesian and buffer sortSQL
- 使用Oracle Hint提示來更改執行計劃Oracle
- 【sql調優之執行計劃】merge semi join and merge anti joinSQL
- 【sql調優之執行計劃】nested loops join and nested loop join outerSQLOOP
- 使用hint來調優sql語句SQL
- SQL調優（SQL TUNING）並行查詢提示（Hints）之pq_distribute的使用SQL並行
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- 使用Oracle Hint提示來更改執行計劃 ZTOracle
- 使用leading(,)優化sql執行計劃優化SQL
- SQL Server調優系列玩轉篇三（利用索引提示（Hint）引導語句最大優化執行）SQLServer索引優化
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL
- 建立索引調整sql的執行計劃索引SQL
- oracle筆記整理14——效能調優之oracle執行計劃Oracle筆記
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- Oracle中SQL調優（SQL TUNING）之最權威獲取SQL執行計劃大全OracleSQL
- 轉摘＿使用leading(,)優化sql執行計劃優化SQL
- 使用sql profile固定執行計劃SQL
- 控制執行計劃之-SQL Profile（一）SQL
- sql 執行計劃SQL