Re: Using Flattened table COSTLIER than using JOINS with the SETUP tables.

[Quoted] On Tue, 07 Aug 2007 04:10:44 -0000, Aravindh <knaravindh81_at_gmail.com> wrote:

>All,
>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

Just a wild guess using my crystal ball. What is the length of a record in JP_HISTORY_TBL? Do they still fit in one single database block? If not, then that is your answer.
You have massive row chaining.
Row chaining results in extra IO, which is not for free. Row chaining is not taking into account by the optimizer. You need to, either
a) recreate your database and increase the block size to 16 or 32k b) deflatten the table.

Other than that, this post belongs in
news:comp.databases.oracle.server

Hth

-- 
Sybrand Bakker
Senior Oracle DBA