ORACLE b*tree索引执行计划中为什么会出现bitmap关键字

问题sql当前执行时间800ms左右，系统需要它在150ms以内出结果，出现问题的sql如下： SELECT /*+monitor test1 DYNAMIC_SAMPLING TMP 1 */          MIN(A.EU) OVER(PARTITION BY A.FINC) EU,          A...          B...   FROM CK.CHK_TMP TMP   JOIN EI_N.CPI_I B     ON TMP.KV = B.EID   JOIN (SELECT         /*+DYNAMIC_SAMPLING TMP 1 */          F.FINC,          SUM(F.SHAREPLACE) OVER(PARTITION BY F.FINC) SUMSREP,          F....           FROM CK.CHK_TMP TMP           JOIN EI_N.CPI_I D             ON TMP.KV = D.EID           JOIN EI_N.CPI_PR F             ON D.FINC = F.FINC) A     ON A.FINC = B.FINC  WHERE (B.SHARD <> A.SUMSREP OR        (B.SHARD IS NOT NULL AND A.SUMSREP IS NULL) OR        (B.SHARD IS NULL AND A.SUMSREP IS NOT NULL))    AND B.ISPUB = '0'    AND B.UPDATEDATE >= DATE '2013-1-1'     当前执行计划如下：  Plan Hash Value  : 1484034248 ------------------------------------------------------------------------------------------------------------ | Id   | Operation                       | Name                   | Rows    | Bytes     | Cost  | Time     | ------------------------------------------------------------------------------------------------------------ |    0 | SELECT STATEMENT                |                        |  326462 |  31340352 | 55991 | 00:00:03 | |    1 |   HASH UNIQUE                   |                        |  326462 |  31340352 | 55991 | 00:00:03 | |    2 |    WINDOW SORT                  |                        |  326462 |  31340352 | 55991 | 00:00:03 | |  * 3 |     HASH JOIN                   |                        |  326462 |  31340352 | 41668 | 00:00:02 | |  * 4 |      HASH JOIN                  |                        |    4620 |    258720 |   413 | 00:00:01 | |    5 |       TABLE ACCESS FULL         | CHK_TMP                |    8168 |    106184 |    29 | 00:00:01 | |  * 6 |       TABLE ACCESS FULL         | CPI_I                  |   11660 |    501380 |   384 | 00:00:01 | |    7 |      VIEW                       |                        | 1247605 |  49904200 | 41252 | 00:00:02 | |    8 |       WINDOW SORT               |                        | 1247605 |  78599115 | 41252 | 00:00:02 | |  * 9 |        HASH JOIN                |                        | 1247605 |  78599115 | 22374 | 00:00:01 | | * 10 |         HASH JOIN               |                        |    8168 |    253208 |   146 | 00:00:01 | |   11 |          TABLE ACCESS FULL      | CHK_TMP                |    8168 |    106184 |    29 | 00:00:01 | |   12 |          VIEW                   | index$_join$_006       |   20613 |    371034 |   116 | 00:00:01 | | * 13 |           HASH JOIN             |                        |         |           |       |          | |   14 |            INDEX FAST FULL SCAN | NNK_CPI_I              |   20613 |    371034 |    68 | 00:00:01 | |   15 |            INDEX FAST FULL SCAN | PK_CPI_I               |   20613 |    371034 |    78 | 00:00:01 | |   16 |         TABLE ACCESS FULL       | CPI_PR                 | 3148493 | 100751776 | 22221 | 00:00:01 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ------------------------------------------ * 3 - access("A"."FINC"="B"."FINC") * 3 - filter("B"."SHARD"<>"A"."SUMSREP" OR "A"."SUMSREP" IS NULL AND "B"."SHARD" IS NOT NULL OR "B"."SHARD" IS NULL AND "A"."SUMSREP" IS NOT NULL) * 4 - access("TMP"."KV"="B"."EID") * 6 - filter("B"."UPDATEDATE">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."ISPUB"=U'0') * 9 - access("D"."FINC"="F"."FINC") * 10 - access("TMP"."KV"="D"."EID") * 13 - access(ROWID=ROWID)     这个执行计划CHK_TMP和CPI_I表哈希连接，结果集记为A，CHK_TMP和CPI_PR都和CPI_I表连接，NNK_CPI_I（FINC）和PK_CPI_I CHK_TMP（eid）和做哈希连接，结果集与CHK_TMP哈希连接，结果集再与CPI_PR哈希连接，结果集记为B，然后A与B再做哈希连接。          第一个哈希连接，因为CPI_I表只有两万多条数据，所以这一步执行时间大致等于CPI_I表全表扫描的时间，所以瓶颈不在这里。     而CPI_PR表有3167993多完数据，这张表如果全表扫描了那么sql整体一定无法在150ms内执行完毕，而且两个索引的快速全扫描，虽然是多块读，但是这里的执行顺序是存在很大问题的，chk_tmp作为驱动表，应该以它为起始驱动，一步一步向后驱动，而不是其他条件结果再来与它驱动。而且有一点值得注意，CPI_PR表在连接列明明存在2个可用的索引，但是没有用到。          所以这里的瓶颈就是：三张表错误的连接方式，以及由于该原因导致的全表扫描。当然了根本原因还是连接方式的问题。          那么如何解决这个问题呢？          在join （。。。） A的内部加hint，ordered use_nl(TMP D F)，强制以TMP表作为驱动表，并且以NL的方式连接。因为CPI_PR表太大，哈希连接很耗时。当然这只是一次尝试。          这次sql的执行时间缩短到了80ms，通过了应用的硬性规定。下面是通过sqlmonitor抓出来的执行计划： Global Stats ================================================= | Elapsed |   Cpu   |  Other   | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls |  Gets  | ================================================= |    0.06 |    0.06 |     0.00 |     1 |   2163 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=440150911) ========================================================================================================================================================================== | Id |                 Operation                 |          Name           |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail | |    |                                           |                         | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   | ========================================================================================================================================================================== |  0 | SELECT STATEMENT                          |                         |         |      |           |        |     1 |          |       |          |                 | |  1 |   HASH UNIQUE                             |                         |    271K | 447K |           |        |     1 |          |       |          |                 | |  2 |    WINDOW SORT                            |                         |    271K | 447K |           |        |     1 |          |       |          |                 | |  3 |     NESTED LOOPS                          |                         |    271K | 426K |         1 |     +0 |     1 |        0 |       |          |                 | |  4 |      HASH JOIN                            |                         |    4620 |  413 |         1 |     +0 |     1 |       94 |    2M |          |                 | |  5 |       TABLE ACCESS FULL                   | CHK_TMP                 |    8168 |   29 |         1 |     +0 |     1 |      100 |       |          |                 | |  6 |       TABLE ACCESS FULL                   | CPI_I                   |   11660 |  384 |         1 |     +0 |     1 |    14512 |       |          |                 | |  7 |      VIEW PUSHED PREDICATE                |                         |       1 |   92 |         1 |     +0 |    94 |        0 |       |          |                 | |  8 |       WINDOW BUFFER                       |                         |       1 |   92 |         1 |     +0 |    94 |      473 |  6144 |          |                 | |  9 |        NESTED LOOPS                       |                         |       1 |   92 |         1 |     +0 |    94 |      473 |       |          |                 | | 10 |         NESTED LOOPS                      |                         |       1 |   92 |         1 |     +0 |    94 |      473 |       |          |                 | | 11 |          NESTED LOOPS                     |                         |       1 |   88 |         1 |     +0 |    94 |       94 |       |          |                 | | 12 |           TABLE ACCESS FULL               | CHK_TMP                 |    8168 |   29 |         1 |     +0 |    94 |     9400 |       |          |                 | | 13 |           BITMAP CONVERSION TO ROWIDS     |                         |       1 |   88 |         1 |     +0 |  9400 |       94 |       |          |                 | | 14 |            BITMAP AND                     |                         |         |      |         1 |     +0 |  9400 |       94 |       |          |                 | | 15 |             BITMAP CONVERSION FROM ROWIDS |                         |         |      |         1 |     +0 |  9400 |     9400 |       |          |                 | | 16 |              INDEX RANGE SCAN             | NNK_CPI_I               |       1 |      |         1 |     +0 |  9400 |     9400 |       |          |                 | | 17 |             BITMAP CONVERSION FROM ROWIDS |                         |         |      |         1 |     +0 |  9400 |     4988 |       |          |                 | | 18 |              INDEX RANGE SCAN             | PK_CPI_I                |       1 |      |         1 |     +0 |  9400 |     9400 |       |          |                 | | 19 |          INDEX RANGE SCAN                 | IDX_CPI_PR_FPP          |       1 |    3 |         1 |     +0 |    94 |      473 |       |          |                 | | 20 |         TABLE ACCESS BY INDEX ROWID       | CPI_PR                  |       1 |    4 |         1 |     +0 |   473 |      473 |       |          |                 | ==========================================================================================================================================================================  Plan Hash Value  : 440150911 sql monitor不存在每一步的注释，补充一下:  Predicate Information (identified by operation id): ------------------------------------------ * 4 - access("TMP"."KV"="B"."EID") * 6 - filter("B"."UPDATEDATE">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."ISPUB"=U'0') * 7 - filter("B"."SHARD"<>"A"."SUMSREP" OR "A"."SUMSREP" IS NULL AND "B"."SHARD" IS NOT NULL OR "B"."SHARD" IS NULL AND "A"."SUMSREP" IS NOT NULL) * 16 - access("D"."FINC"="B"."FINC") * 18 - access("TMP"."KV"="D"."EID") * 19 - access("F"."FINC"="B"."FINC") * 19 - filter("D"."FINC"="F"."FINC")     时间满足了要求，但是紧接着出现了一个非常有趣的执行计划。可以看到出现了BITMAP CONVERSION FROM ROWIDS等位图索引的执行计划。但是NNK_CPI_I和PK_CPI_I并非是位图索引。执行计划中可以看到，通过对两个索引的范围扫，将rowid转换为bitmap（BITMAP CONVERSION FROM ROWIDS）进行匹配（BITMAP AND），然后再转换为rowid（BITMAP CONVERSION TO ROWIDS），结果集与CHK_TMP表进行嵌套循环连接。可以看到之前谈过的sql的瓶颈问题中，仍然存在不合理的连接方式，同一张表的两个索引进行连接匹配。再考虑如下问题，以及为什么CPI_PR这张表最初没有全表扫描，就会明白问题出现在什么地方了。          首先，为什么B*TREE索引会在执行计划中出现位图索引的关键字呢？     众所周知，Oracle对执行计划的选择是按照cost值来计算的。虽然在这个案例中，我的优化方向是缩短执行时间，但是这并不是优化器的工作方式。如果把B*TREE索引转化为位图方式匹配，cost值小的话，那这种方法也可以被Oracle采用。但是这种方式的cost值高达447338，那么最开始是多少呢？只有55991。所以一般这种方式不会被Oracle选择，但是这种方式是客观存在的，只是因为cost值一般比较大，所以普通B*TREE索引看不到bitmap的关键字。出现这种方法，也就意味着表上的索引列选择性不好。注意这种方式非常消耗CPU，这对很多系统都很不友好。          结合CPI_PR的全表扫描，结论就很明显了：FINC列选择性很差，没有也不好创建合适的索引。      select count(distinct FINC) from ei_n.CPI_PR        COUNT(DISTINCTFINC) 1    17038  select count(*)  from ei_n.CPI_PR     COUNT(*) 1    3167996     可以看到，这个选择性真的很差。走索引的代价偏高。 select column_name,        NUM_DISTINCT,        DENSITY,        NUM_NULLS,        LAST_ANALYZED,        HISTOGRAM   from dba_tab_col_statistics  where table_name = 'CPI_PR'    and owner = 'NEWS'    and column_name='FINC';            COLUMN_NAME    NUM_DISTINCT    DENSITY    NUM_NULLS    LAST_ANALYZED    HISTOGRAM 1    FINC    16780    .0000595947556615018    0    2018/12/6 23:30:37    NONE 没有直方图，这里只收集一下索引列的直方图 21:14:43 SQL>    exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'NEWS',tabname=>'CPI_PR',method_opt=>'for all indexed columns size skewonly',estimate_percent=>dbms_stats.auto_sample_size,no_invalidate=>false,cascade=>true,degree => 10); PL/SQL procedure successfully completed. Elapsed: 00:00:07.36 收集之后的列的直方图信息：        COLUMN_NAME    NUM_DISTINCT    DENSITY    NUM_NULLS    LAST_ANALYZED    HISTOGRAM 1    FINC    17138    .000058    0    2019/3/18 21:14:44    HYBRID     因为没有合适的索引，所以这里直方图意义不大。          上面的执行计划中可以看到，这里存在一个谓词推入，导致里面的A部分执行了9000多次，阻止谓词推入后，执行时间100ms，cost值变大，但是这里的执行计划很符合心理预期了，也比较好理解。          不多解释了，看看吧：  Plan Hash Value  : 1209305020 ------------------------------------------------------------------------------------------------------------------- | Id   | Operation                               | Name                  | Rows    | Bytes    | Cost   | Time     | ------------------------------------------------------------------------------------------------------------------- |    0 | SELECT STATEMENT                        |                       |  326462 | 31340352 | 595284 | 00:00:24 | |    1 |   HASH UNIQUE                           |                       |  326462 | 31340352 | 595284 | 00:00:24 | |    2 |    WINDOW SORT                          |                       |  326462 | 31340352 | 595284 | 00:00:24 | |  * 3 |     HASH JOIN                           |                       |  326462 | 31340352 | 580960 | 00:00:23 | |  * 4 |      HASH JOIN                          |                       |    4620 |   258720 |    413 | 00:00:01 | |    5 |       TABLE ACCESS FULL                 | CHK_TMP               |    8168 |   106184 |     29 | 00:00:01 | |  * 6 |       TABLE ACCESS FULL                 | CPI_I                 |   11660 |   501380 |    384 | 00:00:01 | |    7 |      VIEW                               |                       | 1247605 | 49904200 | 580544 | 00:00:23 | |    8 |       WINDOW SORT                       |                       | 1247605 | 78599115 | 580544 | 00:00:23 | |    9 |        COUNT                            |                       |         |          |        |          | | * 10 |         FILTER                          |                       |         |          |        |          | |   11 |          NESTED LOOPS                   |                       | 1247605 | 78599115 | 561667 | 00:00:22 | |   12 |           NESTED LOOPS                  |                       | 1502912 | 78599115 | 561667 | 00:00:22 | |   13 |            NESTED LOOPS                 |                       |    8168 |   253208 |   8199 | 00:00:01 | |   14 |             TABLE ACCESS FULL           | CHK_TMP               |    8168 |   106184 |     29 | 00:00:01 | |   15 |             TABLE ACCESS BY INDEX ROWID | CPI_I                 |       1 |       18 |      1 | 00:00:01 | | * 16 |              INDEX UNIQUE SCAN          | PK_CPI_I              |       1 |          |      0 | 00:00:01 | | * 17 |            INDEX RANGE SCAN             | PK_LG_CPI_PR          |     184 |          |      3 | 00:00:01 | |   18 |           TABLE ACCESS BY INDEX ROWID   | CPI_PR                |     153 |     4896 |     81 | 00:00:01 | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 3 - access("A"."FINC"="B"."FINC") * 3 - filter("B"."SHARD"<>"A"."SUMSREP" OR "A"."SUMSREP" IS NULL AND "B"."SHARD" IS NOT NULL OR "B"."SHARD" IS NULL AND "A"."SUMSREP" IS NOT NULL) * 4 - access("TMP"."KV"="B"."EID") * 6 - filter("B"."UPDATEDATE">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."ISPUB"=U'0') * 10 - filter(ROWNUM>0) * 16 - access("TMP"."KV"="D"."EID") * 17 - access("D"."FINC"="F"."FINC")