1,
SQL> create table t1(a int,b int);
SQL> create table t1(a int,b int);
表已创建。
SQL> create table t2(a int,b int);
表已创建。
SQL> insert into t1 select level,level from dual connect by level<
已创建1999999行。
SQL> commit;
提交完成。
SQL> insert into t2 select level,level from dual connect by level<
已创建1999999行。
SQL> commit;
提交完成。
SQL> create index idx_t1 on t1(a);
索引已创建。
SQL> create index idx_t2 on t2(a);
索引已创建。
--二个行源记录集差不多,故用hash join
SQL> explain plan for select t1.a,t1.b from t1,t2 where t1.a=t2.a;
SQL> explain plan for select t1.a,t1.b from t1,t2 where t1.a=t2.a;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
---------------------------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Ti
|
|
---------------------------------------------------------------------------
---
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1966K| 73M| | 7501 (1)| 00
1 |
|* 1 | HASH JOIN | | 1966K| 73M| 46M| 7501 (1)| 00
1 |
| 2 | TABLE ACCESS FULL| T2 | 1966K| 24M| | 781 (2)| 00
0 |
| 3 | TABLE ACCESS FULL| T1 | 1999K| 49M| | 782 (2)| 00
0 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."A"="T2"."A")
Note
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
---------------------------------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
- dynamic sampling used for this statement (level=2)
已选择19行。
--用提示cardinality告知CBO,T2行源的查询结果集记录数为1,故用nested loop
SQL> explain plan for select /*+ cardinality(t2 1) */ t1.a,t1.b from t1,t2 whe
e t1.a=t2.a;
SQL> explain plan for select /*+ cardinality(t2 1) */ t1.a,t1.b from t1,t2 whe
e t1.a=t2.a;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Plan hash value: 1506669289
-------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
e |
-------------------------------------------------------------------------------
-------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 775 (1)| 00
00:10 |
| 1 | NESTED LOOPS | | | | |
|
|
| 2 | NESTED LOOPS | | 1 | 39 | 775 (1)| 00
00:10 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 13 | 772 (1)| 00
00:10 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
|* 4 | INDEX RANGE SCAN | IDX_T1 | 1 | | 2 (0)| 00
00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 3 (0)| 00 --因为select中要是t1.b,而非t1.a,所以要通过index rowid再次回表,才能得到t1.b,如果是t1.a不用回表,在索引直接可以得到
00:01 |
-------------------------------------------------------------------------------
-------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
4 - access("T1"."A"="T2"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择21行。
SQL>
--select中要获取a,b行源各自的列,直接nested loop t2和t1的索引,因为 select中的列是t1.a,而非t1.b
--如下直接在t1.a的索引可以得到,且在t2可以得到t2.b,所以直接nested loop二行源
SQL> explain plan for select /*+ cardinality(t2 1) */ t1.a,t2.b from t1,t2 wher
e t1.a=t2.a;
--如下直接在t1.a的索引可以得到,且在t2可以得到t2.b,所以直接nested loop二行源
SQL> explain plan for select /*+ cardinality(t2 1) */ t1.a,t2.b from t1,t2 wher
e t1.a=t2.a;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 34775561
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 774 (1)| 00:00:10 |
| 1 | NESTED LOOPS | | 1 | 39 | 774 (1)| 00:00:10 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 26 | 772 (1)| 00:00:10 |
|* 3 | INDEX RANGE SCAN | IDX_T1 | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 774 (1)| 00:00:10 |
| 1 | NESTED LOOPS | | 1 | 39 | 774 (1)| 00:00:10 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 26 | 772 (1)| 00:00:10 |
|* 3 | INDEX RANGE SCAN | IDX_T1 | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
3 - access("T1"."A"="T2"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
已选择19行。
SQL>
小结:1,cardinality对于执行计划的形成影响很大
2,cardinality(表 自表中得到的记录条数)
3,dynamic_sampling(表名 0) 禁用动态采样
4,子查询和多表关联同理
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-751581/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-751581/