Re: Using Flattened table COSTLIER than using JOINS with the SETUP tables.
Date: Tue, 07 Aug 2007 08:38:06 -0700
[Quoted] On Aug 7, 6:08 pm, Aravindh <knaravind..._at_gmail.com> wrote:
> 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,
> 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
> KN Aravindh- Hide quoted text -
> - Show quoted text -
As an addition please look into the two sample queries below
SELECT 1 AS "A"
FROM sysadm.jp_history_tbl a, sysadm.jp_history_tbl z, sysadm.ps_jp_dm_alt_stg c, sysadm.psopralias d, sysadm.ps_jp_cycle_time e, sysadm.ps_rf_provider_grp f, sysadm.ps_rd_person_name g, sysadm.jp_history_tbl x WHERE x.case_id = a.case_id
AND z.status_new = 'Open - In Progress' AND f.provider_grp_id = a.assigned_to_pg_new AND (g.name_type = 'PREFERRED' OR g.name_type IS NULL) AND (g.primary_ind = 'Y' OR g.primary_ind IS NULL)
AND g.person_id(+) = a.assigned_to_new AND a.case_id = e.case_id(+) AND a.assigned_to_pg_new = 'IS00000025' AND a.row_lastmant_dttm = (SELECT MAX (b1.row_lastmant_dttm) FROM sysadm.jp_history_tbl b1 WHERE a.case_id = b1.case_id [Quoted] AND b1.row_lastmant_dttm < TO_DATE ('21-Jul-2007') AND b1.assigned_to_pg_flag = 'Y')AND d.opraliastype = 'PER'
AND z.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm) FROM sysadm.jp_history_tbl b1 WHERE a.case_id = b1.case_id AND b1.row_lastmant_dttm < TO_DATE ('21-Jul-2007') AND b1.status_flag = 'Y')
AND (z.status_new NOT IN ('Closed', 'Cancelled', 'Draft'))
AND a.case_id = z.case_id AND c.case_id = a.case_id AND d.oprid = c.row_added_oprid
AND (c.jp_orig_prvdr_grp != 'IS00000025') AND x.row_lastmant_dttm =
(SELECT MAX (b1.row_lastmant_dttm) FROM sysadm.jp_history_tbl b1 WHERE a.case_id = b1.case_id AND b1.row_lastmant_dttm <= TO_DATE ('21-Jul-2007') AND b1.status_flag = 'Y')
AND x.status_new != 'Cancelled'
This has the PSOPRALIAS table (SET UP table joined). When we have this
table the query results come up in less than 285 msec...
When we remove the join of the PSOPRALIAS table (remove the set up
table) the cost of the query goes up drastically and the results come
up in about 50 sec.
Please note that this table does not have any impact on the number of rows returned. Received on Tue Aug 07 2007 - 17:38:06 CEST