Re: Partition Indexes

Haven't we already seen this one once in the last 4 weeks ? Or was it on metalink ?
And did set autotrace statistics show that one plan did 58 physical reads and the other did 32,845 ?

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:


(see http://www.jlcomp.demon.co.uk/seminar.html )


____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html








Billy Verreynne wrote in message ...
HP-UX 11. Oracle 8.1.7 (32bit).

Have a VLT that is partitioned (500 million plus rows). Local indexes only.

Column FOO is indexed - non-unique simple & local (i.e. partitioned) index.


Okay - the problem: A count runs 11+ minutes (single process), i.e.
SELECT count(*) FROM vlt WHERE foo = 'value'

Adding the hint PARALLEL_INDEX( vlt, 10 ), attempting to tell Oracle to
process the local indexes in parallel, works in the sense that the PQ's are
there. However it still take 11+ minutes. So no performance gain at all.

A count on a single partition runs in less than a second, i.e.:
SELECT count(*) FROM vlt PARTITION(some_partition) WHERE foo = 'value';


(a partition contains typically 50+ million rows)


Doing this count per partition, for each partition, unioning all, and then
summing the results.. less than a second! The sum total matches the
count(*) total - i.e. the results are correct.

I'm a bit confused to as why the 'SELECT count(*) FROM vlt' is so much
slower than doing all the partitions via a 'SELECT count(*) FROM vlt
PARTITION'.

11 minutes vs. less than a second? That is a huge performance discrepancy.

Looking at what EXPLAIN PLAN says, did not point anything out of the
ordinary to me. In all the above SELECT count(*)'s, the same partition
index is used.

The only difference :- when scanning the complete table, it uses a fast full
index scan. When explicitly defining a partition to use, it uses a range
scan.

What am I missing here? (besides brains, blondes and beers)

--
Billy