正文

10g和11g，优化器对外连接的处理对比

IP云
IP云V管理员/5 阅读
08/05
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

我反省，今天面试有个问题没有说清楚。
我给出的结论（而且这个结论我验证过）是：不要使用不必要的外连接，举了下面这个例子却没有说清楚。虽然最近感冒，状态不是很好，但最擅长的东西都没有表达清楚，泪流满面啊：（
多谢那位面试官DBA的提醒，我以后要多多注意啊，要把自己擅长的东西说清楚。

10g：
test@ORA10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production

创建测试环境：
drop table t1;
drop table t2;

create table t1 as select * from dba_objects;
create table t2 as select * from t1;

create index i_t2_objectid on t2(object_id) parallel 3;
alter index i_t2_objectid noparallel;

analyze table t1 compute statistics for table for all indexes for all indexed columns;
analyze table t2 compute statistics for table for all indexes for all indexed columns;

执行SQL并查看其执行计划：
test@ORA10G> select t1.object_id, t2.object_id, t1.object_name, t2.object_name
2 from t1, t2
3 where t1.object_id = t2.object_id(+)
4 and t2.object_id in (3, 6, 11, 28);

Execution Plan
----------------------------------------------------------
Plan hash value: 1594007018

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 600 | | 32331 (1)| 00:06:28 |
|* 1 | FILTER | | | | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 4 | 600 | 173M| 32331 (1)| 00:06:28 |
| 3 | TABLE ACCESS FULL | T2 | 2196K| 148M| | 7109 (1)| 00:01:26 |
| 4 | TABLE ACCESS FULL | T1 | 2196K| 165M| | 7110 (1)| 00:01:26 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T2"."OBJECT_ID"=3 OR "T2"."OBJECT_ID"=6 OR "T2"."OBJECT_ID"=11
OR "T2"."OBJECT_ID"=28)
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"(+))

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

从上面结果可以看到，即使t2中字段object_id上有索引，在10g中也是用不到的。Oracle先选择将两个表走hash join right outer连接，然后将结果进行filter过滤。

同样的操作，下面看11g将会怎样处理：
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

创建测试环境：
drop table t1;
drop table t2;

create table t1 as select * from dba_objects;
create table t2 as select * from t1;

create index i_t2_objectid on t2(object_id) parallel 3;
alter index i_t2_objectid noparallel;

analyze table t1 compute statistics for table for all indexes for all indexed columns;
analyze table t2 compute statistics for table for all indexes for all indexed columns;

执行SQL并查看其执行计划：
SQL> set linesize 400 pagesize 400
SQL> set autot trace
SQL>
SQL> select t1.object_id,t1.object_name,t2.object_id,t2.object_name
2 from t1,t2
3 where t1.object_id=t2.object_id(+)
4 and t2.object_id in (3,6,11,28);

Execution Plan
----------------------------------------------------------

Plan hash value: 1372208711

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1896 | 57 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 1896 | 57 (2)| 00:00:01 |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 12 | 948 | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_T2_OBJECTID | 4 | | 4 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 131 | 10349 | 51 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
4 - access("T2"."OBJECT_ID"=3 OR "T2"."OBJECT_ID"=6 OR "T2"."OBJECT_ID"=11 OR
"T2"."OBJECT_ID"=28)
5 - filter("T1"."OBJECT_ID"=3 OR "T1"."OBJECT_ID"=6 OR "T1"."OBJECT_ID"=11 OR
"T1"."OBJECT_ID"=28)

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

从上面可以看出，11g中，Oracle是忽略了外连接的。Oracle在11g中优化器应该做了优化。
我今天本来想表达的意思是：10g中外连接在这种情况下其实是先两个表full table scan的结果做了hash 外连接，将hash的结果做fillter过滤。而11g会忽略掉外连接，将t2的id列会走索引，然后跟另一个表做join连接。