溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章給大家介紹oracle hint有效范圍是什么，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。
hint的范圍
parameter hints對整個sql有效率，其它hints只對單個查詢塊起作用，要在查詢塊內指定hints
當子查詢，內聯view時候會有多個查詢塊，要為每個查詢塊 加hint控制該查詢塊（hint有效范圍控制在查詢塊內）
SQL> create table t1 (a int,b varchar2(10));
Table created.
SQL> create table t2 (a2 int,b2 varchar2(10));
Table created.
SQL>
SQL> insert into t1 values(1,'a');
1 row created.
SQL> insert into t2 values(1,'b');
1 row created.
SQL> commit;
Commit complete.
SQL> create index t1_id on t1(a);
Index created.
SQL> create index t2_id on t2(a2);
Index created.
SQL> execute dbms_stats.gather_table_stats('XH','T1');
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats('XH','T2');
PL/SQL procedure successfully completed.
SQL> set autotrace trace exp
SQL> select * from t1 where a=(select a2 from t2 where a2=1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 3305116341
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_ID | 1 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T2_ID | 1 | 3 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"= (SELECT "A2" FROM "T2" "T2" WHERE "A2"=1))
3 - access("A2"=1)
SQL> select /*+full(t1)*/ * from t1 where a=(select a2 from t2 where a2=1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 1681039550
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| T2_ID | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"= (SELECT "A2" FROM "T2" "T2" WHERE "A2"=1))
2 - access("A2"=1)
全局hint使用.引用包含在其他查詢塊中的對象(引用的查詢塊已經有別名才行,查詢塊別名 不是表別名)
SQL> select * from t1 ts where a=(select /*+full(t2) full(ts.t1)*/a2 from t2 where a2=1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 2910250514
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_ID | 1 | | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T2 | 1 | 3 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"= (SELECT /*+ FULL ("T2") */ "A2" FROM "T2" "T2" WHERE
"A2"=1))
3 - filter("A2"=1)
SQL> with
2 t1_t as (select * from t1)
3 select t1_t.a,t2.b2 from t2,t1_t where t2.a2=t1_t.a
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 978323357
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 8 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | T1_ID | 1 | 3 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_ID | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."A2"="T1"."A")
SQL> with
2 t1_t as (select * from t1)
3 select/*+full(t2) full(t1_t.t1)*/ t1_t.a,t2.b2 from t2,t1_t where t2.a2=t1_t.a
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 8 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."A2"="T1"."A")
SQL>
假如子查詢沒有別名，可以制作一個別名為子查詢(用qb_name hint制作),10g才能用qb_name
SQL> with
2 t1_t as (select /*+qb_name(sq)*/ * from t1)
3 select /*+qb_name(sq_t) full(@sq t1) full(@sq_t t2)*/ t1_t.a,t2.b2 from t2,t1_t where t2.a2=t1_t.a
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 8 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."A2"="T1"."A")
SQL> SQL> select /*+qb_name(sq) full(@sq t1) full(@sq_t t2)*/* from t1 where a=(select /*+qb_name(sq_t)*/ a2 from t2 where a2=1);
Execution Plan
----------------------------------------------------------
Plan hash value: 1484901111
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"= (SELECT /*+ QB_NAME ("SQ_T") FULL ("T2") */ "A2"
FROM "T2" "T2" WHERE "A2"=1))
2 - filter("A2"=1)
SQL> SQL> select /*+qb_name(sq)*/* from t1 where a=(select /*+qb_name(sq_t) full(@sq t1) full(@sq_t t2)*/ a2 from t2 where a2=1);
Execution Plan
----------------------------------------------------------
Plan hash value: 1484901111
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"= (SELECT /*+ QB_NAME ("SQ_T") FULL ("T2") */ "A2"
FROM "T2" "T2" WHERE "A2"=1))
2 - filter("A2"=1)
使用query optimizer生成的別名
SQL> explain plan set statement_id='xh' for select * from t1 where a=(select a2 from t2 where a2=1);
Explained.
SQL> select * from table(dbms_xplan.display(null,'xh','basic +alias'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3305116341
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 |
| 2 | INDEX RANGE SCAN | T1_ID |
| 3 | INDEX RANGE SCAN | T2_ID |
---------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$2 / T2@SEL$2
17 rows selected.
SEL$為查詢塊前綴名（sel=select,cri$=create index,del$=delete,ins$=insert,misc$=lock table或多類語句,mrg$=merge,set$=union&minus,upd$=update）
順序是按sql語句parse階段查詢塊出現位置(左-右)，所以sel$1表示 select t1那個 查詢塊，sel$2 表示select t2那個查詢塊
SQL> select * from t1 where a=(select /*+full(@sel$2 t2) full(@sel$1 t1)*/a2 from t2 where a2=1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 1484901111
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"= (SELECT /*+ FULL ("T2") */ "A2" FROM "T2" "T2" WHERE
"A2"=1))
2 - filter("A2"=1)
另外表有別名的話hint中要用別名
SQL> select * from t1 ts where a=(select /*+full(@sel$2 t2) full(@sel$1 ts)*/a2 from t2 where a2=1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 1484901111
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"= (SELECT /*+ FULL ("T2") */ "A2" FROM "T2" "T2" WHERE
"A2"=1))
2 - filter("A2"=1)
*另外parameter hints對整個sql有效率(例如hint all_rows,gather_plan_statistics之類)
*11g中v$sql_hint可以查看可以使用的hint列表
關于oracle hint有效范圍是什么就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
