Re: FLATTENED TABLE costing more than JOINING two tables

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Wed, 08 Aug 2007 18:28:59 -0000
Message-ID: <1186597739.910215.31820@q3g2000prf.googlegroups.com>

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 Received on Wed Aug 08 2007 - 13:28:59 CDT