Re: index skip scan
Date: Tue, 06 Jan 2009 22:18:59 +0100
Jonathan Lewis schreef:
> "jernigam_at_nospam.kochind.com" <jernigam_at_kochind.com> wrote in message
>> 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.
what's the meaning of resc_io here? I googled but can not find any explanation for it. It might be a cause for Oracle to use the skip scan. It seems to go for plans with low resc_io... while googling I saw more examples of this behavior.
Shakespeare Received on Tue Jan 06 2009 - 15:18:59 CST