ORACLE外连接函数表达式过滤条件无法转换为INNER JOIN

1)正常情况，where有对内表的过滤条件，没有函数且不是IS NULL，则会转为INNER JOIN

2)oracle的外连接where内表过滤条件是函数，包括自定义函数，没有其他额外非函数的过滤条件，则不能转为inner join，只能走外连接，然后外层FILTER过滤where条件，一般来说，效率比inner join低，增加 内表条件 is not null 则可以转为inner join，数学运算可以转为inner join

3）PG和MySQL支持外连接where条件对内表使用函数过滤，转为inner join，这点ORACLE CBO不如PG和MySQL



###########ORACLE

1.正常情况，where有对内表的过滤条件，没有函数且不是IS NULL，则会转为INNER JOIN

set autotrace traceonly
select count(*)
from t1 left join t2
on t1.object_id=t2.object_id
where t2.object_name like 'T%';




Execution Plan
----------------------------------------------------------
Plan hash value: 4274056747


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    45 |   774   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    45 |            |          |
|*  2 |   HASH JOIN         |      |  1444 | 64980 |   774   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T2   |  1444 | 57760 |   387   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   | 72667 |   354K|   386   (1)| 00:00:01 |
----------------------------------------------------------------------------


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


   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - filter("T2"."OBJECT_NAME" LIKE 'T%')

###where 内表条件 is null会自动转为anti join,这个也是经常用来改写not exists的优化方法

select count(*)
from t1 left join t2
on t1.object_id=t2.object_id
where t2.object_id is null;


Execution Plan
----------------------------------------------------------
Plan hash value: 254713432


---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    10 |   433   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |    10 |            |          |
|*  2 |   HASH JOIN ANTI       |        |   727 |  7270 |   433   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | T1     | 72667 |   354K|   386   (1)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| IDX_T2 | 72814 |   355K|    46   (3)| 00:00:01 |
---------------------------------------------------------------------------------


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


   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

2. oracle的外连接where内表过滤条件是函数，包括自定义函数，没有其他额外非函数的过滤条件，则不能转为inner join，只能走外连接，然后外层FILTER过滤where条件，一般来说，效率比inner join低。增加  内表条件 is not null 则可以转为inner join

select count(*)
from t1 left join t2
on t1.object_id=t2.object_id
where substr(t2.object_name,1,2) = 'T1';




Execution Plan
----------------------------------------------------------
Plan hash value: 2491233022


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    45 |   774   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE      |      |     1 |    45 |            |          |
|*  2 |   FILTER             |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      | 72667 |  3193K|   774   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   | 72667 |   354K|   386   (1)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   | 72814 |  2844K|   387   (1)| 00:00:01 |
-----------------------------------------------------------------------------


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


   2 - filter(SUBSTR("T2"."OBJECT_NAME",1,2)='T1')
   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"(+))

数学运算可以：

select count(*)
from t1 left join t2
on t1.object_id=t2.object_id
where t2.data_object_id+1 = 1000;


Execution Plan
----------------------------------------------------------
Plan hash value: 4274056747


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    12 |   774   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    12 |            |          |
|*  2 |   HASH JOIN         |      |   728 |  8736 |   774   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T2   |   728 |  5096 |   388   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   | 72667 |   354K|   386   (1)| 00:00:01 |
----------------------------------------------------------------------------


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


   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - filter("T2"."DATA_OBJECT_ID"+1=1000)

增加   t2.object_name is not null 则可以转为inner join

select count(*)
from t1 left join t2
on t1.object_id=t2.object_id
where substr(t2.object_name,1,2) = 'T1'
and t2.object_name is not null;




Execution Plan
----------------------------------------------------------
Plan hash value: 4274056747


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    45 |   774   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    45 |            |          |
|*  2 |   HASH JOIN         |      |   728 | 32760 |   774   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T2   |   728 | 29120 |   388   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   | 72667 |   354K|   386   (1)| 00:00:01 |
----------------------------------------------------------------------------


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


   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - filter(SUBSTR("T2"."OBJECT_NAME",1,2)='T1' AND
              "T2"."OBJECT_NAME" IS NOT NULL)