Re: oracle-l Digest V4 #358

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 21 Dec 2007 13:00:15 -0000
Message-ID: <00e301c843d1$6ecb1d10$0200a8c0@Primary>

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_at_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 ?
--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 21 2007 - 07:00:15 CST