Re: FLATTENED TABLE costing more than JOINING two tables

On Aug 8, 11:28 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Aug 8, 5:55 am, Aravindh <knaravind..._at_gmail.com> wrote:
>
>
>
>
>
> > 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 ?
>
> What version? How current are your stats? Do you really have that
> many chained rows?
>
> The following VIEW (used in the HASH JOIN earlier) looks like your
> problem in an absolute sense...
>
> VIEW VW_SQ_1 2M
> 50 M 10783
> SORT GROUP BY 2M
> 36 M 10783
> INDEX FAST FULL SCAN JP_HISTORY_TBL_TEST 2M
> 44 M 1347
>
> HTH,
>
> Steve- Hide quoted text -
>
> - Show quoted text -

Hi Steve,

Currently I am using Oracle 9i.These stats are current in the DEVELOPMENT region..
Yes we are currently aiming at reducing the chained rows and I will get back if I have any break through!

Regards
KN Aravindh Received on Thu Aug 09 2007 - 00:27:03 CDT