Re: index skip scan
Date: Tue, 06 Jan 2009 16:26:02 +0100
Message-ID: <6shbgbF5taorU1_at_mid.individual.net>
On 06.01.2009 16:13, jernigam_at_nospam.kochind.com wrote:
> 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'
What type is column "effective_date"? I assume it's DATE. If so, it seems to me that the query is suboptimal for once because it does not use the proper type for the literal (TO_DATE would have been better IMHO), and secondly because it does not use bind variables.
For a start I'd execute the query with TO_DATE where your VARCHAR literals are and see what cost and plan you get.
> 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.
How do the criteria look in the bad plan?
> 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.
You could use an SQL Profile.
> 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.
You can find out looking at a 10053 trace.
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without endReceived on Tue Jan 06 2009 - 09:26:02 CST