Re: Can Oracle do a query plan like this?

So it sounds like you're saying "no", Oracle is unable to do what I want?

Just to make it clear, a full index scan would not be useful. I have a situation like:

id,user

1,frank
1,bob
1,a million other people
2,frank
2,a million other people
3,frank
3,a million other people

And I need to get all the rows that have "frank" in the second column of the index. It would be much faster for Oracle to do this by iterating through all the values of the first column doing a range scan for frank. An full index scan may be faster than a full table scan but it would still be unusable for the OLTP application.

I need to partition on the first value too. Surely Oracle is capable of using such an index on a partitioned table? Otherwise partitioned tables are useless, you would basically always need to have lots of global indexes.

markp7832_at_my-deja.com writes:

> 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.

--
greg Received on Sat Feb 05 2000 - 20:54:47 CST