It's rejected because tt's more expensive (1038) than the index1 range scan (637).
Roughly the calculation is this: Selectivity 6 * 10e-4 (distinct keys 1,500) Rows in table 7.5M It looks like your query is: key = constant. Rows for a single key: 7,500,000 / 1,500 = 5,000 Bitmap assumption (approx) 80% of the rows are tightly packed, 20% are widely scattered. (See Oracle Wait Interfact (K Gopalakrishnan et. al. and Cost Based Oracle Fundamentals (me)). 20% of 5,000 = 1,000, so you will visit just over 1,000 blocks in the table collecting these rows. For bitmap indexes, the clustering_factor is NOT about data scattering patterns. Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html----- Original Message -----
Access path: Bitmap index - rejectedFrom: "Raj Mareddi" <yoursraju007@xxxxxxxxx> Date: Tue, 18 Dec 2007 13:49:28 -0500
Index: INDEX1 Col#: 1 2 LVLS: 2 #LB: 11825 #DK: 7478026 LB/K: 1.00 DB/K: 1.00 CLUF: 954385.00 Index: INDEX2 Col#: 1 LVLS: 1 #LB: 140 #DK: 1572 LB/K: 1.00 DB/K: 1.00 CLUF: 1655.00 SINGLE TABLE ACCESS PATH Column (#1): NAME (VARCHAR2) AvgLen: 6.00 NDV: 1557 Nulls: 0 Density: 6.4226e-04 Table: TAB1 Alias: TAB1 Card: Original: 7388103 Rounded: 4745 Computed: 4745.09 Non Adjusted: 4745.09 Access Path: TableScan Cost: 27120.03 Resp: 27120.03 Degree: 0 Cost_io: 24677.00 Cost_cpu: 3763177144 Resp_io: 24677.00 Resp_cpu: 3763177144 Access Path: index (RangeScan) Index: INDEX1 resc_io: 623.00 resc_cpu: 21679564 ix_sel: 6.4226e-04 ix_sel_with_filters: 6.4226e-04 Cost: 637.07 Resp: 637.07 Degree: 1 ****** trying bitmap/domain indexes ****** Access Path: index (AllEqRange) Index: INDEX2 resc_io: 1.00 resc_cpu: 10993 ix_sel: 6.4226e-04 ix_sel_with_filters: 6.4226e-04 Cost: 1.01 Resp: 1.01 Degree: 0 Access path: Bitmap index - rejected Cost: 1038.15 Cost_io: 1021.87 Cost_cpu: 25073088 Sel: 6.4226e-04 Not believed to be index-only Best:: AccessPath: IndexRange Index: INDEX1 Cost: 637.07 Degree: 1 Resp: 637.07 Card: 4745.09 Bytes: 0 Why is my bitmap index path rejected ? -- //www.freelists.org/webpage/oracle-l