oracle cardinality對於執行計劃的影響

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

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

---

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

已選擇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

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

-------

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

已選擇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,子查詢和多表關聯同理