Re: Can Oracle do a query plan like this?

A concrete example is much more helpful than a vague description to get the ball rolling.

If you want all the user='frank' rows, and frank is one in a million, you clearly need an index on user.

On a non-partitioned table this works perfectly well according to your stats.

On a table partitioned by id, you simply make the index a local index. Then for each partition, Oracle visits its one in a million frank entry.

Note though, that if you have (say) 1,000 partitions and you fetch one frank from each partition, the overhead of partitioning will be much larger than the basic cost of getting franks - especially if frank only appears in a relatively small number of the partitions.

If you have only a dozen or so partitions then the cost of the frank search will be the more significant fraction of the total cost, and the set-up will be more likely to be viable for a high-speed OLTP
system.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Greg Stark wrote in message
<87og9v3us5.fsf_at_HSE-Montreal-ppp33976.qc.sympatico.ca>...
>
>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 Sun Feb 06 2000 - 15:06:18 CST