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
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博客 ” ，链接：https://blog.itpub.net/13165828/viewspace-610773/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
创建索引影响插入的速度
请登录后发表评论
登录
全部评论
