Re: Partitioned Table Slower - Where's Tim Gorman when you need him?
Date: Tue, 08 Jan 2008 10:48:12 -0700
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...
Quoting Brian Lucas <moabrivers_at_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?