循序漸進調優union相關的sql

檢視這條語句對應的執行計劃資訊，如下：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6b1yv0ucyjsk3, child number 0
-------------------------------------
/* TEST_TESTGetSubscriberNumber_selectGetMobileNo_0 */ SELECT
TEST_TEST_SUBSCRIBER_FA_V.MOBILE_NO, TEST_ACCOUNT.ACCOUNT_ID FROM
TEST_ACCOUNT ,TEST_TEST_SUBSCRIBER_FA_V  WHERE TEST_ACCOUNT.ACCOUNT_ID =
TEST_TEST_SUBSCRIBER_FA_V.BAN  AND TEST_ACCOUNT.ACCOUNT_ID = :1

Plan hash value: 1685959897

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |       |       |       | 56898 (100)|          |
|   1 |  NESTED LOOPS                      |                        |   536K|    61M|       | 56898   (1)| 00:11:23 |
|*  2 |   INDEX UNIQUE SCAN                | TEST_ACCOUNT_PK        |     1 |     6 |       |     1   (0)| 00:00:01 |
|   3 |   VIEW                             | TEST_TEST_SUBSCRIBER_FA_V |536K|    58M|       | 56897   (1)| 00:11:23 |
|   4 |    SORT UNIQUE                     |                        |   536K|    98M|   113M| 56897 (100)| 00:11:23 |
|   5 |     UNION-ALL                      |                        |       |       |       |            |          |
|   6 |      NESTED LOOPS                  |                        |       |       |       |            |          |
|   7 |       NESTED LOOPS                 |                        |     1 |    89 |       |     3   (0)| 00:00:01 |
|   8 |        NESTED LOOPS                |                        |     1 |    31 |       |     2   (0)| 00:00:01 |
|*  9 |         INDEX RANGE SCAN           | TEST_PAY_CHANNEL_1IX   |     1 |    12 |       |     1   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| TEST_DISTRIBUTE        |     1 |    19 |       |     1   (0)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | TEST_DISTRIBUTE_3IX    |     1 |       |       |     1   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN           | SUBSCRIBER_PK          |     1 |       |       |     1   (0)| 00:00:01 |
|* 13 |       TABLE ACCESS BY INDEX ROWID  | SUBSCRIBER             |     1 |    58 |       |     1   (0)| 00:00:01 |
|* 14 |      VIEW                          |                        |   536K|    98M|       | 34296   (1)| 00:06:52 |
|* 15 |       WINDOW SORT PUSHED RANK      |                        |   536K|    45M|    51M| 34296   (1)| 00:06:52 |
|* 16 |        HASH JOIN                   |                        |   536K|    45M|    28M| 23341   (1)| 00:04:41 |
|  17 |         INDEX FULL SCAN            | TEST_PAY_CHANNEL_1IX   |  1259K|    14M|       |  1254   (1)| 00:00:16 |
|* 18 |         HASH JOIN                  |                        |   536K|    39M|    26M| 18391   (1)| 00:03:41 |
|* 19 |          TABLE ACCESS FULL         | SUBSCRIBER             |   397K|    21M|       |  8527   (1)| 00:01:43 |
|* 20 |          TABLE ACCESS FULL         | TEST_DISTRIBUTE        |  1696K|    30M|       |  6046   (1)| 00:01:13 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TEST_ACCOUNT"."ACCOUNT_ID"=TO_NUMBER(:1))
   9 - access("CPC"."BAN"=TO_NUMBER(:1))
  10 - filter("EG_DIST_TYPE"='D')
  11 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN" AND "ED"."EXPIRATION_DATE" IS NULL)
       filter("ED"."EXPIRATION_DATE" IS NULL)
  12 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
  13 - filter(("SUBSCRIBER"."SUB_STATUS"<>'C' AND "SUBSCRIBER"."SUB_STATUS"<>'L' AND
              "SUBSCRIBER"."SUB_STATUS"<>'T'))
  14 - filter(("RANK"=1 AND "BAN"=TO_NUMBER(:1)))
  15 - filter(ROW_NUMBER() OVER ( PARTITION BY "SUBSCRIBER"."SUBSCRIBER_NO" ORDER BY
              "ED"."EXPIRATION_DATE")<=1)
  16 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN")
  18 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
  19 - filter(("SUBSCRIBER"."SUB_STATUS"='C' OR "SUBSCRIBER"."SUB_STATUS"='L' OR
              "SUBSCRIBER"."SUB_STATUS"='T'))
  20 - filter("EG_DIST_TYPE"='D')

