Join 2 FACT Tables partitioned on same KEY column

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Thu, 23 Jun 2005 11:32:36 -0700 (PDT)
Message-ID: <20050623183237.39598.qmail@web31211.mail.mud.yahoo.com>

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

Received on Thu Jun 23 2005 - 14:37:56 CDT