ORACLE:执行计划中BITMAP AND导致sql慢

原sql：

SQL> explain plan for SELECT *
  2    FROM crmdw.irsl_renew_user a, crmdw.irsl_renew_tmp_user b, crmdw.on_cor_table t
  3   WHERE     a.irsl_date = '2015-09-25'
  4         AND b.irsl_date = '2015-09-25'
  5         AND TO_CHAR (t.end_date, 'yyyy-mm-dd') = '9999-12-31'
  6         AND b.IS_BW = 1
  7         AND b.seach_sign_comp IN (1, 2, 3)
  8         AND a.providerid = b.providerid
  9         AND t.providerid = a.providerid;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2509217529
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                             |     1 |  2145 | 88436   (4)| 00:17:42 |
|   1 |  TEMP TABLE TRANSFORMATION            |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                      | SYS_TEMP_0FD9D66A2_D73DA0B4 |       |       |            |          |
|*  3 |    TABLE ACCESS FULL                  | ON_COR_TABLE                |  6242 |    10M| 34723   (1)| 00:06:57 |
|   4 |   LOAD AS SELECT                      | SYS_TEMP_0FD9D66A2_D73DA0B4 |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID        | IRSL_RENEW_TMP_USER         | 10803 |  2342K|  1801   (1)| 00:00:22 |
|*  6 |     INDEX RANGE SCAN                  | IND_RENEW_IRSLDATE_IB_SSC   | 11359 |       |   702   (1)| 00:00:09 |
|*  7 |   HASH JOIN                           |                             |     1 |  2145 | 51912   (7)| 00:10:23 |
|*  8 |    HASH JOIN                          |                             |    15 | 28620 | 51839   (7)| 00:10:23 |
|   9 |     TABLE ACCESS BY INDEX ROWID       | IRSL_RENEW_USER             |   292 | 48307 | 51556   (7)| 00:10:19 |
|  10 |      BITMAP CONVERSION TO ROWIDS      |                             |       |       |            |          |
|  11 |       BITMAP AND                      |                             |       |       |            |          |
|  12 |        BITMAP CONVERSION FROM ROWIDS  |                             |       |       |            |          |
|* 13 |         INDEX RANGE SCAN              | IDX_RENEW_IRSLDATE          |       |       |   707   (1)| 00:00:09 |
|  14 |        BITMAP MERGE                   |                             |       |       |            |          |
|  15 |         BITMAP KEY ITERATION          |                             |       |       |            |          |
|  16 |          TABLE ACCESS FULL            | SYS_TEMP_0FD9D66A2_D73DA0B4 |     1 |    13 |     2   (0)| 00:00:01 |
|  17 |          BITMAP CONVERSION FROM ROWIDS|                             |       |       |            |          |
|* 18 |           INDEX RANGE SCAN            | IDX_RENEW_PROVIDERID        |       |       |     3   (0)| 00:00:01 |
|  19 |        BITMAP MERGE                   |                             |       |       |            |          |
|  20 |         BITMAP KEY ITERATION          |                             |       |       |            |          |
|  21 |          TABLE ACCESS FULL            | SYS_TEMP_0FD9D66A1_D73DA0B4 |     1 |    13 |     2   (0)| 00:00:01 |
|  22 |          BITMAP CONVERSION FROM ROWIDS|                             |       |       |            |          |
|* 23 |           INDEX RANGE SCAN            | IDX_RENEW_PROVIDERID        |       |       |     3   (0)| 00:00:01 |
|  24 |     TABLE ACCESS FULL                 | SYS_TEMP_0FD9D66A1_D73DA0B4 |  6242 |    10M|   282   (1)| 00:00:04 |
|  25 |    TABLE ACCESS FULL                  | SYS_TEMP_0FD9D66A2_D73DA0B4 | 10803 |  2500K|    73   (2)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(TO_CHAR(INTERNAL_FUNCTION("T"."END_DATE"),'yyyy-mm-dd')='9999-12-31')
   6 - access("B"."IRSL_DATE"='2015-09-25')
       filter(TO_NUMBER("B"."IS_BW")=1 AND (TO_NUMBER("B"."SEACH_SIGN_COMP")=2 OR
              TO_NUMBER("B"."SEACH_SIGN_COMP")=3 OR TO_NUMBER("B"."SEACH_SIGN_COMP")=1))
   7 - access("A"."PROVIDERID"="C0")
   8 - access("C0"="A"."PROVIDERID")
  13 - access("A"."IRSL_DATE"='2015-09-25')
  18 - access("A"."PROVIDERID"="C0")
  23 - access("A"."PROVIDERID"="C0")
Note
-----
   - star transformation used for this statement

需要5、6分钟跑完

改写后：

explain plan for select *
  from (select /*+ no_merge */ b.*
          from crmdw.irsl_renew_user a, crmdw.irsl_renew_tmp_user b
         where a.irsl_date = '2015-09-25'
           and a.providerid = b.providerid
           and b.irsl_date = '2015-09-25'
           and b.IS_BW = 1
           and b.seach_sign_comp in (1, 2, 3)) ab,
       crmdw.on_cor_table t
 where t.providerid = ab.providerid
   and to_char(t.end_date, 'yyyy-mm-dd') = '9999-12-31';       
   
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |       |       |       | 68569 (100)|          |
|*  1 |  HASH JOIN                     |                           |  6242 |   171M|    10M| 68569   (1)| 00:13:43 |
|*  2 |   TABLE ACCESS FULL            | ON_COR_TABLE              |  6242 |    10M|       | 34723   (1)| 00:06:57 |
|   3 |   VIEW                         |                           | 19403 |   502M|       |  8425   (1)| 00:01:42 |
|*  4 |    HASH JOIN                   |                           | 19403 |  4566K|  2472K|  8425   (1)| 00:01:42 |
|   5 |     TABLE ACCESS BY INDEX ROWID| IRSL_RENEW_TMP_USER       | 10803 |  2342K|       |  1801   (1)| 00:00:22 |
|*  6 |      INDEX RANGE SCAN          | IND_RENEW_IRSLDATE_IB_SSC | 11359 |       |       |   702   (1)| 00:00:09 |
|   7 |     TABLE ACCESS BY INDEX ROWID| IRSL_RENEW_USER           |   220K|  4088K|       |  6177   (1)| 00:01:15 |
|*  8 |      INDEX RANGE SCAN          | IDX_RENEW_IRSLDATE        |   228K|       |       |   707   (1)| 00:00:09 |
--------------------------------------------------------------------------------------------------------------------   
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T"."PROVIDERID"="AB"."PROVIDERID")
   2 - filter(TO_CHAR(INTERNAL_FUNCTION("T"."END_DATE"),'yyyy-mm-dd')='9999-12-31')
   4 - access("A"."PROVIDERID"="B"."PROVIDERID")
   6 - access("B"."IRSL_DATE"='2015-09-25')
       filter((TO_NUMBER("B"."IS_BW")=1 AND (TO_NUMBER("B"."SEACH_SIGN_COMP")=2 OR
              TO_NUMBER("B"."SEACH_SIGN_COMP")=3 OR TO_NUMBER("B"."SEACH_SIGN_COMP")=1)))
   8 - access("A"."IRSL_DATE"='2015-09-25')

30秒