Using Flattened table COSTLIER than using JOINS with the SETUP tables.
From: Aravindh <knaravindh81_at_gmail.com>
Date: Tue, 07 Aug 2007 04:10:44 -0000
Message-ID: <1186459844.733014.205920_at_x40g2000prg.googlegroups.com>
All,
[Quoted] We are using a flattened table for the Reports. This table is called as JP_HISTORY_TBL. This table is like a Data Warehouse having all the required fields for the reports in a flattened structure. So it has the codes as well as the description. For Example it has the Member ID as well as the member name.
Prior to this development it had only the Member ID and we used to fetch the Member ID from its corresponding SET UP table(PERSON Table).
We thought of reducing the Joins and got the Member name also into the JP_HISTORY_TBL...
Similarly we have got the other DESCRIPTION data as well into the JP_HISTORY_TBL.
We expected this to reduce the query cost but oppposed to this the query cost is getting increased and it is taking a lot of time to get executed.
We are not able to figure our why this is happening..Is this because JP_HISTORY_TBL is a very large table containing a lot of TRANSACTIONAL data ? Then how do they create the datawarehouse with all the flattened information ?
Date: Tue, 07 Aug 2007 04:10:44 -0000
Message-ID: <1186459844.733014.205920_at_x40g2000prg.googlegroups.com>
All,
[Quoted] We are using a flattened table for the Reports. This table is called as JP_HISTORY_TBL. This table is like a Data Warehouse having all the required fields for the reports in a flattened structure. So it has the codes as well as the description. For Example it has the Member ID as well as the member name.
Prior to this development it had only the Member ID and we used to fetch the Member ID from its corresponding SET UP table(PERSON Table).
We thought of reducing the Joins and got the Member name also into the JP_HISTORY_TBL...
Similarly we have got the other DESCRIPTION data as well into the JP_HISTORY_TBL.
We expected this to reduce the query cost but oppposed to this the query cost is getting increased and it is taking a lot of time to get executed.
We are not able to figure our why this is happening..Is this because JP_HISTORY_TBL is a very large table containing a lot of TRANSACTIONAL data ? Then how do they create the datawarehouse with all the flattened information ?
For example
Select Member_ID,MEMBER_NAME from JP_HISTORY_TBL is costlier than this
select A.MEMBER_ID,B.MEMBER_NAME from JP_HISTORY_TBL A,PS_RD_PERSON B
where a.memberid = b.memberid
Your help in this action will be greatly greatly appreciated..
Thanks
KN Aravindh
Received on Tue Aug 07 2007 - 06:10:44 CEST