ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g的CBO有不少的变化

10g的CBO有不少的变化

原创 Linux操作系统 作者:husthxd 时间:2004-10-27 00:00:00 0 删除 编辑

比较了一下not exists & not in,发现没什么区别。看如下实验。


11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> set time on
11:42:41 test@GZSERVER> set timing on
11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> drop table outer_large_t
11:42:41   2  /

表已丢弃。

已用时间:  00: 00: 00.01
11:42:41 test@GZSERVER> create table outer_large_t
11:42:41   2  (id number not null,
11:42:41   3  c1 varchar2(100),
11:42:41   4  c2 varchar2(100)
11:42:41   5  )
11:42:41   6  /

表已创建。

已用时间:  00: 00: 00.00
11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> alter table outer_large_t add primary key (id)
11:42:41   2  /

表已更改。

已用时间:  00: 00: 00.00
11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> drop table outer_small_t
11:42:41   2  /

表已丢弃。

已用时间:  00: 00: 00.00
11:42:41 test@GZSERVER> create table outer_small_t
11:42:41   2  as select *from outer_large_t
11:42:41   3  where 1=2
11:42:41   4  /

表已创建。

已用时间:  00: 00: 00.01
11:42:41 test@GZSERVER> create index idx_outer_small_t_id on outer_small_t(id)
11:42:41   2  /

索引已创建。

已用时间:  00: 00: 00.00
11:42:41 test@GZSERVER>
11:42:41 test@GZSERVER> drop table inner_large_t
11:42:41   2  /

表已丢弃。

已用时间:  00: 00: 00.01
11:42:42 test@GZSERVER> create table inner_large_t
11:42:42   2  (id number not null,
11:42:42   3  c3 varchar2(100),
11:42:42   4  c4 varchar2(100)
11:42:42   5  )
11:42:42   6  /

表已创建。

已用时间:  00: 00: 00.00
11:42:42 test@GZSERVER> create index idx_inner_large_t_1 on inner_large_t(id,c3)
11:42:42   2  /

索引已创建。

已用时间:  00: 00: 00.00
11:42:42 test@GZSERVER>
11:42:42 test@GZSERVER> drop table inner_small_t
11:42:42   2  /

表已丢弃。

已用时间:  00: 00: 00.00
11:42:42 test@GZSERVER> create table inner_small_t
11:42:42   2  as select *from inner_large_t
11:42:42   3  where 1=2
11:42:42   4  /

表已创建。

已用时间:  00: 00: 00.01
11:42:42 test@GZSERVER> create index idx_inner_small_t_1 on inner_small_t(id,c3)
11:42:42   2  /

索引已创建。

已用时间:  00: 00: 00.00
11:42:42 test@GZSERVER>
11:42:42 test@GZSERVER> declare
11:42:42   2  begin
11:42:42   3    for i in 1..50000 loop
11:42:42   4      insert into outer_large_t values (i,'test','test');
11:42:42   5    end loop;
11:42:42   6    for i in 1..1000 loop
11:42:42   7      insert into outer_small_t values (i,'test','test');
11:42:42   8    end loop;
11:42:42   9    for i in 30000..50000 loop
11:42:42  10      insert into inner_large_t values (i,'test','test');
11:42:42  11    end loop;
11:42:42  12    for i in 3000..5000 loop
11:42:42  13      insert into inner_small_t values (i,'test','test');
11:42:42  14    end loop;
11:42:42  15    commit;
11:42:42  16  end;
11:42:42  17  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 41.06
11:43:24 test@GZSERVER>
11:43:24 test@GZSERVER> analyze table outer_large_t compute statistics for table for all indexes
11:43:24   2  /

表已分析。

已用时间:  00: 00: 02.04
11:43:26 test@GZSERVER> analyze table outer_small_t compute statistics for table for all indexes
11:43:26   2  /

表已分析。

已用时间:  00: 00: 00.01
11:43:26 test@GZSERVER> analyze table inner_large_t compute statistics for table for all indexes
11:43:26   2  /

表已分析。

已用时间:  00: 00: 00.07
11:43:27 test@GZSERVER> analyze table inner_large_t compute statistics for table for all indexes
11:43:27   2  /

表已分析。

已用时间:  00: 00: 00.07
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> set autotrace on
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> select count(*) from outer_large_t
11:43:28   2  where id not in
11:43:28   3  (select id from inner_large_t)
11:43:28   4  /

  COUNT(*)                                                                     
----------                                                                     
     29999                                                                     

已用时间:  00: 00: 00.01

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=161 Card=1 Bytes=2         
          6)                                                                   
                                                                               
   1    0   SORT (AGGREGATE)                                                   
   2    1     HASH JOIN (RIGHT ANTI) (Cost=161 Card=1 Bytes=26)                
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX         
          ) (Cost=13 Card=20001 Bytes=260013)                                  
                                                                               
   4    2       INDEX (FAST FULL SCAN) OF 'SYS_C005134' (INDEX (UNIQUE         
          )) (Cost=22 Card=50000 Bytes=650000)                                 
                                                                               


Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
        160  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        382  bytes sent via SQL*Net to client                                  
        504  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

