Partition Indexes

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