BITMAP CONVERSION FROM ROWIDS 適用場景

zhenggaona1發表於2017-09-12
SQL> SELECT COUNT (*)
   2   FROM (SELECT DISTINCT prpcmain.*
  3                     FROM prpcmain
  4                    WHERE prpcmain.riskcode IN ('0506', '0590', '0507', '0508')
  5                      AND SUBSTR (othflag, 4, 1) != '1'
   6                     AND underwriteflag = '4'
  7                      AND NOT EXISTS (
  8                             SELECT 1
  9                               FROM prpjpayexchsub a, prpjpayexch b
 10                              WHERE a.certino = prpcmain.policyno
 11                                AND a.exchangeno = b.exchangeno
 12                                AND b.paystatus = '1')
 13                      AND (   prpcmain.comcode LIKE '2510183%'
 14                           OR prpcmain.comcode IN ('2510183601')
 15                           OR (   prpcmain.handlercode = '251000149'
 16                               OR prpcmain.operatorcode = '251000149'
 17                               OR prpcmain.handler1code = '251000149'
 18                              )
 19                           OR 1 = 0
    20                       )
 21                      AND (   prpcmain.startdate >
 22                                            TO_DATE ('2017-08-31', 'YYYY-MM-DD')
 23                           OR (    prpcmain.startdate =
 24                                      TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
 25                                               'YYYY-MM-DD'
 26                                              )
 27                               AND prpcmain.starthour > 0
 28                              )
 29                          )
 30                      AND (SYSDATE - prpcmain.inputdate < 10)
  31                     AND NOT EXISTS (
  32                            SELECT 1
 33                               FROM prpjpaywechatorder wchatorder,
 34                                    prpjpaywechatdetail detail
 35                              WHERE wchatorder.exchangeno = detail.exchangeno
 36                                AND detail.certino = prpcmain.policyno
 37                                AND wchatorder.paystatus = '1')
...........................以下省略

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |     1 |   107 |  1580   (1)| 00:00:19 |
|   1 |  SORT AGGREGATE                   |                            |     1 |   107 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID     | PRPCMAIN                   |     1 |   107 |  1552   (1)| 00:00:19 |
|*  3 |    INDEX RANGE SCAN               | IDX_PRPCMAIN_151128        |     2 |       |  1550   (1)| 00:00:19 |
|   4 |     NESTED LOOPS                  |                            |     2 |   112 |     8   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                            |     2 |   112 |     8   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID | PRPJPAYEXCHSUB             |     2 |    76 |     5   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN           | IDX_PRPJPAYEXCHSUB_CERTINO |     2 |       |     4   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN           | PK_JPAYEXCH                |     1 |       |     1   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS BY INDEX ROWID  | PRPJPAYEXCH                |     1 |    18 |     2   (0)| 00:00:01 |
|  10 |     NESTED LOOPS                  |                            |     2 |   112 |     4   (0)| 00:00:01 |
|  11 |      NESTED LOOPS                 |                            |     2 |   112 |     4   (0)| 00:00:01 |
|  12 |       TABLE ACCESS BY INDEX ROWID | PRPJPAYWECHATDETAIL        |     2 |    76 |     2   (0)| 00:00:01 |
|* 13 |        INDEX RANGE SCAN           | IDX_PRPJPAYDTL_CERTINO     |     2 |       |     1   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN           | PK_PRPJPAYWECHATORDER      |     1 |       |     0   (0)| 00:00:01 |
|* 15 |      TABLE ACCESS BY INDEX ROWID  | PRPJPAYWECHATORDER         |     1 |    18 |     1   (0)| 00:00:01 |
|  16 |     NESTED LOOPS                  |                            |     2 |   110 |     4   (0)| 00:00:01 |
|  17 |      NESTED LOOPS                 |                            |     2 |   110 |     4   (0)| 00:00:01 |
|  18 |       TABLE ACCESS BY INDEX ROWID | PRPJPOSEXCHZBFDETAIL       |     2 |    74 |     3   (0)| 00:00:01 |
|* 19 |        INDEX RANGE SCAN           | IDX_CERTINO                |     2 |       |     1   (0)| 00:00:01 |
|* 20 |       INDEX UNIQUE SCAN           | PK_PRPJPOSEXCHZBFORDER     |     1 |       |     0   (0)| 00:00:01 |
|* 21 |      TABLE ACCESS BY INDEX ROWID  | PRPJPOSEXCHZBFORDER        |     1 |    18 |     1   (0)| 00:00:01 |
|  22 |     NESTED LOOPS                  |                            |     1 |    68 |    12   (0)| 00:00:01 |
|  23 |      NESTED LOOPS                 |                            |     2 |    68 |    12   (0)| 00:00:01 |
|  24 |       NESTED LOOPS                |                            |     2 |   112 |     9   (0)| 00:00:01 |
|  25 |        TABLE ACCESS BY INDEX ROWID| PRPJPAYEXCHSUB             |     2 |    76 |     5   (0)| 00:00:01 |
|* 26 |         INDEX RANGE SCAN          | IDX_PRPJPAYEXCHSUB_CERTINO |     2 |       |     4   (0)| 00:00:01 |
|* 27 |        TABLE ACCESS BY INDEX ROWID| PRPJPAYEXCH                |     1 |    18 |     2   (0)| 00:00:01 |
|* 28 |         INDEX UNIQUE SCAN         | PK_JPAYEXCH                |     1 |       |     1   (0)| 00:00:01 |
|* 29 |       INDEX UNIQUE SCAN           | PK_JCHECK                  |     1 |       |     1   (0)| 00:00:01 |
|* 30 |      TABLE ACCESS BY INDEX ROWID  | PRPJCHECK                  |     1 |    12 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
以上是最佳化前執行計劃
以上sql語句先完成子查詢後,回主表prpcmain 透過低效索引IDX_PRPCMAIN_151128 條件underwriteflag = '4'先到表中取數,再透過兩個連線欄位進行連線過濾,
執行時間過長。由於多個session 併發執行,導致cpu資源耗盡