11:43:28 test@GZSERVER> select count(*) from outer_large_t outer_t
11:43:28   2  where not exists
11:43:28   3  (select id from inner_large_t where id = outer_t.id)
11:43:28   4  /

  COUNT(*)                                                                     
----------                                                                     
     29999                                                                     

已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=161 Card=1 Bytes=2         
          6)                                                                   
                                                                               
   1    0   SORT (AGGREGATE)                                                   
   2    1     HASH JOIN (RIGHT ANTI) (Cost=161 Card=1 Bytes=26)                
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX         
          ) (Cost=13 Card=20001 Bytes=260013)                                  
                                                                               
   4    2       INDEX (FAST FULL SCAN) OF 'SYS_C005134' (INDEX (UNIQUE         
          )) (Cost=22 Card=50000 Bytes=650000)                                 
                                                                               


Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
        160  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        382  bytes sent via SQL*Net to client                                  
        504  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> select count(*) from outer_small_t
11:43:28   2  where id not in
11:43:28   3  (select id from inner_large_t)
11:43:28   4  /

  COUNT(*)                                                                     
----------                                                                     
      1000                                                                     

已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1 Bytes=26         
          )                                                                    
                                                                               
   1    0   SORT (AGGREGATE)                                                   
   2    1     HASH JOIN (ANTI) (Cost=16 Card=1 Bytes=26)                       
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE         
          X) (Cost=2 Card=1000 Bytes=13000)                                    
                                                                               
   4    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX         
          ) (Cost=13 Card=20001 Bytes=260013)                                  
                                                                               


Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
         64  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        380  bytes sent via SQL*Net to client                                  
        504  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

11:43:28 test@GZSERVER> select count(*) from outer_small_t outer_t
11:43:28   2  where not exists
11:43:28   3  (select id from inner_large_t where id = outer_t.id)
11:43:28   4  /

  COUNT(*)                                                                     
----------                                                                     
      1000                                                                     

已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=1 Bytes=26         
          )                                                                    
                                                                               
   1    0   SORT (AGGREGATE)                                                   
   2    1     HASH JOIN (ANTI) (Cost=16 Card=1 Bytes=26)                       
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OUTER_SMALL_T_ID' (INDE         
          X) (Cost=2 Card=1000 Bytes=13000)                                    
                                                                               
   4    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_LARGE_T_1' (INDEX         
          ) (Cost=13 Card=20001 Bytes=260013)                                  
                                                                               


Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
         64  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        380  bytes sent via SQL*Net to client                                  
        504  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> select count(*) from outer_large_t
11:43:28   2  where id not in
11:43:28   3  (select id from inner_small_t)
11:43:28   4  /

  COUNT(*)                                                                     
----------                                                                     
     47999                                                                     

已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=1 Bytes=26         
          )                                                                    
                                                                               
   1    0   SORT (AGGREGATE)                                                   
   2    1     HASH JOIN (RIGHT ANTI) (Cost=26 Card=1 Bytes=26)                 
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_SMALL_T_1' (INDEX         
          ) (Cost=3 Card=2001 Bytes=26013)                                     
                                                                               
   4    2       INDEX (FAST FULL SCAN) OF 'SYS_C005134' (INDEX (UNIQUE         
          )) (Cost=22 Card=50000 Bytes=650000)                                 
                                                                               


Statistics
----------------------------------------------------------                     
          6  recursive calls                                                   
          0  db block gets                                                     
        133  consistent gets                                                   
          0  physical reads                                                    
        912  redo size                                                         
        382  bytes sent via SQL*Net to client                                  
        504  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          1  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

11:43:28 test@GZSERVER> select count(*) from outer_large_t outer_t
11:43:28   2  where not exists
11:43:28   3  (select id from inner_small_t where id = outer_t.id)
11:43:28   4  /

  COUNT(*)                                                                     
----------                                                                     
     47999                                                                     

已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=1 Bytes=26         
          )                                                                    
                                                                               
   1    0   SORT (AGGREGATE)                                                   
   2    1     HASH JOIN (RIGHT ANTI) (Cost=26 Card=1 Bytes=26)                 
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_INNER_SMALL_T_1' (INDEX         
          ) (Cost=3 Card=2001 Bytes=26013)                                     
                                                                               
   4    2       INDEX (FAST FULL SCAN) OF 'SYS_C005134' (INDEX (UNIQUE         
          )) (Cost=22 Card=50000 Bytes=650000)                                 
                                                                               


Statistics
----------------------------------------------------------                     
          5  recursive calls                                                   
          0  db block gets                                                     
        121  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        382  bytes sent via SQL*Net to client                                  
        504  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          1  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER>
11:43:28 test@GZSERVER> select count(*) from outer_small_t
11:43:28   2  where id not in
11:43:28   3  (select id from inner_small_t)
11:43:28   4  /

  COUNT(*)                                    

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

请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,对Oracle、PostgreSQL以及大数据等相关技术有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1167
  • 访问量
    3634310