Re: Can Oracle do a query plan like this?

So you have 300+ values of ID ?

Even on a non-partitioned table its
border-line whether Oracle would
need an index on ID for queries
of the type:

    id = 'x'
Depends very much on size of row,
how well clustered the 'x' rows
are, size of block, size of read-scan etc.

Certainly on a table with 300 partitions with partition = one value of ID you do not need an index on id, Oracle will
find the right partition for

    id = 'x'

If the index is (name) only, then
on a local index Oracle will find the
correct index partition on

    id = 'x' and name = 'adsf'

On just:

    name = 'adsf'
Oracle will check use every single
index partition. If you expect to find 'adsf' in just a few partitions, then
the overhead of checking the wrong
index partition partitions will be high. (say 4 logical I/Os per hit on good partitions, 2 logical I/Os per miss on bad partitions - if 'asdf' exists in 10 out of 300 you get

    40 I/Os for good partitions
    580 for bad partitions
)

One though: you don't need to have one id per partition to help Oracle - it would be perfectly reasonable to have, say, 100
partitions with 3-4 ids per partitions, and the strategy still works.

--

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

Greg Stark wrote in message
<87emaq1073.fsf_at_HSE-Montreal-ppp33976.qc.sympatico.ca>...

>Well the thing was that i also needed an index on a,userid and wanted to
avoid
>needing both indexes. Also I wanted to prepare for partitioning when we
move
>to it.
>
>> 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.
>
>So to get this to work it would be a local index on just (userid)?
>No prefix of a? And Oracle just figures out how to use the index?
>
>If I partition on a with each value of a having a distinct partition then I
>would have no need for an index on (a,userid)? Oracle would be doing
>essentially the same optimization by doing the partition logic?
>
>> 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.
>
>Well we will have >300 partitions if we do this, so it would be a factor,
>though it's not a key query.
>
>> 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.
>
>--
>greg
Received on Mon Feb 07 2000 - 01:02:59 CST