Oracle 子查询和联接性能

嗨，

我想知道哪个连接或子查询更快？

这要看情况了。

在许多情况下，优化程序可以将子查询重写为（半）连接。所以性能是一样的。

例如，以下比较：

-连接
-在子查询中
-存在子查询

set serveroutput off
create table t1 (
  t1_id int not null primary key,
  stuff varchar2(10) not null
);

create table t2 (
  t2_id int not null primary key,
  t1_id int not null
);

insert into t1 
  select rownum, 'XXXXXXX' from dual connect by level <= 10;
  
insert into t2
  select rownum, rownum from dual connect by level <= 5;
  
commit;
exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');

select /*+ gather_plan_statistics */t1.* from t1
join   t2 
on     t1.t1_id = t2.t1_id;

select * 
from table(dbms_xplan.display_cursor(null, null, 'BASIC +IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                      
EXPLAINED SQL STATEMENT:                                                               
------------------------                                                               
select /*+ gather_plan_statistics */t1.* from t1 join   t2  on                         
t1.t1_id = t2.t1_id                                                                    
                                                                                       
Plan hash value: 3467165580                                                            
                                                                                       
-------------------------------------------------------------------------------------  
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.01 |       5 |  
|*  1 |  HASH JOIN         |      |      1 |      5 |      5 |00:00:00.01 |       5 |  
|   2 |   TABLE ACCESS FULL| T2   |      1 |      5 |      5 |00:00:00.01 |       2 |  
|   3 |   TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |       3 |  
-------------------------------------------------------------------------------------  
                                                                                       
Predicate Information (identified by operation id):                                    
---------------------------------------------------                                    
                                                                                       
   1 - access("T1"."T1_ID"="T2"."T1_ID")  

select /*+ gather_plan_statistics */* from t1
where  t1_id in (select t1_id from t2);

select * 
from table(dbms_xplan.display_cursor(null, null, 'BASIC +IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                      
EXPLAINED SQL STATEMENT:                                                               
------------------------                                                               
select /*+ gather_plan_statistics */* from t1 where  t1_id in (select                  
t1_id from t2)                                                                         
                                                                                       
Plan hash value: 3465738540                                                            
                                                                                       
-------------------------------------------------------------------------------------  
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.01 |       5 |  
|*  1 |  HASH JOIN SEMI    |      |      1 |      4 |      5 |00:00:00.01 |       5 |  
|   2 |   TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |       2 |  
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |      5 |00:00:00.01 |       3 |  
-------------------------------------------------------------------------------------  
                                                                                       
Predicate Information (identified by operation id):                                    
---------------------------------------------------                                    
                                                                                       
   1 - access("T1_ID"="T1_ID")    

select /*+ gather_plan_statistics */* from t1
where  exists (
  select null from t2
  where  t1.t1_id = t2.t1_id
);

select * 
from table(dbms_xplan.display_cursor(null, null, 'BASIC +IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                      
EXPLAINED SQL STATEMENT:                                                               
------------------------                                                               
select /*+ gather_plan_statistics */* from t1 where  exists (   select                 
null from t2  where  t1.t1_id = t2.t1_id )                                             
                                                                                       
Plan hash value: 3465738540                                                            
                                                                                       
-------------------------------------------------------------------------------------  
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.01 |       5 |  
|*  1 |  HASH JOIN SEMI    |      |      1 |      4 |      5 |00:00:00.01 |       5 |  
|   2 |   TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |       2 |  
|   3 |   TABLE ACCESS FULL| T2   |      1 |      5 |      5 |00:00:00.01 |       3 |  
-------------------------------------------------------------------------------------  
                                                                                       
Predicate Information (identified by operation id):                                    
---------------------------------------------------                                    
                                                                                       
   1 - access("T1"."T1_ID"="T2"."T1_ID") 

11.2.0.4上的测试。

三个人做的工作量都一样（每个计划顶部的SELECT缓冲区= 5 ）但是子查询以不同的顺序访问表（ t1然后t2 , t2然后t1 ）。

因此，没有普遍的答案。您需要在数据库上测试并与数据进行比较。