Re: index skip scan

From: Robert Klemme <shortcutter_at_googlemail.com>
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 end

Received on Tue Jan 06 2009 - 09:26:02 CST