1,
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> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

Plan hash value: 2959412835

---------------------------------------------------------------------------

---

---------------------------------------------------------------------------

---

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)

已选择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> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

Plan hash value: 1506669289

-------------------------------------------------------------------------------

-------

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)

已选择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;

已解释。

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 |
-----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."A"="T2"."A")

Note
-----
- dynamic sampling used for this statement (level=2)

已选择19行。

SQL>

小结:1,cardinality对于执行计划的形成影响很大
     2,cardinality(表自表中得到的记录条数)
     3,dynamic_sampling(表名 0) 禁用动态采样
     4,子查询和多表关联同理

ITPub博客

oracle cardinality对于执行计划的影响

最新文章