Re: oracle-l Digest V4 #358

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 21 Dec 2007 13:00:15 -0000


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 - rejected
From: "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


Other related posts:

  • » Re: oracle-l Digest V4 #358