Re: Join 2 FACT Tables partitioned on same KEY column

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 23 Jun 2005 19:59:40 +0100
Message-ID: <031101c57825$b570d180$6902a8c0@Primary>

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*

| 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" <sharmakdeep_oracle_at_yahoo.com> To: <oracle-l_at_freelists.org> 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

Received on Thu Jun 23 2005 - 15:04:54 CDT