or改寫為union all效能提升n倍

SQL> explain plan for SELECT SUM(OCCOUNT) as EVENTCOUNT , F_4 FROM binbin1 WHERE binbin1.EUIDCODE like '19739-19740-704013-%' or binbin1.EUIDCODE like '19739-19740-704018-%' or binbin1.EUIDCODE like '19339-19440-704274-%' AND STARTTIME>=1375237687118 AND
STARTTIME<1375927747118 GROUP BY F_4 HAVING SUM(OCCOUNT)>=2;
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 996176126

-----------------------------------------------------------------------------------------------------| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |-----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |             |   658M|   175G|    33M  (2)|111:31:45 |       |       ||*  1 |  FILTER               |             |       |       |            |          |       |       ||   2 |   HASH GROUP BY       |             |   658M|   175G|    33M  (2)|111:31:45 |       |       ||   3 |    PARTITION RANGE ALL|             |   658M|   175G|    33M  (2)|111:14:43 |     1 |   773 ||*  4 |     TABLE ACCESS FULL | binbin1 |   658M|   175G|    33M  (2)|111:14:43 |     1 |   773 |-----------------------------------------------------------------------------------------------------

 

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

   1 - filter(SUM("OCCOUNT")>=2)
   4 - filter("binbin1"."EUIDCODE" LIKE '19739-19740-704013-%' OR
              "binbin1"."EUIDCODE" LIKE '19739-19740-704018-%' OR "STARTTIME">=1375237687118 AND
              "STARTTIME"<1375927747118 AND "binbin1"."EUIDCODE" LIKE '19339-19440-704274-%')

19 rows selected.

上面這個sql跑了一個半小時，將or改寫為union all之後如下：
SQL>  select SUM(OCCOUNT) as EVENTCOUNT,F_4 from (SELECT  OCCOUNT, F_4 FROM binbin1 t WHERE EUIDCODE like '19739-19740-704013-%'
  2   AND t.STARTTIME>=1375237687118 AND t.STARTTIME<1375927747118
  3  union all SELECT OCCOUNT, F_4 FROM binbin1 t WHERE t.EUIDCODE like '19739-19740-704018-%'

  4  AND t.STARTTIME>=1375237687118 AND t.STARTTIME<1375927747118
  5  union all SELECT OCCOUNT, F_4 FROM binbin1 t WHERE t.EUIDCODE like '19339-19440-704274-%'

  6  AND t.STARTTIME>=1375237687118 AND t.STARTTIME<1375927747118) GROUP BY F_4 HAVING SUM(OCCOUNT)>=2;

no rows selected

Elapsed: 00:00:31.42

Execution Plan
----------------------------------------------------------
Plan hash value: 3849714746

----------------------------------------------------------------------------------------------------------| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |----------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT           |             |  2531K|   345M|   129K  (2)| 00:25:52 |       |       ||*  1 |  FILTER                    |             |       |       |            |          |       |       ||   2 |   HASH GROUP BY            |             |  2531K|   345M|   129K  (2)| 00:25:52 |       |       ||   3 |    VIEW                    |             |  2531K|   345M|   129K  (2)| 00:25:49 |       |       ||   4 |     UNION-ALL              |             |       |       |            |          |       |       ||   5 |      PARTITION RANGE SINGLE|             |   843K|   230M| 43013   (2)| 00:08:37 |   750 |   750 ||*  6 |       TABLE ACCESS FULL    | binbin1 |   843K|   230M| 43013   (2)| 00:08:37 |   750 |   750 ||   7 |      PARTITION RANGE SINGLE|             |   843K|   230M| 43013   (2)| 00:08:37 |   750 |   750 ||*  8 |       TABLE ACCESS FULL    | binbin1 |   843K|   230M| 43013   (2)| 00:08:37 |   750 |   750 ||   9 |      PARTITION RANGE SINGLE|             |   843K|   230M| 43013   (2)| 00:08:37 |   750 |   750 ||* 10 |       TABLE ACCESS FULL    | binbin1 |   843K|   230M| 43013   (2)| 00:08:37 |   750 |   750 |----------------------------------------------------------------------------------------------------------

 

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

   1 - filter(SUM("OCCOUNT")>=2)
   6 - filter("T"."STARTTIME">=1375237687118 AND "T"."STARTTIME"<1375927747118 AND "EUIDCODE"
              LIKE '19739-19740-704013-%')
   8 - filter("T"."STARTTIME">=1375237687118 AND "T"."STARTTIME"<1375927747118 AND "T"."EUIDCODE"
              LIKE '19739-19740-704018-%')
  10 - filter("T"."STARTTIME">=1375237687118 AND "T"."STARTTIME"<1375927747118 AND "T"."EUIDCODE"
              LIKE '19339-19440-704274-%')

Statistics
----------------------------------------------------------
          1  recursive calls
          3  db block gets
     700713  consistent gets
          0  physical reads
          0  redo size
        403  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed