OraFAQ Forum: Data Integration » 1 dimension and 2 fact table -cartesian join

Home » Developer & Programmer » Data Integration » 1 dimension and 2 fact table -cartesian join (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

1 dimension and 2 fact table -cartesian join [message #570989]

Mon, 19 November 2012 10:57

swas_recall
Messages: 12
Registered: May 2012
Location: Bangalore

Junior Member

Hi

I have to do a analysis where i need to join 2 fact tables with a conformed dimension as below

D1
-----
Datekey
Datevalue

F1
----
Week_key
Weekdate
Issue_key
Cus_key

F2
______
Week_key
Weekdate
Product_key
Seller_key

I need to join D1 with F1 and F2 such that i can display the below columns.

Product_key,Seller_key,Issue_key,Weekdate.

Relationships are as below

D1:F1-1:N
D1:F2-1:N

now if i use a normal join clause i get cartesian product.

Please help me form the query for data retrival.

Thanks

Report message to a moderator

Re: 1 dimension and 2 fact table -cartesian join [message #570991 is a reply to message #570989]

Mon, 19 November 2012 13:13

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You need to start your question again. No-one can understand what you are saying. Provide the CREATE TABLE statements for your tables, and the ALTER TABLE ADD CONSTRAINT commands to define the primary key and foreign key relationships. Then provide the query you have written. And use the [code] tags to format everything.

Report message to a moderator

Re: 1 dimension and 2 fact table -cartesian join [message #570996 is a reply to message #570989]

Mon, 19 November 2012 21:30

swas_recall
Messages: 12
Registered: May 2012
Location: Bangalore

Junior Member

Please find the details below on the problem faced..

Below are the table structures of the 3 tables out of which datedim is the conformed dimension and fact1 and fact2 are two fact tables.

SQL> create table datedim
  2  (datekey  number,
  3  datevalue date);

Table created.

SQL> create table fact1
  2  (cuskey varchar2(10),
  3  issue_key varchar2(5),
  4  weekkey number,
  5  weekdate date);

Table created.

SQL> ed
Wrote file afiedt.buf

  1  create table fact2
  2  (product_key varchar2(10),
  3  seller_key varchar2(5),
  4  weekkey number,
  5* weekdate date)
  6  /

Table created.

The primary keys of the above tables are as below.

ALTER TABLE datedim
add CONSTRAINT date_pk PRIMARY KEY (datekey);



ALTER TABLE fact1
add CONSTRAINT date_pk PRIMARY KEY (cuskey,issue_key,weekkey);


ALTER TABLE fact2
add CONSTRAINT date_pk PRIMARY KEY (product_key,seller_key,weekkey);

If i fire the below query this is giving me a Cartesian product.

SQL> ed
Wrote file afiedt.buf

  1  select f2.Product_key,f2.Seller_key,f1.Issue_key,f1.Weekdate
  2  from
  3  datedim d,fact1 f1,fact2 f2
  4  where d.datekey=f1.weekkey and
  5* d.datekey=f2.weekkey
  6  /

My result should show the below columns in the result set combining the above 3 tables.

Quote:

f2.Product_key,f2.Seller_key,f1.Issue_key,f1.Weekdate

Please let me know in this regard.

Thanks

Report message to a moderator

Re: 1 dimension and 2 fact table -cartesian join [message #571001 is a reply to message #570996]

Tue, 20 November 2012 00:16

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

At first glance: 3 tables, 2 join conditions with all 3 tables involved *shouldn't* result in Cartesian product. Could you post several sample INSERT INTO statements?

Report message to a moderator

Re: 1 dimension and 2 fact table -cartesian join [message #571005 is a reply to message #571001]

Tue, 20 November 2012 01:09

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As it is a warehouse and tables are empty, it could if Oracle thinks that the fact tables are actually the dimension ones and the dimension table is the fact one, it then create a start transformation cartesian joining the dimension (actually fact) tables.

An execution plan should be welcome.

Regards
Michel

Report message to a moderator

Re: 1 dimension and 2 fact table -cartesian join [message #571014 is a reply to message #570996]

Tue, 20 November 2012 02:19

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Your code doesn't run, you are trying to re-use names:

orcl> ALTER TABLE datedim
  2  add CONSTRAINT date_pk PRIMARY KEY (datekey);

Table altered.

orcl>
orcl>
orcl>
orcl> ALTER TABLE fact1
  2  add CONSTRAINT date_pk PRIMARY KEY (cuskey,issue_key,weekkey);
add CONSTRAINT date_pk PRIMARY KEY (cuskey,issue_key,weekkey)
               *
ERROR at line 2:
ORA-02264: name already used by an existing constraint


orcl>
orcl>
orcl> ALTER TABLE fact2
  2  add CONSTRAINT date_pk PRIMARY KEY (product_key,seller_key,weekkey);
add CONSTRAINT date_pk PRIMARY KEY (product_key,seller_key,weekkey)
               *
ERROR at line 2:
ORA-02264: name already used by an existing constraint


orcl>

and you need to add the foreign key constraints to tell Oracle what is going on. But I don't see how you can get a cartesian join from that. To check Michel's hypothesis, you can tell the optimizer that the tables are loaded:

exec dbms_stats.set_table_Stats(user,'datedim',numrows=>100)
exec dbms_stats.set_table_Stats(user,'fact1',numrows=>1000000000)
exec dbms_stats.set_table_Stats(user,'fact2',numrows=>1000000000)

Report message to a moderator

Re: 1 dimension and 2 fact table -cartesian join [message #571015 is a reply to message #571014]

Tue, 20 November 2012 02:31

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

I corrected your code and set the stats, and this is the plan 1 get:

Execution Plan
----------------------------------------------------------
Plan hash value: 2296331767

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |    10P|   559P|       |    53G(100)|999:59:59 |
|*  1 |  HASH JOIN          |           |    10P|   559P|    33G|    53G(100)|999:59:59 |
|   2 |   INDEX FULL SCAN   | DATE_PKF2 |  1000M|    22G|       |     0   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |           |  1000M|    36G|       | 16674 (100)| 00:00:04 |
|   4 |    INDEX FULL SCAN  | DATE_PK   |   100 |  1300 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| FACT1     |  1000M|    24G|       | 11304 (100)| 00:00:03 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("D"."DATEKEY"="F2"."WEEKKEY")
   3 - access("D"."DATEKEY"="F1"."WEEKKEY")

orcl>
orcl>

so Oracle isn't doing a cartesian join, but it still expect 10P rows back. Then I added the FK constraints:

alter table fact1 add constraint f1fk (weekkey) references datedim;
alter table fact1 add constraint f2fk (weekkey) references datedim;

and the plan changes:

Execution Plan
----------------------------------------------------------
Plan hash value: 3489621693

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1000M|    46G|       |  5538K  (1)| 00:21:52 |
|*  1 |  HASH JOIN         |           |  1000M|    46G|    33G|  5538K  (1)| 00:21:52 |
|   2 |   INDEX FULL SCAN  | DATE_PKF2 |  1000M|    22G|       |     0   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| FACT1     |  1000M|    24G|       | 11304 (100)| 00:00:03 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("F2"."WEEKKEY"="F1"."WEEKKEY")

orcl>

which avoids the dimension table completely.

Report message to a moderator

Re: 1 dimension and 2 fact table -cartesian join [message #571055 is a reply to message #571015]

Tue, 20 November 2012 09:50

swas_recall
Messages: 12
Registered: May 2012
Location: Bangalore

Junior Member

Many Thanks for the explanation,you are correct that it does not form a cartesian at all.I will check my production query again why i was not getting the desired result set as it was giving me a number of rows but i expected only 30.

Also i was trying to analyse the below explain plan.

My question was why it does not uses date_pk index while doing a scan?

I have added the below PKs

ALTER TABLE datedim
add CONSTRAINT date_pk_d PRIMARY KEY (datekey);

ALTER TABLE fact1
   add CONSTRAINT date_pk PRIMARY KEY (cuskey,issue_key,weekkey);

ALTER TABLE fact2
   add CONSTRAINT date_pk2 PRIMARY KEY (product_key,seller_key,weekkey)

SQL> explain plan for
  2  select f2.Product_key,f2.Seller_key,f1.Issue_key,f1.Weekdate
  3  from
  4  datedim d,fact1 f1,fact2 f2
  5  where d.datekey=f1.weekkey and
  6  d.datekey=f2.weekkey;

Explained.

SQL> SELECT * 
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 669027766

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |    40 |  2520 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN          |           |    40 |  2520 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS      |           |    20 |   740 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | DATE_PK2  |    20 |   480 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| DATE_PK_D |     1 |    13 |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | FACT1     |    20 |   520 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DATEKEY"="F1"."WEEKKEY")
   4 - access("D"."DATEKEY"="F2"."WEEKKEY")

Note
-----
   - dynamic sampling used for this statement

22 rows selected.

Thanks

Report message to a moderator

Previous Topic:	HOW to convert rows into column in ODI
Next Topic:	Not able to edit Flat File Location path

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 14 04:14:36 CST 2025