從執行計劃可以看出，瓶頸就在於有兩個表走了全表掃描。那兩個表資料也不少。
統計資訊如下：
Elapsed: 00:00:03.53

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

其中TEST_TEST_SUBSCRIBER_FA_V 是一個檢視，裡面使用了Union,（另外關於這個union的地方和開發確認過，暫時還不能改為union all)。
把檢視的內容填進去，sql語句就成了如下的樣子，在資料庫裡執行的時候也基本是這個樣子的。對於在查詢中沒有用到的欄位都給註釋掉了。標註為灰色。
SELECT  MOBILE_NO, TEST_ACCOUNT.ACCOUNT_ID
FROM
(
SELECT cpc.ban,
          subscriber.prim_resource_val MOBILE_NO,
       --   subscriber.init_act_date,
        --  SUBSCRIBER.CUSTOMER_ID,
        --  subscriber.subscriber_no,
        --  SUBSCRIBER.SUBSCRIBER_TYPE,
         -- SUBSCRIBER.SUB_STATUS,
        --  SUBSCRIBER.SUB_STS_RSN_CD,
       --   SUBSCRIBER.SUB_STATUS_DATE,
       --   SUBSCRIBER.EFFECTIVE_DATE,
          1 RANK
     FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc
    WHERE     cpc.pym_channel_no = ed.target_pcn
          AND ed.agreement_no = subscriber.subscriber_no
          AND eg_dist_type = 'D'
          AND ed.expiration_date IS NULL
          AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
          )temp, TEST_ACCOUNT
          where TEST_ACCOUNT.account_id=temp.ban and TEST_ACCOUNT.account_id=10001245
   UNION
  SELECT   MOBILE_NO,  TEST_ACCOUNT.ACCOUNT_ID
