笛卡尔连接构造一则

来源：互联网发布：女网络歌手2016 编辑：程序博客网时间：2024/05/19 00:14

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> 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,tb23.where tb2.status=tb11.status4.and tb1.object_type=tb2.object_type5.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.status4.and tb1.object_type=tb2.object_type

上面简单的模拟笛卡尔连接的出现情况，当表与表连接可过滤性过差，存在有些表含有极少量的数据，CBO会先考虑使用cartesian方式（MERGE JOIN CARTESIAN）；

在这种情况下注意地方就是CBO对采用笛卡尔连接表的结果集的预估准确性，如果实际的数量远远大于预估，则会对性能产生重大影响；

0 0