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

[Quoted] On Aug 7, 10:41 am, sybra..._at_hccnet.nl wrote:
> On Tue, 07 Aug 2007 04:10:44 -0000, Aravindh <knaravind..._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- Hide quoted text -
>
> - Show quoted text -

HI Sybrand,

[Quoted] Thanks for your quick reply. Can you tell us how to see whether the JP_HISTORY_TBL is present in a single block or not ? Is there a command which can be executed in TOAD or SQL PLUS ? If you could tell us the command we would execute it and find out whether the table is in a SINGLE Block or not.

Your quick reply will be appreciated. Thanks

Regards
KN Aravindh Received on Tue Aug 07 2007 - 15:08:37 CEST