SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
可以看到SQL语句中的第3,4行,连接后的结果集较大,所以CBO考虑先执行最后两句in子查询语句,预估值都为1,实际也为1,通过笛卡尔的连接后再分别与TB1,TB2连接并返回最终结果集(且最后一句为11G R2新特性,向量IO的使用)
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> create table tb1 as select * from dba_objects where object_id is not null;
Table created.
SQL> create table tb2 as select * from tb1;
Table created.
SQL> create table tb3 as select * from tb1 where rownum<2;
Table created.
SQL> create index IDX_TB1_OBJECT_ID on tb1(object_id);
Index created.
SQL> create index IDX_TB1_OBJECT_TYPE on tb1(object_type);
Index created.
SQL> create index IDX_TB2_OBJECT_ID on tb2(object_id);
Index created.
SQL> create index IDX_TB2_OBJECT_TYPE on tb2(object_type);
Index created.
SQL> create index IDX_TB3_OBJECT_ID on tb3(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('TEST','TB1',CASCADE=>TRUE ,method_opt=>'for all indexed columns size 1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('TEST','TB2',CASCADE=>TRUE ,method_opt=>'for all indexed columns size 1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('TEST','TB3',CASCADE=>TRUE ,method_opt=>'for all indexed columns size 1');
PL/SQL procedure successfully completed.
执行SQL:
1.select tb1.status,tb2.status
2.from tb1,tb1 tb11,tb2
3.where tb2.status=tb11.status
4.and tb1.object_type=tb2.object_type
5.and tb2.object_id in (1475,1484)
6.and tb1.object_id in (select distinct object_id from tb3)
7.and tb11.object_id in (select distinct object_id from tb3);
Execution Plan
----------------------------------------------------------
Plan hash value: 3283748246
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 10 (10)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 54 | 10 (10)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 35 | 7 (15)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 25 | 5 (20)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN | | 1 | 6 | 3 (34)| 00:00:01 |
| 6 | SORT UNIQUE | | 1 | 3 | 1 (0)| 00:00:01 |
| 7 | INDEX FULL SCAN | IDX_TB3_OBJECT_ID | 1 | 3 | 1 (0)| 00:00:01 | --最后in子查询
| 8 | BUFFER SORT | | 1 | 3 | 2 (50)| 00:00:01 |
| 9 | SORT UNIQUE | | 1 | 3 | 1 (0)| 00:00:01 |
| 10 | INDEX FULL SCAN | IDX_TB3_OBJECT_ID | 1 | 3 | 1 (0)| 00:00:01 | --最后in子查询
| 11 | TABLE ACCESS BY INDEX ROWID| TB1 | 1 | 19 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_TB1_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | TB1 | 1 | 10 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_TB1_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
| 15 | INLIST ITERATOR | | | | | |
|* 16 | INDEX RANGE SCAN | IDX_TB2_OBJECT_ID | 2 | | 2 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | TB2 | 1 | 19 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - access("TB1"."OBJECT_ID"="OBJECT_ID")
14 - access("TB11"."OBJECT_ID"="OBJECT_ID")
16 - access("TB2"."OBJECT_ID"=1475 OR "TB2"."OBJECT_ID"=1484)
17 - filter("TB2"."STATUS"="TB11"."STATUS" AND "TB1"."OBJECT_TYPE"="TB2"."OBJECT_TYPE")
可以看到SQL语句中的第3,4行,连接后的结果集较大,所以CBO考虑先执行最后两句in子查询语句,预估值都为1,实际也为1,通过笛卡尔的连接后再分别与TB1,TB2连接并返回最终结果集(且最后一句为11G R2新特性,向量IO的使用)
3.where tb2.status=tb11.status
4.and tb1.object_type=tb2.object_type
上面简单的模拟笛卡尔连接的出现情况,当表与表连接可过滤性过差,存在有些表含有极少量的数据,CBO会先考虑使用cartesian方式(MERGE JOIN CARTESIAN);
在这种情况下注意地方就是CBO对采用笛卡尔连接表的结果集的预估准确性,如果实际的数量远远大于预估,则会对性能产生重大影响;