第一感覺是既然索引低效,不妨使用no_index 阻止使用這個索引試試,看看最佳化器會怎樣選擇執行計劃
SQL>                                                                                                                                                                                   
SQL> SELECT COUNT (*)
  2    FROM (SELECT/*+ no_index(prpcmain IDX_PRPCMAIN_151128)*/ DISTINCT prpcmain.*
  3                     FROM prpcmain
....
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                            |     1 |   115 |       |  2435   (1)| 00:00:30 |
|   1 |  SORT AGGREGATE                        |                            |     1 |   115 |       |            |          |
|   2 |   NESTED LOOPS ANTI                    |                            |     1 |   115 |       |  2435   (1)| 00:00:30 |
|   3 |    NESTED LOOPS ANTI                   |                            |     1 |   113 |       |  2423   (1)| 00:00:30 |
|   4 |     NESTED LOOPS ANTI                  |                            |     1 |   111 |       |  2415   (1)| 00:00:29 |
|   5 |      NESTED LOOPS ANTI                 |                            |     1 |   109 |       |  2411   (1)| 00:00:29 |
|*  6 |       TABLE ACCESS BY INDEX ROWID      | PRPCMAIN                   |     1 |   107 |       |  2408   (1)| 00:00:29 |
|   7 |        BITMAP CONVERSION TO ROWIDS     |                            |       |       |       |            |          |
|   8 |         BITMAP AND                     |                            |       |       |       |            |          |
|   9 |          BITMAP OR                     |                            |       |       |       |            |          |
|  10 |           BITMAP CONVERSION FROM ROWIDS|                            |       |       |       |            |          |
|  11 |            SORT ORDER BY               |                            |       |       |       |            |          |
|* 12 |             INDEX RANGE SCAN           | IDX_PRPCMAIN_1             |       |       |       |    42   (0)| 00:00:01 |
|  13 |           BITMAP CONVERSION FROM ROWIDS|                            |       |       |       |            |          |
|  14 |            SORT ORDER BY               |                            |       |       |       |            |          |
|* 15 |             INDEX RANGE SCAN           | IDX_PRPCMAIN_1             |       |       |       |    42   (0)| 00:00:01 |
|  16 |           BITMAP CONVERSION FROM ROWIDS|                            |       |       |       |            |          |
|* 17 |            INDEX RANGE SCAN            | IDX_PRPCMAIN_OPERATORCODE  |       |       |       |    12   (0)| 00:00:01 |
|  18 |           BITMAP CONVERSION FROM ROWIDS|                            |       |       |       |            |          |
|* 19 |            INDEX RANGE SCAN            | IDX_PRPCMAIN_HANDLERCODE   |       |       |       |     6   (0)| 00:00:01 |
|  20 |           BITMAP CONVERSION FROM ROWIDS|                            |       |       |       |            |          |
|* 21 |            INDEX RANGE SCAN            | IDX_PRPCMAIN_HANDLER1CODE  |       |       |       |     6   (0)| 00:00:01 |
|  22 |          BITMAP OR                     |                            |       |       |       |            |          |
|  23 |           BITMAP CONVERSION FROM ROWIDS|                            |       |       |       |            |          |
|  24 |            SORT ORDER BY               |                            |       |       |  3208K|            |          |
|* 25 |             INDEX RANGE SCAN           | IDX_PRPCMAIN_STARTDATE     |       |       |       |   633   (1)| 00:00:08 |
|  26 |           BITMAP CONVERSION FROM ROWIDS|                            |       |       |       |            |          |
|* 27 |            INDEX RANGE SCAN            | IDX_PRPCMAIN_STARTDATE     |       |       |       |    19   (0)| 00:00:01 |
|  28 |       VIEW PUSHED PREDICATE            | VW_SQ_3                    |     1 |     2 |       |     3   (0)| 00:00:01 |

透過使用no_index提示,sql執行由幾乎無法完成,到30多秒,邏輯讀由200多萬 降到了 1500多
看來效果明顯,透過觀察執行計劃,由原來的後回主表,改為先使用BITMAP CONVERSION 
把多個b tree 索引轉化為一個點陣圖,到表內一次取出資料,然後根據連線欄位上的資料傳入謂詞到各個
子查詢 not exists 
透過測試,也可以使用提示  /*+ index_combine(prpcmain)*/ 來主動要求最佳化器使用這個執行計劃,
效果略有不同的是,把 in 子句也進行了 BITMAP CONVERSION ,其效果不如最後過濾 in 子句條件,邏輯讀略高,在5萬多。

最終使用提示 
 /*+ index_combine(prpcmain IDX_PRPCMAIN_1)*/ 
指定一個或者多個 or 條件內使用的索引,可以避免in 條件也進行轉換

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23944170/viewspace-2144833/,如需轉載,請註明出處,否則將追究法律責任。

相關文章