Hint Cardinality

ty@BBK10G> create table t1 as select rownum id, object_name from dba_objects where rownum < 1001;

Table created.

ty@BBK10G> create table t2 as select * from t1 where 1 = 0;

Table created.

ty@BBK10G> create index t1_idx on t1 (id);

Index created.

ty@BBK10G> create index t2_idx on t2 (id);

Index created.

ty@BBK10G> set autot trace exp stat

ty@BBK10G> exec dbms_stats.gather_table_stats(user, 't1', cascade=>true, method_opt=>'for all indexed columns');

PL/SQL procedure successfully completed.

ty@BBK10G> select/*+ dynamic_sampling(t2 0) cardinality(t2 100000)*/ * from t1, t2 where t1.id = t2.id; --告诉优化器t2有100000条，不准动态采样

no rows selected

Execution Plan

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

Plan hash value: 1838229974

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |  1219K|   112M|    14  (65)| 00:00:01 |

|*  1 |  HASH JOIN         |      |  1219K|   112M|    14  (65)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T1   |  1000 | 18000 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T2   |   100K|  7714K|     2   (0)| 00:00:01 | --走的HASH连接，因为T2太大了

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

Predicate Information (identified by operation id):

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

   1 - access("T1"."ID"="T2"."ID")

Statistics

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

        100  recursive calls

         32  db block gets

         22  consistent gets

          0  physical reads

       7256  redo size

        445  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

ty@BBK10G> select/*+ dynamic_sampling(t2 0) cardinality(t2 1)*/ * from t1, t2 where t1.id = t2.id; --T2有1条

no rows selected

Execution Plan

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

Plan hash value: 1363497261

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |        |    12 |  1164 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |    12 |   216 |     2   (0)| 00:00:01 |

|   2 |   NESTED LOOPS              |        |    12 |  1164 |     4   (0)| 00:00:01 | --走的嵌套连接

|   3 |    TABLE ACCESS FULL        | T2     |     1 |    79 |     2   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN         | T1_IDX |    12 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   4 - access("T1"."ID"="T2"."ID")

Statistics

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

         30  recursive calls

        192  db block gets

         36  consistent gets

          0  physical reads

          0  redo size

        445  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

子查询的Cardinality直接影响主查询的执行计划。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25361369/viewspace-712832/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25361369/viewspace-712832/