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博客 ” ，链接：https://blog.itpub.net/25361369/viewspace-712832/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
CBO与RBO的基本概念
请登录后发表评论
登录
全部评论
