index skip scan

From: jernigam_at_nospam.kochind.com <jernigam_at_kochind.com>
Date: Tue, 6 Jan 2009 07:13:09 -0800 (PST)
Message-ID: <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. Received on Tue Jan 06 2009 - 09:13:09 CST