执行的SQL语句：
select /*+ use_merge(t4,t2)*/ count(1)  from lixia.t4,lixia.t2
where t4.object_id=t2.object_id
  and (t2.object_id=52946 and t4.owner='BI'
  and t2.object_name='CHANNELS' OR t2.object_name='C_COBJ#');
 
查看执行计划：

SQL>  select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS Advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID  5vmfh4mtp3j7f, child number 0
-------------------------------------
select /*+ use_merge(t4,t2)*/ count(1)  from lixia.t4,lixia.t2 where t4.object_id=t2.object_id   and (t2.object_id=52946 and t4.owner='BI'   and t
OR t2.object_name='C_COBJ#')

Plan hash value: 630170684

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buf
--------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                 |                    |      1 |      1 |    38 |       |         |             |      1 |00:00:00.04 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|   2 |   CONCATENATION                 |                    |      1 |        |       |       |         |             |      3 |00:00:00.04 |
|   3 |    MERGE JOIN                   |                    |      1 |   4521 |   167K|       |   557   (2)| 00:00:07 |      1 |00:00:00.04 |
|   4 |     SORT JOIN                   |                    |      1 |      1 |    29 |       |     3  (34)| 00:00:01 |      1 |00:00:00.01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| T2                 |      1 |      1 |    29 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |
|*  6 |       INDEX RANGE SCAN          | T2_OBJECT_NAME_IDX |      1 |      2 |       |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |
|*  7 |     SORT JOIN                   |                    |      1 |  91594 |   805K|  3608K|   554   (2)| 00:00:07 |      1 |00:00:00.04 |
|   8 |      TABLE ACCESS FULL          | T4                 |      1 |  91594 |   805K|       |   199   (2)| 00:00:03 |  91594 |00:00:00.01 |
|   9 |    MERGE JOIN                   |                    |      1 |      1 |    38 |       |     6  (34)| 00:00:01 |      2 |00:00:00.01 |
|  10 |     SORT JOIN                   |                    |      1 |      1 |    29 |       |     3  (34)| 00:00:01 |      2 |00:00:00.01 |
|* 11 |      TABLE ACCESS BY INDEX ROWID| T2                 |      1 |      1 |    29 |       |     2   (0)| 00:00:01 |      2 |00:00:00.01 |
|* 12 |       INDEX RANGE SCAN          | T2_OBJECT_NAME_IDX |      1 |      2 |       |       |     1   (0)| 00:00:01 |      3 |00:00:00.01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|* 13 |     SORT JOIN                   |                    |      2 |      8 |    72 |       |     3  (34)| 00:00:01 |      2 |00:00:00.01 |
|  14 |      TABLE ACCESS BY INDEX ROWID| T4                 |      1 |      8 |    72 |       |     2   (0)| 00:00:01 |      8 |00:00:00.01 |
|* 15 |       INDEX RANGE SCAN          | T4_OWENR_IDX       |      1 |      8 |       |       |     1   (0)| 00:00:01 |      8 |00:00:00.01 |
--------------------------------------------------------------------------------------------------------------------------------------------------


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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
   5 - filter(("T2"."OBJECT_NAME"='C_COBJ#' OR ("T2"."OBJECT_NAME"='CHANNELS' AND "T2"."OBJECT_ID"=52946)))
   6 - access("T2"."OBJECT_NAME"='C_COBJ#')
   7 - access("T4"."OBJECT_ID"="T2"."OBJECT_ID")
       filter("T4"."OBJECT_ID"="T2"."OBJECT_ID")
  11 - filter(("T2"."OBJECT_ID"=52946 AND ("T2"."OBJECT_NAME"='C_COBJ#' OR ("T2"."OBJECT_NAME"='CHANNELS' AND "T2"."OBJECT_ID"=52946))))
  12 - access("T2"."OBJECT_NAME"='CHANNELS')
       filter(LNNVL("T2"."OBJECT_NAME"='C_COBJ#'))  --直接在索引扫描出的数据上过滤
  13 - access("T4"."OBJECT_ID"="T2"."OBJECT_ID")
       filter("T4"."OBJECT_ID"="T2"."OBJECT_ID")
  15 - access("T4"."OWNER"='BI')


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]  --联合（合并）两个MERGE JOIN  的结果集
   2 - "T2"."OBJECT_ID"[NUMBER,22], "T4"."OBJECT_ID"[NUMBER,22], "T2".ROWID[ROWID,10], "T2"."OBJECT_NAME"[VARCHAR2,128], "T4".ROWID[ROWID,10], "T4"."OWNER"[VARCHAR2,30]
   3 - "T2"."OBJECT_ID"[NUMBER,22], "T4"."OBJECT_ID"[NUMBER,22], "T2".ROWID[ROWID,10], "T2"."OBJECT_NAME"[VARCHAR2,128], "T4".ROWID[ROWID,10], "T4"."OWNER"[VARCHAR2,30]
   4 - (#keys=1) "T2"."OBJECT_ID"[NUMBER,22], "T2".ROWID[ROWID,10], "T2"."OBJECT_NAME"[VARCHAR2,128]
   5 - "T2".ROWID[ROWID,10], "T2"."OBJECT_NAME"[VARCHAR2,128], "T2"."OBJECT_ID"[NUMBER,22]
   6 - "T2".ROWID[ROWID,10], "T2"."OBJECT_NAME"[VARCHAR2,128]
   7 - (#keys=1) "T4"."OBJECT_ID"[NUMBER,22], "T4".ROWID[ROWID,10], "T4"."OWNER"[VARCHAR2,30]
   8 - "T4".ROWID[ROWID,10], "T4"."OWNER"[VARCHAR2,30], "T4"."OBJECT_ID"[NUMBER,22]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
   9 - "T2"."OBJECT_ID"[NUMBER,22], "T4"."OBJECT_ID"[NUMBER,22], "T2".ROWID[ROWID,10], "T2"."OBJECT_NAME"[VARCHAR2,128], "T4".ROWID[ROWID,10], "T4"."OWNER"[VARCHAR2,30]
   --ID9 连接T2和T4排序后的数据
  10 - (#keys=1) "T2"."OBJECT_ID"[NUMBER,22], "T2".ROWID[ROWID,10], "T2"."OBJECT_NAME"[VARCHAR2,128]  --对T2表查询出的数据进行排序
  11 - "T2".ROWID[ROWID,10], "T2"."OBJECT_NAME"[VARCHAR2,128], "T2"."OBJECT_ID"[NUMBER,22]
  12 - "T2".ROWID[ROWID,10], "T2"."OBJECT_NAME"[VARCHAR2,128]
  13 - (#keys=1) "T4"."OBJECT_ID"[NUMBER,22], "T4".ROWID[ROWID,10], "T4"."OWNER"[VARCHAR2,30]  --对T4表查询出的数据进行排序
  14 - "T4".ROWID[ROWID,10], "T4"."OWNER"[VARCHAR2,30], "T4"."OBJECT_ID"[NUMBER,22]
  15 - "T4".ROWID[ROWID,10], "T4"."OWNER"[VARCHAR2,30]




解析执行计划：



执行计划分析：该执行计划把OR 谓词转换为两个SQL的联合查询
第一步：执行ID6，对T2_OBJECT_NAME_IDX索引进行INDEX RANGE SCAN，访问索引的条件时"T2"."OBJECT_NAME"='C_COBJ#'
        （access("T2"."OBJECT_NAME"='C_COBJ#') ）。
第二步：执行ID5，访问路径为TABLE ACCESS BY INDEX ROWID  T2（通过ROWID回表T2），从表T2查询出数据后，进行过滤
        filter(("T2"."OBJECT_NAME"='C_COBJ#' OR ("T2"."OBJECT_NAME"='CHANNELS' AND "T2"."OBJECT_ID"=52946)))
第三步：执行ID4操作为SORT JOIN 对T2表的结果集进行排序。
第四步：执行ID8 操作为TABLE ACCESS FULL 对象为T4表，对T4表进行全表扫描。
第五步：执行ID7 操作为 SORT JOIN 对T4 表的结果集进行排序。Predicate Information 谓词信息
        access("T4"."OBJECT_ID"="T2"."OBJECT_ID")
        filter("T4"."OBJECT_ID"="T2"."OBJECT_ID")
        有谓词信息可以看出在ID 7 对T4和T2表进行了连接，由此判断ID3 和 ID7 合并为一步执行了。
第六步：执行ID 12 操作为 INDEX RANGE SCAN  操作的对象为T2_OBJECT_NAME_IDX索引，谓词信息
        access("T2"."OBJECT_NAME"='CHANNELS')
        filter(LNNVL("T2"."OBJECT_NAME"='C_COBJ#'))
        解释：对索引T2_OBJECT_NAME_IDX进行索引访问扫描，使用条件"T2"."OBJECT_NAME"='CHANNELS'
        驱劢访问索引，直接在索引扫描出的数据上使用条件filter(LNNVL("T2"."OBJECT_NAME"='C_COBJ#')过滤。
第七步：执行 ID11 操作为 TABLE ACCESS BY INDEX ROWID ，对象为T2 表，谓词信息
        filter(("T2"."OBJECT_ID"=52946 AND ("T2"."OBJECT_NAME"='C_COBJ#' OR ("T2"."OBJECT_NAME"='CHANNELS' AND "T2"."OBJECT_ID"=52946))))
        解释：使用第六步获取的 ROWID进行T2的回表操作，通过回表把ROWID 相等的记录取出，然后使用谓词中的条件进行过滤。
第八步：执行 ID 10 操作为SORT JOIN ，对T2表查询出的数据进行排序
第九步：执行 ID 15 操作为 INDEX RANGE SCAN 对象为索引T4_OWENR_IDX 。谓词信息
        access("T4"."OWNER"='BI')
        解释：对索引T4_OWENR_IDX进行索引访问扫描，使用条件"T4"."OWNER"='BI'驱劢访问索引。
第十步：执行 ID 14 操作为 TABLE ACCESS BY INDEX ROWID 对象为T4表
        解释：使用第九步获取的 ROWID 对 T4表进行回表扫描。
第十一步：执行 ID 13 操作为 SORT JOIN ，谓词信息
         access("T4"."OBJECT_ID"="T2"."OBJECT_ID")
         filter("T4"."OBJECT_ID"="T2"."OBJECT_ID")
         解释：这一步其实把 ID 13 和 ID9合并为一步执行，首先对 T4表的结果集进行排序，然后进行T2和T4表连接。
第十二步：执行 ID 2 操作为 CONCATENATION （联合操作）
         解释：联合第五步和第十一步的结果集。
第十三步：最后执行 ID 1
        

总结：
    1、解析执行计划是先理顺执行步骤。
    2、按执行步骤解析执行计划，解析时要结合Operation 、Name  、Predicate Information和Column Projection Information
       进行分析执行计划。