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

On Tue, 07 Aug 2007 08:38:06 -0700, Aravindh <knaravindh81_at_gmail.com> wrote:

>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
>>
>> Regards
>> KN Aravindh- Hide quoted text -
>>
>> - Show quoted text -
>
>Hi ,
>
>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
> 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.

The typical way to address this in a forum like this one would be to post the query in conjunction with EXPLAIN PLAN results. Right now I can only guess.

2 different variants of a statement, 2 different EXPLAIN PLAN somehow in the join situation psopralias is the driving table in the query, and CBO chooses efficient indexes to query the rest of the disaster.
In the variant without psopralias *different* indexes are being used. As you don't disclose the EXPLAIN PLAN results, nor the table definition, who can tell?

This bit strikes me
> AND a.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.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')
You have two almost identical subqueries. I would try to experiment with inline views, or the WITH statement.

-- 
Sybrand Bakker
Senior Oracle DBA