Message-ID: <20080108104812.rxtp0hsk8owkgkok@webmail.evdbt.com>
Date: Tue, 08 Jan 2008 10:48:12 -0700
From: Tim Gorman <tim@evdbt.com>
To: moabrivers@gmail.com
Cc: oracle-l@freelists.org
Subject: Re: Partitioned Table Slower - Where's Tim Gorman when you need
 him?
References: <b604d45b0801080731y2a8f8a15g969c59a31bc62b33@mail.gmail.com>
In-Reply-To: <b604d45b0801080731y2a8f8a15g969c59a31bc62b33@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1;	DelSp="Yes";	format="flowed"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) H3 (4.1.4)
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: tim@evdbt.com

Brian,

At first blush, looking at the two TKPROF excerpts, I would speculate  
that the index ASN_PART_ORG_IX has somehow been defined as a global  
index, not as a local index, although you state that it is both local  
and prefixed.  At any rate, from the TKPROF output, we can see that  
index range scan has pruned to the single index partition, but could  
you post the DDL for the indexes, both for ASN_ORG_IX as well as  
ASN_PART_ORG_IX?

In general, knowing definitions is useless without also knowing  
utilization, so could you post the SQL used in both cases, including  
any hints used to generate these test cases?  Ideally, we should see  
all sections of the TKPROF output, but I'm guessing you edited for  
brevity and clarity.  If they are huge, then just the FROM and WHERE  
clauses might be sufficient, I guess...

Thanks!

-Tim


Quoting Brian Lucas <moabrivers@gmail.com>:

> All, I'm experimenting with partitioning in 10g (10.2.0.3 64-bit) over
> Oracle Enterprise Linux 5 64-bit.  I am using ASM over FC tied in to a
> NetApp FAS 3020 HA cluster.  I have a table with 8 million records of which
> one particular group of records takes about 30%.  In trying to see if
> partitioning them off to their own partition with local prefixed indexes
> matching the indexes of the original heap table, I'm finding that the
> original heap table query with regular btree indexes is consistently faster
> and shows a lower total cost in the explain plan.  My query specifically
> includes a predicate that causes the optimizer to select the partitioned
> indexes and partitioned section of the table.  The cost using the heap table
> is 6 and the cost of the partitioned table is over 8000.  All statistics
> have been gathered and updated.  This is a multijoin query of 4 tables and
> only the one large table have I chose for this partition test.  Some tkprof
> analysis shows that using the partition results in it reading over 2 million
> records whereas the same index on the heap table only reads about 51000.
> The query portion of tkprof shows the large discrepancy.  Can someone help
> my understanding of why the partition chooses to read all of the records in
> the partition whereas the heap table doesn't?  Does it have to do with the
> selectivity of the index at the local partition level and that somehow the
> btree's are built differently within a partition than they are for heaps?

--
http://www.freelists.org/webpage/oracle-l