Re: index skip scan

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 6 Jan 2009 18:54:38 -0000
Message-ID: <Q8SdncnbHdp1Nf7UnZ2dnUVZ8jidnZ2d_at_bt.com>



"jernigam_at_nospam.kochind.com" <jernigam_at_kochind.com> wrote in message news:557f004c-1e91-40ea-99df-06057ea90093_at_i24g2000prf.googlegroups.com...
> First of all I am using 10.2.0.3 running on solaris 64-bit.
>
> I have the following query.
>
> select * from inventory_trans_qty itq
> where itq.effective_date > '01-Dec-2008'
> and itq.effective_date <= '05-Dec-2008'
>
> I have the following two indexes on that table.
>
> inv_trans_qty_ind2 (effective_date)
> inv_trans_qty_ind3 (addr_id, effective_date)
>
> Instead of doing a range scan on inv_trans_qty_ind2, the query does a
> skip scan on inv_trans_qty_ind3.
>
> If I hint the query to use the ind2 index, the logical reads drops and
> it runs faster. Unfortunately I can't hint the real queries, because
> they are in a packaged application.
>
> The skip scan shows a lower cost. How is the cost of a skip scan
> calculated? I would think it would have a higher cost given the two
> fields.
>
>
>
>

There are various "anomalies" in the costing of skip scans.

In principal, you should get a component of cost which indicates the number of times that Oracle thinks it will have to probe on the first column (and this is likely to be vaguely similar to the number of distinct values of the first column), plus the cost of the table visit (which shouldn't be very different from the cost of the table vist from the range scan.

Your results suggest that Oracle thinks you have only a very few distinct values for the addr_id, and an index on effective_date that is less well-packed than the index on the (addr_id, effective_date) pair.

The figures you posted for Charles Hooper, however, do not seem to indicate this.

Could you post the entire "Single Table Access" section of the 10053 trace - I am curious to see how the optimizer managed to get the results it did.

If you are very confident that you don't have any code where a skip scan will be a really good idea, then you could (after the usual consultation with Oracle support) set the hidden parameter _optimizer_skip_scan_enabled to false.

-- 
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
Received on Tue Jan 06 2009 - 12:54:38 CST

Original text of this message