当 SQL 语句中同时有 or 和子查询，这种情况下子查询无法展开（unnest），只能走 FILTER。遇到这种情况我们可以将 SQL 改写为 union，从而消除 FILTER。

    带有 or 子查询的写法与执行计划如下。

    1.  select *
    2.     from t1
    3.    where owner = 'SCOTT'
    4.      or object_id in (select object_id from t2);
    1. 72571 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 895956251
    7. ---------------------------------------------------------------------------
    8. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    9. ---------------------------------------------------------------------------
    10. |   0 | SELECT STATEMENT   |      |  3378 |   682K|   235   (1)| 00:00:03 |
    11. |*  1 |  FILTER            |      |       |       |            |          |
    12. |   2 |   TABLE ACCESS FULL| T1   | 56766 |    11M|   235   (1)| 00:00:03 |
    13. |*  3 |   TABLE ACCESS FULL| T2   |   734 |  9542 |     2   (0)| 00:00:01 |
    14. ---------------------------------------------------------------------------
    16. Predicate Information (identified by operation id):
    17. ---------------------------------------------------
    19.    1 - filter("OWNER"='SCOTT' OR  EXISTS (SELECT 0 FROM "T2" "T2" WHERE
    20.               "OBJECT_ID"=:B1))
    21.    3 - filter("OBJECT_ID"=:B1)

    改写为 union 的写法如下。

    1. select * from t1 where owner='SCOTT'
    2.   union
    3.   select * from t1 where object_id in(select object_id from t2);
    1. 72571 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 696035008
    7. --------------------------------------------------------------------------
    8. | Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|
    9. --------------------------------------------------------------------------
    10. |   0 | SELECT STATEMENT     |      | 56778 |    11M|       |  4088  (95)|
    11. |   1 |  SORT UNIQUE         |      | 56778 |    11M|    12M|  4088  (95)|
    12. |   2 |   UNION-ALL          |      |       |       |       |            |
    13. |*  3 |    TABLE ACCESS FULL | T1   |    12 |  2484 |       |   234   (1)|
    14. |*  4 |    HASH JOIN         |      | 56766 |    11M|  1800K|  1146   (1)|
    15. |   5 |     TABLE ACCESS FULL| T2   | 73407 |   931K|       |   234   (1)|
    16. |   6 |     TABLE ACCESS FULL| T1   | 56766 |    11M|       |   235   (1)|
    17. --------------------------------------------------------------------------
    19. Predicate Information (identified by operation id):
    20. ---------------------------------------------------
    22.    3 - filter("OWNER"='SCOTT')
    23.    4 - access("OBJECT_ID"="OBJECT_ID")

    改写为 union 之后，消除了 FILTER。如果无法改写 SQL，那么 SQL 就只能走 FILTER，这时我们需要在子查询表的连接列（t2.object_id）建立索引。