通过内部的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博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-688783/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
