Re: Can Oracle do a query plan like this?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:

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

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 Sun Feb 06 2000 - 15:38:21 CST