Re: Partitioned Table Slower - Where's Tim Gorman when you need him?

Assuming that the table is list-partitioned on SP_CODE, then....

Can you try the query without the ORDER BY?
I'm suspicious of the use of ROWNUM with ORDER BY. Since ROWNUM is assigned before ordering is performed, I'm guessing that the two test cases lead to more rows being examined for the second case than for the first. If we get rid of the conflict between ROWNUM and ORDER BY, I'm figuring you might yield more consistent results?
...I'm glad you included the ORDER BY clause, by the way.... otherwise, I wouldn't have even this slender straw to grasp at.... ;-) 


Brian Lucas wrote:

Hi Tim!  Thanks so much for your response and input.

Here's the ASN_PART_ORG_IX DDL for the partitioned table (ASN_PART):


....edited for brevity...


FROM    ASN_PART G, B1PT P, B3AD A, B3OW B
                        WHERE   G.sp_code= 'SACRA'
                        AND             G.sp_code= P.sp_code
                        AND             P.sp_code= A.sp_code(+)
                        AND             P.sp_code= B.sp_code(+)
                        AND             G.BID1 = P.BID1
                        AND             G.BID2 = P.BID2
                        AND             G.BID3 = P.BID3
                        AND             P.BID1 = A.BID1(+)
                        AND             P.BID2 = A.BID2(+)
                        AND             P.BID3 = A.BID3(+)
                        AND             P.BID1 = B.BID1(+)
                        AND             P.BID2 = B.BID2(+)
                        AND             P.BID3 = B.BID3(+)
AND (P.B1CLASS !='INCOMPLETE' or P.B1CLASS is null or P.B1CLASS = '') 
                        AND             B.B1_PRI_OWN(+) = 'Y'
                        AND             B.RECSTAT(+) = 'A'
                        AND   ROWNUM < 101
                                AND  G.sp_code = 'SACRA'
                                        AND  G.AGENCY_CODE = 'STC'
                                        AND  G.BUREAU_CODE = 'XRT'
                                        AND  G.DIVISION_CODE = 'ABCD'
                                        AND  G.SECTION_CODE = 'NA'
                                        AND  G.GROUP_CODE = 'NA'
                                        AND  G.OFFICE_CODE = 'NA'
                                        AND  G.FNAME LIKE 'First'
                                        AND  G.LNAME LIKE 'Last'
                                AND G.SD_LV1 = 'Y'
                                AND G.SD_LV2 = 'N'
                ORDER BY G.LNAME, G.FNAME, G.B1_DD, G.BID1, G.BID2, G.BID3

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

Other related posts: