Re: Partition Indexes

Jonathan Lewis wrote:

> Haven't we already seen this one once in the
> last 4 weeks ? Or was it on metalink ?

First time posting on the subject from me, Jon.

I had a look at MetaLink and only saw the following on my bug notification list:
NO PRUNING ON CONCAT KEY PARTITIONING WITH INLIST AND RANGE ON KEYS Not really the same problem, though it seems to point to a problem with how local partitioned indexes are used.

> And did set autotrace statistics show that
> one plan did 58 physical reads and the other
> did 32,845 ?

Nope. I did a search on Google and could not find any related articles in comp.database.oracle.server (using keywords such as performance, partition, index and so on).

Plainly put Jon, this is my problem:

SQL1:
select count(*) from partition_table where partition_index_colum = 'foo'

vs.

SQL2:
select sum(x) from
(
select count(*) "X" from partition_table partition(p1) where partition_index_colum = 'foo'
union all
select count(*) from partition_table partition(p2) where partition_index_colum = 'foo'
.. etc..
)

SQL1 = 11+ minutes execution time

SQL2 = below 1 second execution time

Both SQL1 and SQL2's utlxpls say they are using the same local partition index. Of course, SQL1 does a partition interation.

Does not make a lot of sense to me.

--
Billy