From oracle-l-bounce@freelists.org Thu Jun 23 23:08:07 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j5O487Pu017152 for ; Thu, 23 Jun 2005 23:08:07 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180] (may be forged)) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j5O487Ni017148 for ; Thu, 23 Jun 2005 23:08:07 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6AFFE1C4199; Thu, 23 Jun 2005 22:04:40 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 28592-01; Thu, 23 Jun 2005 22:04:40 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C7B1C1C403E; Thu, 23 Jun 2005 22:04:39 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=dNEl1ZROQF/OZIKlmstnDH306QoC3XZBT4cebekX1DZQj+7RNw2WBgDbUYSwqbKzDai5xQwEkuxSAVoJVww5fEElh7zHKzj+FZA/fAmX2k6xOcoQdxxcZSUKofUmG6GuJoQnG/Pedjk+BeGTw6fZWAtvkksy1e5TWw2ihXSklpQ= ; Message-ID: <20050624030246.26741.qmail@web31205.mail.mud.yahoo.com> Date: Thu, 23 Jun 2005 20:02:46 -0700 (PDT) From: Deepak Sharma Subject: Fwd: Re: Join 2 FACT Tables partitioned on same KEY column To: oracle-l@freelists.org MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="0-1033016982-1119582166=:26590" X-archive-position: 21642 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sharmakdeep_oracle@yahoo.com Precedence: normal Reply-To: sharmakdeep_oracle@yahoo.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.0 required=5.0 tests=AWL,BAYES_00, FORGED_YAHOO_RCVD autolearn=no version=2.63 --0-1033016982-1119582166=:26590 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Content-Id: Content-Disposition: inline Note: forwarded message attached. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --0-1033016982-1119582166=:26590 Content-Type: message/rfc822 Content-Transfer-Encoding: 8bit Received: from [66.41.152.105] by web31213.mail.mud.yahoo.com via HTTP; Thu, 23 Jun 2005 19:48:08 PDT Date: Thu, 23 Jun 2005 19:48:08 -0700 (PDT) From: Deepak Sharma Subject: Re: Join 2 FACT Tables partitioned on same KEY column To: jonathan@jlcomp.demon.co.uk In-Reply-To: <031101c57825$b570d180$6902a8c0@Primary> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Content-Length: 2402 Is the 'PARTITION RANGE ITERATOR' instead of 'PARTITION RANGE SINGLE' in the plan due to the fact that more than 1 partition of T2 are scanned? You are right in saying that with Nested Loop it is possible. Is the 'PARTITION RANGE ITERATOR' on T2 confirming the fact that only certain (not all) partitions of T2 are probed? -- Is this possible with a Hash-join? Say, in your example, D1 and T1 join results in 10 rows, and those pertain to 2 partitions. Wouldn't it make sense to probe only those 2 partiitons of T2 'USING a HASH'? Since I am not seeing that with a Hash join between T1 and T2, but hypothetically speaking, should the plan show something like: HASH NESTED LOOP TABLE ACCESS FULL DRIVER PARTITION RANGE ALL PARTIITON RANGE ITERATOR Thanks, Deepak --- Jonathan Lewis wrote: > > I think this should be possible, but only if the > join between core_fact and param_fact is a > nested loop > > You'll have to cut the example below and > paste it into a fixed font editor to read it, > but in my test case: > > T1 and T2 are partitioned on ID. > > The join from DRIVER to T1 has to do > partition range (ALL) because there is > no partitioning column visible in the join. > > Running with event 10128 set showed that > only the correct two partitions had been > visited the correct number of times from > the second fact table. (I am a little surprised > that the plan says ITERATOR rather than > SINGLE). > > SQL> l > 1 select > 2 /*+ ordered use_nl(t1) use_nl(t2) */ > 3 t1.small_vc, > 4 t2.small_vc > 5 from > 6 driver d1, > 7 t1, > 8 t2 > 9 where > 10 d1.id between 1 and 100 > 11 and t1.n1 = d1.n1 > 12 and t2.id = t1.id > 13* > > ----------------------------------------------------------------------------------------------------- > | Id | Operation | > Name | Rows | Bytes > | Cost | Pstart| Pstop | > ----------------------------------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | > | 100 | 3800 > | 1204 | | | > | 1 | TABLE ACCESS BY LOCAL INDEX ROWID | T2 > | 1 | 14 > | 2 | | | > | 2 | NESTED LOOPS | > | 100 | 3800 > | 1204 | | | > | 3 | NESTED LOOPS | > | 100 | 2400 > | 1004 | | | > |* 4 | TABLE ACCESS FULL | > DRIVER | 100 | 600 > | 4 | | | > | 5 | PARTITION RANGE ALL | > | | > | | 1 | 9 | > | 6 | TABLE ACCESS BY LOCAL INDEX ROWID| T1 > | 1 | 18 > | 10 | 1 | 9 | > |* 7 | INDEX RANGE SCAN | > T1_N1 | 1 | > | 9 | 1 | 9 | > | 8 | PARTITION RANGE ITERATOR | > | | > | | KEY | KEY | > |* 9 | INDEX RANGE SCAN | > T1_I1 | 1 | > | 1 | KEY | KEY | > ----------------------------------------------------------------------------------------------------- > > Predicate Information (identified by operation id): > --------------------------------------------------- > > 4 - filter("D1"."ID">=1 AND "D1"."ID"<=100) > 7 - access("T1"."N1"="D1"."N1") > 9 - access("T2"."ID"="T1"."ID") > > > > Regards > > Jonathan Lewis > > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > The Co-operative Oracle Users' FAQ > > http://www.jlcomp.demon.co.uk/appearances.html > Public Appearances - schedule updated June 22nd 2005 > > > > > > > ----- Original Message ----- > From: "Deepak Sharma" > To: > Sent: Thursday, June 23, 2005 7:32 PM > Subject: Join 2 FACT Tables partitioned on same KEY > column > > > > We have 3 tables in our DW -- DATE_DIM D1, > CORE_FACT > > F1 and PARAM_FACT F2 > > > > The tables F1 and F2 are both partitioned on a > column > > TEST_KEY. Also, as an example, we are sure that a > row > > with TEST_KEY value 100, if exists in Partition > P10 of > > table F1, then it will definitely be in Partition > P10 > > of table F2. > > > > To give an example of what we expect to see : > > > > SELECT f2.column1, f2.column2, f2.column3 > > FROM CORE_FACT f1, PARAM_FACT f2, DATE_DIM d1 > > WHERE f1.TEST_KEY = f2.TEST_KEY > > AND f1.DATE_KEY = d1.DATE_KEY > > AND d1.ACTUAL_DATE between (sysdate-2) and > > (sysdate); > > > > Based on a condition placed on DATE_DIM, rows from > > CORE_FACT are retrieved. Let's say the number of > rows > > returned by that join is 100. Those 100 rows, say, > lie > > in 3 partitions P2, P13 and P25, of table > CORE_FACT. > > > > What we expect to see is that when CORE_FACT joins > to > > PARAM_FACT (as above), only specific partitions > P2, > > P13 and P25 of PARAM_FACT should be accessed. > > > > I have a TAR open with Oracle Support on this > issue, > > and they say it is not possible/supported. Any > > comments? > > > > > > __________________________________________________ > > Do You Yahoo!? > > Tired of spam? Yahoo! Mail has the best spam > protection around > > http://mail.yahoo.com > > -- > > http://www.freelists.org/webpage/oracle-l > > > -- > http://www.freelists.org/webpage/oracle-l > __________________________________ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail --0-1033016982-1119582166=:26590-- -- http://www.freelists.org/webpage/oracle-l