Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 29985 invoked from network); 8 Jan 2008 15:59:02 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.intergenia.de with SMTP; 8 Jan 2008 15:58:57 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7F9A77E168E; Tue, 8 Jan 2008 16:58:47 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27066-04; Tue, 8 Jan 2008 16:58:47 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E9F0E7E15ED; Tue, 8 Jan 2008 16:58:46 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 08 Jan 2008 16:10:52 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1F5797E0563 for ; Tue, 8 Jan 2008 16:10:52 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20104-04 for ; Tue, 8 Jan 2008 16:10:52 -0500 (EST) Received: from taro.utanet.at (taro.utanet.at [213.90.36.45]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 86E7E7E03B7 for ; Tue, 8 Jan 2008 16:10:50 -0500 (EST) Received: from andrea.utanet.at ([213.90.36.55]) by taro.utanet.at with esmtp (Exim 4.69) (envelope-from ) id 1JCLii-0008Vu-KM; Tue, 08 Jan 2008 22:10:48 +0100 Received: from dsl-153-244.utaonline.at ([62.218.153.244] helo=ADLA) by andrea.utanet.at with smtp (Exim 4.50) id 1JCLii-0007qN-9F; Tue, 08 Jan 2008 22:10:48 +0100 Message-ID: <06d701c8523b$3869b920$3e02a8c0@ADLA> From: "jaromir nemec" To: , , Cc: References: Subject: Re: Partitioned Table Slower - Where's Tim Gorman when you need him? Date: Tue, 8 Jan 2008 22:12:44 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_06D4_01C85243.98349B10" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 X-archive-position: 4363 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jaromir@db-nemec.com Precedence: normal Reply-to: jaromir@db-nemec.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_NextPart_000_06D4_01C85243.98349B10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable HI, > In trying to see if > partitioning them off to their own partition with local prefixed = indexes > matching the indexes of the original heap table any chance that due to "prefixing" of the index on the partitioned table = the range scan is forced to scan the whole partition? i.e. index on the partitioned table is defined (col_a,col_b) and on = the non-partitioned table only (col_b) The predicate col_a =3D 'X' returns 2.5M rows with is approximately 30% = of your 8M rows, i.e. the target data. Verify that the index ASN_ORG_IX is defined identically on both tables = except for the LOCAL keyword on the partitioned table.=20 regards, Jaromir D.B. Nemec ----- Original Message -----=20 From: Greg Rahn=20 To: moabrivers@gmail.com=20 Cc: oracle-l@freelists.org=20 Sent: Tuesday, January 08, 2008 7:04 PM Subject: Re: Partitioned Table Slower - Where's Tim Gorman when you = need him? It appears that there is a different number of rows being returned = from the ASN table. Why is this? Rows Row Source Operation ------- ---------------------------------------------------=20 // non partitioned 51560 INDEX RANGE SCAN ASN_ORG_IX (cr=3D323 pr=3D0 pw=3D0 = time=3D464207 us) // partitioned 2540907 INDEX RANGE SCAN ASN_PART_ORG_IX PARTITION: 1 1 = (cr=3D9988 pr=3D0 pw=3D0 time=3D22868284 us)=20 ------=_NextPart_000_06D4_01C85243.98349B10 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
HI,
 
> In trying to see if
> = partitioning them=20 off to their own partition with local prefixed indexes
> matching = the=20 indexes of the original heap table
 
any chance that due to "prefixing" of = the index on=20 the partitioned table the range scan is forced to scan the whole=20 partition?
i.e.  index on the partitioned = table is=20 defined (col_a,col_b)  and on the non-partitioned table only=20 (col_b)
The predicate col_a =3D 'X' returns = 2.5M rows with is=20 approximately=20 30% of your 8M rows, i.e. the target data.
Verify that the index ASN_ORG_IX is = defined=20 identically on both tables except for the LOCAL keyword on the = partitioned=20 table.
 
 
regards,
 
Jaromir D.B. Nemec
----- Original Message -----
From:=20 Greg=20 Rahn
Sent: Tuesday, January 08, 2008 = 7:04=20 PM
Subject: Re: Partitioned Table = Slower -=20 Where's Tim Gorman when you need him?

It = appears=20 that there is a different number of rows being returned from the ASN=20 table.  Why is this?Rows     Row=20 Source Operation
------- =20 --------------------------------------------------- // non partitioned =20 51560         INDEX RANGE SCAN = ASN_ORG_IX (cr=3D323 pr=3D0 pw=3D0 time=3D464207 us)// partitioned2540907        &nb= sp;=20 INDEX RANGE SCAN ASN_PART_ORG_IX PARTITION: 1 1 (cr=3D9988 pr=3D0 = pw=3D0=20 time=3D22868284 us)
------=_NextPart_000_06D4_01C85243.98349B10-- -- http://www.freelists.org/webpage/oracle-l