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

From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 8 Jan 2008 10:04:21 -0800
Message-ID: <a9c093440801081004v11443832gff16dc97aca664d1@mail.gmail.com>

It appears that there is a different number of rows being returned from the ASN table. Why is this?

Rows Row Source Operation

-------  ---------------------------------------------------
// non partitioned
  51560         INDEX RANGE SCAN ASN_ORG_IX (cr=323 pr=0 pw=0 time=464207

us)
// partitioned
2540907 INDEX RANGE SCAN ASN_PART_ORG_IX PARTITION: 1 1 (cr=9988 pr=0 pw=0 time=22868284 us)

On 1/8/08, Brian Lucas <moabrivers_at_gmail.com> wrote:
>
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 7 SORT ORDER BY (cr=4963 pr=0 pw=0 time=172431 us)
> 7 HASH UNIQUE (cr=4963 pr=0 pw=0 time=172288 us)
> 7 COUNT STOPKEY (cr=4963 pr=0 pw=0 time=71444 us)
> 7 NESTED LOOPS OUTER (cr=4963 pr=0 pw=0 time=71327 us)
> 7 NESTED LOOPS OUTER (cr=4940 pr=0 pw=0 time=69933 us)
> 7 NESTED LOOPS (cr=4917 pr=0 pw=0 time=68401 us)
> 7 TABLE ACCESS BY INDEX ROWID ASN (cr=4894 pr=0 pw=0
> time=67058 us)
> 51560 INDEX RANGE SCAN ASN_ORG_IX (cr=323 pr=0 pw=0 time=464207
> us)(object id 55269)
> 7 TABLE ACCESS BY INDEX ROWID B1PT (cr=23 pr=0 pw=0 time=956
> us)
> 7 INDEX UNIQUE SCAN B1PT_PK (cr=16 pr=0 pw=0 time=484
> us)(object id 54884)
> 7 TABLE ACCESS BY INDEX ROWID B3AD (cr=23 pr=0 pw=0 time=860
> us)
> 7 INDEX RANGE SCAN B3AD_PK (cr=16 pr=0 pw=0 time=491
> us)(object id 54895)
> 7 TABLE ACCESS BY INDEX ROWID B3OW (cr=23 pr=0 pw=0 time=899
> us)
> 7 INDEX RANGE SCAN B3OW_PK (cr=16 pr=0 pw=0 time=495
> us)(object id 54914)
>
>
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 7 SORT ORDER BY (cr=83809 pr=0 pw=0 time=5091910 us)
> 7 HASH UNIQUE (cr=83809 pr=0 pw=0 time=5091816 us)
> 7 COUNT STOPKEY (cr=83809 pr=0 pw=0 time=93934 us)
> 7 NESTED LOOPS OUTER (cr=83809 pr=0 pw=0 time=93828 us)
> 7 NESTED LOOPS OUTER (cr=83786 pr=0 pw=0 time=92905 us)
> 7 NESTED LOOPS (cr=83763 pr=0 pw=0 time=91989 us)
> 7 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=83740 pr=0
> pw=0 time=91089 us)
> 7 TABLE ACCESS BY LOCAL INDEX ROWID ASN_PART PARTITION: 1 1
> (cr=83740 pr=0 pw=0 time=90954 us)
> 2540907 INDEX RANGE SCAN ASN_PART_ORG_IX PARTITION: 1 1 (cr=9988
> pr=0 pw=0 time=22868284 us)(object id 64296)
> 7 TABLE ACCESS BY INDEX ROWID B1PT (cr=23 pr=0 pw=0 time=815
> us)
> 7 INDEX UNIQUE SCAN B1PT_PK (cr=16 pr=0 pw=0 time=388
> us)(object id 54884)
> 7 TABLE ACCESS BY INDEX ROWID B3AD (cr=23 pr=0 pw=0 time=639
> us)
> 7 INDEX RANGE SCAN B3AD_PK (cr=16 pr=0 pw=0 time=381
> us)(object id 54895)
> 7 TABLE ACCESS BY INDEX ROWID B3OW (cr=23 pr=0 pw=0 time=705
> us)
> 7 INDEX RANGE SCAN B3OW_PK (cr=16 pr=0 pw=0 time=351
> us)(object id 54914)
>
>
>
> .

-- 
Regards,

Greg Rahn
http://structureddata.org

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

Received on Tue Jan 08 2008 - 12:04:21 CST