BITMAP CONVERSION FROM ROWIDS 適用場景
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 條件也進行轉換
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- bitmap conversion from rowids
- oracle sql hint提示_BITMAP CONVERSION FROM ROWIDSOracleSQL
- BITMAP CONVERSION TO ROWIDS
- 執行計劃出現BITMAP CONVERSION TO ROWIDS的解釋!
- Spark適用於哪些場景？不適用於哪些場景？Spark
- CDN適用哪些場景？
- INDEX SKIP SCAN適用場景Index
- Redis 中 BitMap 的使用場景Redis
- Streaming特性和適用場景
- 設計模式適用場景整理設計模式
- SPM適用的場景和示例
- MongoDB的優勢和適用場景MongoDB
- 企業雲盤適用哪些應用場景
- 區塊鏈不適用的若干場景區塊鏈
- 物件儲存適用於哪些場景？這5個場景皆可使用！物件
- list與Set、Map區別及適用場景
- NIO是什麼？適用於何種場景？
- 全息投影技術所適用的場景分析
- 企業API閘道器適用業務場景API
- 談談 Redux 與 Mobx 思想的適用場景Redux
- 本地索引和全域性索引的適用場景索引
- 低程式碼適用於哪些應用開發場景
- 輪換代理介紹及適用場景介紹
- 機械硬碟與固態硬碟的適用場景硬碟
- 自定義npm模組包——打包後適用多場景NPM
- TokuDB的優缺點和適用場景介紹
- NodeJS優缺點及適用場景討論NodeJS
- 什麼場景適合mongodbMongoDB
- Kotlin邊用邊學：Inline Functions的適用場景KotlininlineFunction
- 閉鎖和柵欄的區分以及適用場景
- 分散式事務解決方案與適用場景分析分散式
- Disruptor適合這種場景嗎
- invalid conversion from ‘LRUCache*‘ to ‘int‘ [-fpermissive] /new的使用
- 箭頭函式適用場景及需要注意的地方函式
- flexbox（彈性盒佈局模型）,以及適用場景Flex模型
- ClickHouse 與 MySQL 資料庫適用場景對比總結MySql資料庫
- ClickHouse(01)什麼是ClickHouse,ClickHouse適用於什麼場景
- 大資料分析： SAP Sybase IQ功能特性及適用場景大資料