通過內部的hint來控制執行計劃
通過內部的hint來控制執行計劃
SQL> create table t1(c1 number, c2 number);
Table created.
SQL> create table t2(c1 number, c2 number);
Table created.
SQL> create table t3(c1 number, c2 number);
Table created.
示例一
1)select * from t1 where exists (select 1 from t2 where t1.c1=t2.c1)，如何讓這個查詢走nest loop，表t2為驅動。
SQL> select * from t1 where exists (select 1 from t2 where t1.c1=t2.c1);
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));-----------預設的執行計劃是hash join
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID 2s3ytddz3pszf, child number 0
-------------------------------------
select * from t1 where exists (select 1 from t2 where t1.c1=t2.c1)
Plan hash value: 1713220790
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | HASH JOIN SEMI | | 1 | 39 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$2")
END_OUTLINE_DATA
*/
已選擇58行。
根據Outline Data部分，我們可以仿照寫出如下的hint.
SQL> select /*+ use_nl(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1") LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1") */
exists (select 1 from t2 where t1.c1=t2.c1);
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID aku38p8aarqmw, child number 0
-------------------------------------
select /*+ use_nl(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1") */* from t1 where
exists (select 1 from t2 where t1.c1=t2.c1)
Plan hash value: 2519264338
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS | | 1 | 39 | 5 (20)| 00:00:01 |
| 2 | SORT UNIQUE | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T1 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
也可以自己指定query block的名字。
SQL> select /*+ use_nl([email=t2@c]t2@c[/email] t1) leading([email=t2@c]t2@c[/email]) */ * from t1 where exists (select /*+ qb_name(c) */1 from t2 where t1.c1=t2.c1);
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9xzh3cjfu1jgn, child number 0
-------------------------------------
select /*+ use_nl([email=t2@c]t2@c[/email] t1) leading([email=t2@c]t2@c[/email]) */ * from t1 where exists
(select /*+ qb_name(c) */1 from t2 where t1.c1=t2.c1)
Plan hash value: 2519264338
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 39 | 5 (20)| 00:00:01 |
| 2 | SORT UNIQUE | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T1 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3CE84023")
UNNEST(@"C")
OUTLINE(@"SEL$1")
OUTLINE(@"C")
FULL(@"SEL$3CE84023" [email=]"T2"@"C[/email]")
FULL(@"SEL$3CE84023" [email=]"T1"@"SEL$1[/email]")
LEADING(@"SEL$3CE84023" [email=]"T2"@"C[/email]" [email=]"T1"@"SEL$1[/email]")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
USE_NL(@"SEL$3CE84023" [email=]"T1"@"SEL$1[/email]")
END_OUTLINE_DATA
*/
示例2
select * from
(
select
v.c1 as v_c1,
v.c2 as v_c2,
t3.c2 as t3_c2
from
(select
t1.c1,
t2.c2
from
t1, t2
where
t1.c1 = t2.c1) v,
t3
where
v.c1 = t3.c1
) x
;
如何讓上面的查詢走hash join，並且join的順序是t1 ,t2 ,t3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID dvt15ap8szdbv, child number 0
-------------------------------------
select * from ( select v.c1 as v_c1, v.c2 as v_c2,
t3.c2 as t3_c2 from (select t1.c1, t2.c2 from
t1, t2 where t1.c1 = t2.c1) v, t3 where
v.c1 = t3.c1 ) x
Plan hash value: 2745117478
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 52 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "T2"@"SEL$3")
FULL(@"SEL$5C160134" "T3"@"SEL$2")
FULL(@"SEL$5C160134" "T1"@"SEL$3")
LEADING(@"SEL$5C160134" "T2"@"SEL$3" "T3"@"SEL$2" "T1"@"SEL$3")
USE_MERGE_CARTESIAN(@"SEL$5C160134" "T3"@"SEL$2")
USE_HASH(@"SEL$5C160134" "T1"@"SEL$3")
END_OUTLINE_DATA
*/
同樣的我們根據Outline Data部分，可以模仿寫出如下語句，控制SQL語句的執行計劃
SQL> select /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2" ) */* from
2 (
3 select
4 v.c1 as v_c1,
5 v.c2 as v_c2,
6 t3.c2 as t3_c2
7 from
8 (select
9 t1.c1,
10 t2.c2
11 from
12 t1, t2
13 where
14 t1.c1 = t2.c1) v,
15 t3
16 where
17 v.c1 = t3.c1
18 ) x
19 ;
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
SQL_ID 1w9nxggt0062t, child number 0
-------------------------------------
select /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3"
"T3"@"SEL$2" ) */* from ( select v.c1 as v_c1, v.c2 as
v_c2, t3.c2 as t3_c2 from (select t1.c1,
t2.c2 from t1, t2 where t1.c1 = t2.c1) v,
t3 where v.c1 = t3.c1 ) x
Plan hash value: 261998084
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "T1"@"SEL$3")
FULL(@"SEL$5C160134" "T2"@"SEL$3")
FULL(@"SEL$5C160134" "T3"@"SEL$2")
LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2")
USE_HASH(@"SEL$5C160134" "T2"@"SEL$3")
USE_HASH(@"SEL$5C160134" "T3"@"SEL$2")
END_OUTLINE_DATA
*/
也可以自己指定query block 的名字
SQL> explain plan for
2 select * from
3 (
4 select
5 /*+ leading(t1@inline t2@inline t3) */
6 v.c1 as v_c1,
7 v.c2 as v_c2,
8 t3.c2 as t3_c2
9 from
10 (select /*+ qb_name(inline) */
11 t1.c1,
12 t2.c2
13 from
14 t1, t2
15 where
16 t1.c1 = t2.c1) v,
17 t3
18 where
19 v.c1 = t3.c1
20 ) x
21 ;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Table created.
SQL> create table t2(c1 number, c2 number);
Table created.
SQL> create table t3(c1 number, c2 number);
Table created.
示例一
1)select * from t1 where exists (select 1 from t2 where t1.c1=t2.c1)，如何讓這個查詢走nest loop，表t2為驅動。
SQL> select * from t1 where exists (select 1 from t2 where t1.c1=t2.c1);
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));-----------預設的執行計劃是hash join
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID 2s3ytddz3pszf, child number 0
-------------------------------------
select * from t1 where exists (select 1 from t2 where t1.c1=t2.c1)
Plan hash value: 1713220790
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | HASH JOIN SEMI | | 1 | 39 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$2")
END_OUTLINE_DATA
*/
已選擇58行。
根據Outline Data部分，我們可以仿照寫出如下的hint.
SQL> select /*+ use_nl(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1") LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1") */
exists (select 1 from t2 where t1.c1=t2.c1);
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID aku38p8aarqmw, child number 0
-------------------------------------
select /*+ use_nl(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1") */* from t1 where
exists (select 1 from t2 where t1.c1=t2.c1)
Plan hash value: 2519264338
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS | | 1 | 39 | 5 (20)| 00:00:01 |
| 2 | SORT UNIQUE | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T1 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
也可以自己指定query block的名字。
SQL> select /*+ use_nl([email=t2@c]t2@c[/email] t1) leading([email=t2@c]t2@c[/email]) */ * from t1 where exists (select /*+ qb_name(c) */1 from t2 where t1.c1=t2.c1);
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9xzh3cjfu1jgn, child number 0
-------------------------------------
select /*+ use_nl([email=t2@c]t2@c[/email] t1) leading([email=t2@c]t2@c[/email]) */ * from t1 where exists
(select /*+ qb_name(c) */1 from t2 where t1.c1=t2.c1)
Plan hash value: 2519264338
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 39 | 5 (20)| 00:00:01 |
| 2 | SORT UNIQUE | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T1 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3CE84023")
UNNEST(@"C")
OUTLINE(@"SEL$1")
OUTLINE(@"C")
FULL(@"SEL$3CE84023" [email=]"T2"@"C[/email]")
FULL(@"SEL$3CE84023" [email=]"T1"@"SEL$1[/email]")
LEADING(@"SEL$3CE84023" [email=]"T2"@"C[/email]" [email=]"T1"@"SEL$1[/email]")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
USE_NL(@"SEL$3CE84023" [email=]"T1"@"SEL$1[/email]")
END_OUTLINE_DATA
*/
示例2
select * from
(
select
v.c1 as v_c1,
v.c2 as v_c2,
t3.c2 as t3_c2
from
(select
t1.c1,
t2.c2
from
t1, t2
where
t1.c1 = t2.c1) v,
t3
where
v.c1 = t3.c1
) x
;
如何讓上面的查詢走hash join，並且join的順序是t1 ,t2 ,t3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID dvt15ap8szdbv, child number 0
-------------------------------------
select * from ( select v.c1 as v_c1, v.c2 as v_c2,
t3.c2 as t3_c2 from (select t1.c1, t2.c2 from
t1, t2 where t1.c1 = t2.c1) v, t3 where
v.c1 = t3.c1 ) x
Plan hash value: 2745117478
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 52 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "T2"@"SEL$3")
FULL(@"SEL$5C160134" "T3"@"SEL$2")
FULL(@"SEL$5C160134" "T1"@"SEL$3")
LEADING(@"SEL$5C160134" "T2"@"SEL$3" "T3"@"SEL$2" "T1"@"SEL$3")
USE_MERGE_CARTESIAN(@"SEL$5C160134" "T3"@"SEL$2")
USE_HASH(@"SEL$5C160134" "T1"@"SEL$3")
END_OUTLINE_DATA
*/
同樣的我們根據Outline Data部分，可以模仿寫出如下語句，控制SQL語句的執行計劃
SQL> select /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2" ) */* from
2 (
3 select
4 v.c1 as v_c1,
5 v.c2 as v_c2,
6 t3.c2 as t3_c2
7 from
8 (select
9 t1.c1,
10 t2.c2
11 from
12 t1, t2
13 where
14 t1.c1 = t2.c1) v,
15 t3
16 where
17 v.c1 = t3.c1
18 ) x
19 ;
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
SQL_ID 1w9nxggt0062t, child number 0
-------------------------------------
select /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3"
"T3"@"SEL$2" ) */* from ( select v.c1 as v_c1, v.c2 as
v_c2, t3.c2 as t3_c2 from (select t1.c1,
t2.c2 from t1, t2 where t1.c1 = t2.c1) v,
t3 where v.c1 = t3.c1 ) x
Plan hash value: 261998084
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
OUTLINE(@"SEL$3")
FULL(@"SEL$5C160134" "T1"@"SEL$3")
FULL(@"SEL$5C160134" "T2"@"SEL$3")
FULL(@"SEL$5C160134" "T3"@"SEL$2")
LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2")
USE_HASH(@"SEL$5C160134" "T2"@"SEL$3")
USE_HASH(@"SEL$5C160134" "T3"@"SEL$2")
END_OUTLINE_DATA
*/
也可以自己指定query block 的名字
SQL> explain plan for
2 select * from
3 (
4 select
5 /*+ leading(t1@inline t2@inline t3) */
6 v.c1 as v_c1,
7 v.c2 as v_c2,
8 t3.c2 as t3_c2
9 from
10 (select /*+ qb_name(inline) */
11 t1.c1,
12 t2.c2
13 from
14 t1, t2
15 where
16 t1.c1 = t2.c1) v,
17 t3
18 where
19 v.c1 = t3.c1
20 ) x
21 ;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-688783/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 使用Oracle Hint提示來更改執行計劃Oracle
- 使用Oracle Hint提示來更改執行計劃 ZTOracle
- 使用hint改變執行計劃
- 通過shell指令碼來得到不穩定的執行計劃指令碼
- 通過等待事件來獲得查詢SQl的執行計劃事件SQL
- 使用no_merge結合其它hint完全控制Oracle執行計劃Oracle
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- 通過鎖定表的統計資訊來穩定sql的執行計劃SQL
- Oracle 通過註釋改變執行計劃Oracle
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- 通過執行計劃中的CONCATENATION分析sql問題SQL
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 【sql調優之執行計劃】使用hint(三)Hints for Query TransformationsSQLORM
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- 控制執行計劃之-SQL Profile（一）SQL
- 透過shell指令碼來得到不穩定的執行計劃指令碼
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- Java的通過管道來實現執行緒通訊Java執行緒
- 加hint改變執行計劃訪問順序優化sql優化SQL
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 通過API執行AutoCAD命令來…API
- hint不能傳播到view內部！View
- 用sql profile來固定執行計劃SQL
- 揭秘計算機指令執行的神秘過程：CPU內部的絕密操作計算機
- Nginx通過內建的Perl模組執行Perl程式Nginx
- 執行計劃-1：獲取執行計劃
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- java 內部執行緒Java執行緒
- 控制執行計劃之-SPM BASELINE（六）
- 控制執行計劃之-SPM BASELINE（五）
- 控制執行計劃之-SPM BASELINE（四）
- 控制執行計劃之-SPM BASELINE（三）
- 控制執行計劃之-SPM BASELINE（二）
- 控制執行計劃之-SPM BASELINE（一）