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