本篇文章的主要详细展开的是优化器外连接转内连接的内容，外连接转连接其实是很多数据库都有的功能，当sql的某些外连接写法满足内连接语义时就会进行转换。
oracle应该很早就拥有了这个功能好像是11.1版本，由参数_optimizer_outer_join_to_inner控制。默认为true，也可以通过hint OUTER_JOIN_TO_INNER/NO_OUTER_JOIN_TO_INNER来控制。
如sql：
|
1 2 3 4 5 6 |
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM t t1 LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 100 WHERE t2.object_type = 'VIEW'; |
该sql从语义看就可以转换成内连接，oracle确实也是这样做的，执行计划并未出现left/right join，并且outline写了OUTER_JOIN_TO_INNER(@”SEL$2BFA4EE4″ “T2″@”SEL$1”)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 2663495741 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 84 | 962 (1)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 84 | 962 (1)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T2 | 1 | 49 | 481 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL| T | 84381 | 2884K| 481 (1)| 00:00:01 | --------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_HASH(@"SEL$683367AA" "T1"@"SEL$1") LEADING(@"SEL$683367AA" "T2"@"SEL$1" "T1"@"SEL$1") FULL(@"SEL$683367AA" "T1"@"SEL$1") FULL(@"SEL$683367AA" "T2"@"SEL$1") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") ANSI_REARCH(@"SEL$1") OUTLINE(@"SEL$8812AA4E") ANSI_REARCH(@"SEL$2") OUTLINE(@"SEL$948754D7") MERGE(@"SEL$8812AA4E" >"SEL$948754D7") OUTLINE(@"SEL$2BFA4EE4") OUTER_JOIN_TO_INNER(@"SEL$2BFA4EE4" "T2"@"SEL$1") OUTLINE_LEAF(@"SEL$683367AA") ALL_ROWS OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false') OPT_PARAM('_optim_peek_user_binds' 'false') DB_VERSION('23.1.0') OPTIMIZER_FEATURES_ENABLE('23.1.0') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME") 2 - filter("T2"."OBJECT_ID"=100 AND "T2"."OBJECT_TYPE"='VIEW') |
但是当使用函数时，则无法转成内连接，如：
|
1 2 3 4 5 6 |
SELECT t1.object_name ,t2.object_type ,t2.object_name n1 FROM t t1 LEFT JOIN t2 ON t1.object_name = t2.object_name AND t2.object_id = 100 WHERE substr(t2.object_type, 1, 4) = 'VIEW'; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SQL> explain plan for select t1.object_name,t2.object_type,t2.object_name n1 from t t1 left join t2 on t1.object_name=t2.object_name and t2.object_id=100 where substr(t2.object_type,1,4)='VIEW'; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1760969921 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 84381 | 6921K| 962 (1)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | HASH JOIN RIGHT OUTER| | 84381 | 6921K| 962 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | T2 | 1 | 49 | 481 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL | T | 84381 | 2884K| 481 (1)| 00:00:01 | ------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SUBSTR("T2"."OBJECT_TYPE",1,4)='VIEW') 2 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME"(+)) 3 - filter("T2"."OBJECT_ID"(+)=100) |
其实从语义上来说也是可以转换的，这个在国产数据库vastbase中就可以完成转换。
vastbase：
|
1 2 3 4 5 6 7 8 9 10 |
vastbase=# explain select t1.object_name,t2.object_type,t2.object_name n1 from t t1 left join t2 on t1.object_name=t2.object_name and t2.object_id=100 where substr(t2.object_type,1,4)='VIEW'; QUERY PLAN ------------------------------------------------------------------------------------------------- Nested Loop (cost=4.27..630.77 rows=48 width=156) -> Seq Scan on t2 (cost=0.00..618.90 rows=1 width=142) Filter: ((object_id = 100::oid) AND (substr((object_type)::text, 1, 4) = 'VIEW'::text)) -> Bitmap Heap Scan on t t1 (cost=4.27..11.85 rows=2 width=14) Recheck Cond: (object_name = t2.object_name) -> Bitmap Index Scan on idx_2 (cost=0.00..4.26 rows=2 width=0) Index Cond: (object_name = t2.object_name) |
可以看到即使使用了函数substr，vastbase也可以进行内连接转换。只要是strict的函数，都可以转换。如果不是strict的函数，比如nvl函数表达式，不满足如果参数为空，输出必定为空的就不能转换。这样才是合理的。
外连接转内连接的好处：
以left join为例，如果where谓词作用于右表，并且没有(+)时，可以通过转换成内连接使得nestloop的驱动表为右表。如果是left join，nestloop的驱动表不可能为右表(同样如果是rightjoin，那么nestloop的驱动表不能为左表)。当然hash join是可以的，oracle有参数_right_outer_hash_enable控制。
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> @sp right -- show parameter by sp -- show hidden parameter by sp old 3: where x.indx=y.indx and ksppinm like '_%&p%' new 3: where x.indx=y.indx and ksppinm like '_%right%' NAME VALUE DESC ---------------------------------------- ---------- ------------------------------------------------------------------------------------------ _right_outer_hash_enable TRUE Right Outer/Semi/Anti Hash Enabled |
综上所述好处则是，当满足转换内连接语义时，并且右表返回记录很少，非常适合nestloop驱动表时，外连接转内连接就会有很大提升。