From: "Richard Foote" Newsgroups: comp.databases.oracle.server References: Subject: Re: Partition Indexes Lines: 58 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Mon, 9 Sep 2002 23:20:01 +1000 NNTP-Posting-Host: 144.134.117.134 X-Trace: newsfeeds.bigpond.com 1031577064 144.134.117.134 (Mon, 09 Sep 2002 23:11:04 EST) NNTP-Posting-Date: Mon, 09 Sep 2002 23:11:04 EST Organization: Telstra BigPond Internet Services (http://www.bigpond.com) 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" wrote in message news:alht52$463$1@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