Re: Can Oracle do a query plan like this?

In article <87wvoj4kmd.fsf_at_HSE-Montreal-ppp33976.qc.sympatico.ca>,   Greg Stark <greg-spare-1_at_mit.edu> wrote:
>
> If I have an index on (a,b) and a has relatively low cardinality is
there any
> way to convince Oracle to use this index when I do a query "WHERE
b=:p"? That
> is to repeatedly try the various values of a looking for this value
of b.
>
> I don't want to index just b because I want this design to work when
we
> partition the table on a. In the case of a partitioned table with a
local
> index on a,b would Oracle then be willing to use the index?
>
> Assume each value of a has more than enough records to make it
worthwhile to
> do an index scan over a full table scan.
>
> --

As a general rule Oracle can not use a multi-valued index to solve a query if the first (or leading) columns are missing from the where clause. However, if your query selected A and B and no other values from the table then Oracle might decide to use the index to retrieve the values. If it does not then you could probably force Oracle's CBO to do a fast full index scan using the hint. If you do not retrieve column A then I am not sure the hint will work or not; you will have to try it.

[I do not remember the hint off the top of my head but I am pretty sure it starts with FFS, maybe FFS_INDEX?] You should be able to find the hints in the Application Developers Guide or the Performance and Tuning Manual.
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Feb 05 2000 - 14:43:37 CST