From: Billy Verreynne Newsgroups: comp.databases.oracle.server Subject: Partition Indexes Date: Mon, 09 Sep 2002 12:28:19 +0200 Organization: Verreynne Software Labs Lines: 40 Message-ID: NNTP-Posting-Host: 198.54.202.209 Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7Bit X-Trace: ctb-nnrp2.saix.net 1031567330 4291 198.54.202.209 (9 Sep 2002 10:28:50 GMT) X-Complaints-To: abuse@saix.net NNTP-Posting-Date: 9 Sep 2002 10:28:50 GMT User-Agent: KNode/0.7.1 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