FROM
(
   SELECT "BAN","PRIM_RESOURCE_VAL" MOBILE_NO--,"INIT_ACT_DATE","CUSTOMER_ID","SUBSCRIBER_NO","SUBSCRIBER_TYPE","SUB_STATUS","SUB_STS_RSN_CD","SUB_STATUS_DATE","EFFECTIVE_DATE","RANK"
     FROM (SELECT cpc.ban,
                  subscriber.prim_resource_val ,
               --   subscriber.init_act_date,
               --   SUBSCRIBER.CUSTOMER_ID,
               --   subscriber.subscriber_no,
               --   SUBSCRIBER.SUBSCRIBER_TYPE,
               --   SUBSCRIBER.SUB_STATUS,
               --   SUBSCRIBER.SUB_STS_RSN_CD,
                 -- SUBSCRIBER.SUB_STATUS_DATE,
            --      SUBSCRIBER.EFFECTIVE_DATE,
                  ROW_NUMBER ()
                  OVER (
                     PARTITION BY subscriber.subscriber_no
                     ORDER BY
                        ed.expiration_date, subscriber.subscriber_no DESC)
                     AS RANK
             FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc
            WHERE     cpc.pym_channel_no = ed.target_pcn
                  AND ed.agreement_no = subscriber.subscriber_no
                  AND eg_dist_type = 'D'
                  AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T'))
    WHERE RANK = 1
    )temp, TEST_ACCOUNT
    where TEST_ACCOUNT.account_id=temp.ban and TEST_ACCOUNT.account_id=10001245


檢視走全表掃描的兩個表，TEST_pay_channel上有一個索引列中含有ban,從如上的執行計劃中看出，沒有嘗試走索引，而是走了全表掃描，受此影響，TEST_distribute 也做了全表掃描。
根據查詢條件TEST_ACCOUNT.account_id和TEST_pay_channel的ban是關聯的，如果可以走索引的話，效率會大大提高。
TEST_PAY_CHANNEL和 TEST_DISTRIBUTE 中索引的資訊如下：
INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                    TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
TEST_PAY_CHANNEL_1IX            INDXS01    NORMAL     NONUNIQUE NO  BAN,BEN, CHANNEL_NO            TABLE      VALID     1229972 01-JUL-14 N
TEST_DISTRIBUTE_3IX             INDXS01    NORMAL     NONUNIQUE NO  TARGET_PCN,AGR_NO,EFFECT_DATE  TABLE      VALID     1597330 01-JUL-14 N

如果根據ban來關聯的話，至少可以走一個range  index scan或者skip/scan了.
如果可以走索引的話。條件 “cpc.pym_channel_no = ed.target_pcn”對應的索引就可以啟用了，test_distribute表也就不走全表掃描了,可以走range scan/或者skip scan了。

現在的工作就是來看看能不能不改動邏輯來看看能做些什麼。
改動後的sql語句如下。首先是棄用了原有的檢視。然後在union的兩個子查詢中使用TEST_ACCOUNT來和TEST_PAY_CHANNEL來做關聯。雖然關聯的表多了一個，但是因為都可以走索引，比全表掃描要效率高很多了。

SELECT  MOBILE_NO,  ACCOUNT_ID
FROM
(
  select prim_resource_val MOBILE_NO,ACCOUNT_ID,rank
  from  (
    SELECT  cpc.ban,TEST_ACCOUNT.ACCOUNT_ID,
            subscriber.prim_resource_val,
            1 RANK
       FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc,TEST_ACCOUNT
      WHERE     cpc.pym_channel_no = ed.target_pcn
            AND ed.agreement_no = subscriber.subscriber_no
            AND eg_dist_type = 'D'
            AND ed.expiration_date IS NULL
            AND SUBSCRIBER.SUB_STATUS NOT IN ('C', 'L', 'T')
           AND        TEST_ACCOUNT.ACCOUNT_ID = cpc.BAN  AND TEST_ACCOUNT.ACCOUNT_ID = 10001245
       )TEST_TEST_SUBSCRIBER
  UNION
   select TEST_TEST_SUBSCRIBER.prim_resource_val MOBILE_NO,TEST_TEST_SUBSCRIBER.ACCOUNT_ID,rank
   from
  (SELECT cpc.ban, subscriber.prim_resource_val,  TEST_ACCOUNT.ACCOUNT_ID ,
                   ROW_NUMBER ()
                   OVER (
                      PARTITION BY subscriber.subscriber_no
                      ORDER BY
                         ed.expiration_date, subscriber.subscriber_no DESC)
                      AS RANK
              FROM subscriber, TEST_distribute ed, TEST_pay_channel cpc,TEST_ACCOUNT
             WHERE     cpc.pym_channel_no = ed.target_pcn
                   AND ed.agreement_no = subscriber.subscriber_no
                   AND eg_dist_type = 'D'
                   AND SUBSCRIBER.SUB_STATUS IN ('C', 'L', 'T')
                              AND TEST_ACCOUNT.ACCOUNT_ID = cpc.BAN  AND TEST_ACCOUNT.ACCOUNT_ID =10001245
  ) TEST_TEST_SUBSCRIBER
  ) where rank=1

來看看執行計劃和統計資訊和執行速度。
    首先是執行速度，用了0.01秒，相比原來的3秒左右提高了不少，毫秒級的速度。
    cpu的消耗一下子到了11，提高了很多倍
    兩個全本走全表掃描的表，現在都走了index range scan.
    統計資訊的提升，也提高了很多。從原來的96346降低到了目前的23

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1502756759
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     2 |   256 |    11  (28)| 00:00:01 |
|   1 |  VIEW                               |                      |     2 |   256 |    11  (28)| 00:00:01 |
|   2 |   SORT UNIQUE                       |                      |     2 |   184 |    11  (64)| 00:00:01 |
|   3 |    UNION-ALL                        |                      |       |       |            |          |
|   4 |     NESTED LOOPS                    |                      |       |       |            |          |
|   5 |      NESTED LOOPS                   |                      |     1 |    56 |     4   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                  |                      |     1 |    37 |     3   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                 |                      |     1 |    18 |     2   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN           | TEST_ACCOUNT_PK       |     1 |     6 |     1   (0)| 00:00:01 |
|*  9 |         INDEX RANGE SCAN            | TEST_PAY_CHANNEL_1IX  |     1 |    12 |     1   (0)| 00:00:01 |
|* 10 |        TABLE ACCESS BY INDEX ROWID  | TEST_DISTRIBUTE     |     1 |    19 |     1   (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN            | TEST_DISTRIBUTE_3IX |     1 |       |     1   (0)| 00:00:01 |
|* 12 |       INDEX UNIQUE SCAN             | SUBSCRIBER_PK        |     1 |       |     1   (0)| 00:00:01 |
|* 13 |      TABLE ACCESS BY INDEX ROWID    | SUBSCRIBER           |     1 |    19 |     1   (0)| 00:00:01 |
|* 14 |     VIEW                            |                      |     1 |   128 |     5  (20)| 00:00:01 |
|* 15 |      WINDOW SORT PUSHED RANK        |                      |     1 |    56 |     5  (20)| 00:00:01 |
|  16 |       NESTED LOOPS                  |                      |       |       |            |          |
|  17 |        NESTED LOOPS                 |                      |     1 |    56 |     4   (0)| 00:00:01 |
|  18 |         NESTED LOOPS                |                      |     1 |    37 |     3   (0)| 00:00:01 |
|  19 |          NESTED LOOPS               |                      |     1 |    18 |     2   (0)| 00:00:01 |
|* 20 |           INDEX UNIQUE SCAN         | TEST_ACCOUNT_PK       |     1 |     6 |     1   (0)| 00:00:01 |
|* 21 |           INDEX RANGE SCAN          | TEST_PAY_CHANNEL_1IX  |     1 |    12 |     1   (0)| 00:00:01 |
|* 22 |          TABLE ACCESS BY INDEX ROWID| TEST_DISTRIBUTE     |     1 |    19 |     1   (0)| 00:00:01 |
|* 23 |           INDEX RANGE SCAN          | TEST_DISTRIBUTE_3IX |     1 |       |     1   (0)| 00:00:01 |
|* 24 |         INDEX UNIQUE SCAN           | SUBSCRIBER_PK        |     1 |       |     1   (0)| 00:00:01 |
|* 25 |        TABLE ACCESS BY INDEX ROWID  | SUBSCRIBER           |     1 |    19 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("TEST_ACCOUNT"."ACCOUNT_ID"=10001245)
   9 - access("CPC"."BAN"=10001245)
  10 - filter("EG_DIST_TYPE"='D')
  11 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN" AND "ED"."EXPIRATION_DATE" IS NULL)
       filter("ED"."EXPIRATION_DATE" IS NULL)
  12 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
  13 - filter("SUBSCRIBER"."SUB_STATUS"<>'C' AND "SUBSCRIBER"."SUB_STATUS"<>'L' AND
              "SUBSCRIBER"."SUB_STATUS"<>'T')
  14 - filter("RANK"=1)
  15 - filter(ROW_NUMBER() OVER ( PARTITION BY "SUBSCRIBER"."SUBSCRIBER_NO" ORDER BY
              "ED"."EXPIRATION_DATE")<=1)
  20 - access("TEST_ACCOUNT"."ACCOUNT_ID"=10001245)
  21 - access("CPC"."BAN"=10001245)
  22 - filter("EG_DIST_TYPE"='D')
  23 - access("CPC"."PYM_CHANNEL_NO"="ED"."TARGET_PCN")
  24 - access("ED"."AGREEMENT_NO"="SUBSCRIBER"."SUBSCRIBER_NO")
  25 - filter("SUBSCRIBER"."SUB_STATUS"='C' OR "SUBSCRIBER"."SUB_STATUS"='L' OR
              "SUBSCRIBER"."SUB_STATUS"='T')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          3  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
有了一定的資料保證，就需要在測試環境中進行了模擬測試，可以比對是否和預期的一樣有極大的提升。