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