Re: Partition Indexes

Hi Billy,

Are you sure that the first query isn't run in parallel as well ?

There are some bugs regarding reading tables in parallel with local indexes that have been discussed recently here (or in comp.databases.oracle.misc).

Richard

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:alht52$463$1_at_ctb-nnrp2.saix.net...
> 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
Received on Mon Sep 09 2002 - 08:20:01 CDT