Access path: Bitmap index - rejected ( statistics has no effect to access plan)

Access path: Bitmap index - rejected

背景:
SQL> select count(*) from wc_loymember_d;

  COUNT(*)
----------
   8309483


SQL> select count(*) from wc_loymember_d where X_TRVL_CD = 'Unspecified'

  COUNT(*)
----------
         1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=2)
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT) (Cost=1 Card=1 Bytes=2)
   3    2       BITMAP INDEX (SINGLE VALUE) OF 'WC_LOYMEMBER_D_M65' (I
          NDEX (BITMAP))


SQL> select count(*) from wc_loymember_d where X_TRVL_CD is null;

  COUNT(*)
----------
   8309482

Elapsed: 00:00:00.01
SQL> select count(*) from wc_loymember_d where X_TRVL_CD is not null;

  COUNT(*)
----------
         1



SQL> select distinct D1.c1 as c1,
         D1.c2 as c2,
         D1.c3 as c3,
         D1.c4 as c4
    from
         (select distinct T28836.FREQUENCY as c1,
                   T28836.MEM_TYPE_CD as c2,
                   T28836.X_TRVL_CD as c3,
                   T28836.MEMBER_AVG_BKS_BYYEAR as c4
             from
                  WC_LOYMEMBER_D T28836 /* Dim_WC_LOYMEMBER_D_Air3 */
             where  ( T28836.X_TRVL_CD = 'Unspecified' and (T28836.MEM_TYPE_CD
in ('Unspecified', '个人', '公司', '合作伙伴')) )
        ) D1
   /

Elapsed: 00:00:30.25

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=43369 Card=1 Bytes
          =15)

   1    0   HASH (UNIQUE) (Cost=43369 Card=1 Bytes=15)
   2    1     TABLE ACCESS (FULL) OF 'WC_LOYMEMBER_D' (TABLE) (Cost=43
          368 Card=1 Bytes=15)





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


如果强制走索引，将会非常迅速，只需要0.03秒的时间

SQL> select distinct D1.c1 as c1,
         D1.c2 as c2,
         D1.c3 as c3,
         D1.c4 as c4
    from
         (select/*+index(T28836,WC_LOYMEMBER_D_M65)*/ distinct T28836.FREQUENCY
as c1,
                   T28836.MEM_TYPE_CD as c2,
                   T28836.X_TRVL_CD as c3,
                   T28836.MEMBER_AVG_BKS_BYYEAR as c4
             from
                  WC_LOYMEMBER_D T28836 /* Dim_WC_LOYMEMBER_D_Air3 */
             where  ( T28836.X_TRVL_CD = 'Unspecified' and (T28836.MEM_TYPE_CD
in ('Unspecified', '个人', '公司', '合作伙伴')) )
        ) D1
   /



Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=897930 Card=1 Byte
          s=15)

   1    0   HASH (UNIQUE) (Cost=897930 Card=1 Bytes=15)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'WC_LOYMEMBER_D' (TABLE
          ) (Cost=897929 Card=1 Bytes=15)

   3    2       BITMAP CONVERSION (TO ROWIDS)
   4    3         BITMAP INDEX (SINGLE VALUE) OF 'WC_LOYMEMBER_D_M65'
          (INDEX (BITMAP))





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

收集表的统计信息，结果无济于事，还是full table scan..

SQL> exec dbms_stats.gather_table_stats(ownname => 'olap',tabname => 'WC_LOYMEMBER_D',method_opt => 'FOR ALL COLUMNS size 1',cascade => TRUE,no_invalidate => FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:16:04.04
SQL> select distinct D1.c1 as c1,
         D1.c2 as c2,
         D1.c3 as c3,
         D1.c4 as c4
    from
         (select distinct T28836.FREQUENCY as c1,
                   T28836.MEM_TYPE_CD as c2,
                   T28836.X_TRVL_CD as c3,
                   T28836.MEMBER_AVG_BKS_BYYEAR as c4
             from
                  WC_LOYMEMBER_D T28836 /* Dim_WC_LOYMEMBER_D_Air3 */
             where  ( T28836.X_TRVL_CD = 'Unspecified' and (T28836.MEM_TYPE_CD
in ('Unspecified', '个人', '公司', '合作伙伴')) )
        ) D1
   /

Elapsed: 00:00:37.14

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=43436 Card=1 Bytes
          =15)

   1    0   HASH (UNIQUE) (Cost=43436 Card=1 Bytes=15)
   2    1     TABLE ACCESS (FULL) OF 'WC_LOYMEMBER_D' (TABLE) (Cost=43
          435 Card=1 Bytes=15)





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


收集柱状图,结果没有变化.


10053 trace 信息:

Access Path: TableScan
    Cost:  43367.68  Resp: 43367.68  Degree: 0
      Cost_io: 42806.00  Cost_cpu: 7202240368
      Resp_io: 42806.00  Resp_cpu: 7202240368
  ****** trying bitmap/domain indexes ******
  Access Path: index (AllEqRange)
    Index: WC_LOYMEMBER_D_M65
    resc_io: 1.00  resc_cpu: 16036
    ix_sel: 1.2055e-07  ix_sel_with_filters: 1.2055e-07
    Cost: 1.00  Resp: 1.00  Degree: 0
  Using prorated density: 6.0275e-08 of col #3 as selectivity of out-of-range value pred
  Using prorated density: 6.0275e-08 of col #3 as selectivity of out-of-range value pred
  Access Path: index (AllEqRange)
    Index: WC_LOYMEMBER_D_M1
    resc_io: 1.00  resc_cpu: 16036
    ix_sel: 6.0275e-08  ix_sel_with_filters: 6.0275e-08
    Cost: 1.00  Resp: 1.00  Degree: 0
  Access Path: index (AllEqRange)
    Index: WC_LOYMEMBER_D_M1
    resc_io: 72.00  resc_cpu: 2810025
    ix_sel: 0.9921  ix_sel_with_filters: 0.9921
    Cost: 72.22  Resp: 72.22  Degree: 0
  Using density: 0.0039487 of col #3 as selectivity of unpopular value pred
  Using density: 0.0039487 of col #3 as selectivity of unpopular value pred
  Access Path: index (AllEqRange)
    Index: WC_LOYMEMBER_D_M1
    resc_io: 1.00  resc_cpu: 22836
    ix_sel: 0.0039487  ix_sel_with_filters: 0.0039487
    Cost: 1.00  Resp: 1.00  Degree: 0
  Access Path: index (AllEqRange)
    Index: WC_LOYMEMBER_D_M1
resc_io: 1.00  resc_cpu: 29436
    ix_sel: 0.0077328  ix_sel_with_filters: 0.0077328
    Cost: 1.00  Resp: 1.00  Degree: 0
  Access path: Bitmap index - rejected
    Cost: 897929.36 Cost_io: 895185.00 Cost_cpu: 35190154366 Sel: 1
    Not believed to be index-only
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: TableScan
         Cost: 43367.68  Degree: 1  Resp: 43367.68  Card: 1.00  Bytes: 0

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13165828/viewspace-610773/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13165828/viewspace-610773/