Re: FLATTENED TABLE costing more than JOINING two tables

On Aug 8, 11: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 ?
>
> 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
>
> SELECT avg_row_len,chain_cnt, num_rows
> FROM user_tables WHERE table_name = 'JP_HISTORY_TBL'
>
> query result :-
>
> 198 3006586 5797557
>
> Next Execution plan without PSOPRALIAS :- is 13135
>
> Operation Object Name
> Rows Bytes Cost
>
> SELECT STATEMENT Optimizer Mode=CHOOSE
> 1 13135
> FILTER
> TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL
> 1 31 4
> NESTED LOOPS
> 1 1 K 13123
> NESTED LOOPS
> 1 1 K 13119
> HASH JOIN
> 1 150 13115
> NESTED LOOPS OUTER
> 1 128 2321
> NESTED LOOPS
> 1 106 2319
> INDEX FAST FULL SCAN JP_HISTORY_IDX2
> 1 24 2315
> TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL
> 1 82 4
> INDEX RANGE SCAN JP_HISTORY_IDX2
> 1 2
> TABLE ACCESS BY INDEX ROWID
> PS_JP_CYCLE_TIME 1 22 2
> INDEX RANGE SCAN PS_JP_CYCLE_TIME
> 1 1
> VIEW VW_SQ_1 2
> M 50 M 10783
> SORT GROUP BY 2
> M 36 M 10783
> INDEX FAST FULL SCAN JP_HISTORY_TBL_TEST 2
> M 44 M 1347
> TABLE ACCESS BY INDEX ROWID PS_JP_DM_ALT_STG
> 1 1 K 4
> INDEX RANGE SCAN JP_DM_ALT_STG_IDX1
> 1 2
> INDEX RANGE SCAN CID_SNEW
> 1 2
> SORT AGGREGATE
> 1 16
> TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL
> 1 16 6
> INDEX RANGE SCAN CID_SNEW
> 2 3
> SORT AGGREGATE
> 1 16
> TABLE ACCESS BY INDEX ROWID JP_HISTORY_TBL
> 1 16 6
> INDEX RANGE SCAN CID_SNEW
> 2 3
>
> Execution Plan with PSOPRALIAS is coming down drastically to about
> 100
>
> SELECT STATEMENT Optimizer
> Mode=CHOOSE 1 103
> FILTER
> TABLE ACCESS BY INDEX ROWID
> JP_HISTORY_TBL 1 31 4
> NESTED
> LOOPS 1 1
> K 97
> NESTED
> LOOPS 1 1
> K 93
> NESTED
> LOOPS 1 1 K
> 93
> NESTED
> LOOPS 1 128
> 89
> NESTED LOOPS
> OUTER 1 104 87
> TABLE ACCESS BY INDEX ROWID
> JP_HISTORY_TBL 1 82 85
> INDEX RANGE SCAN
> JP_HISTORY_IDX2 1 83
> SORT
> AGGREGATE 1 16
> INDEX RANGE SCAN
> JP_HISTORY_TBL_TEST 1 16 3
> TABLE ACCESS BY INDEX ROWID
> PS_JP_CYCLE_TIME 1 22 2
> INDEX RANGE SCAN
> PS_JP_CYCLE_TIME 1 1
> INDEX RANGE SCAN
> JP_HISTORY_TBL_I3 1 24 2
> SORT
> AGGREGATE 1 16
> TABLE ACCESS BY INDEX ROWID
> JP_HISTORY_TBL 1 16 6
> INDEX RANGE SCAN
> CID_SNEW 2 3
> TABLE ACCESS BY INDEX ROWID
> PS_JP_DM_ALT_STG 1 1 K 4
> INDEX RANGE SCAN
> JP_DM_ALT_STG_IDX1 1 2
> INDEX UNIQUE SCAN
> PS_PSOPRALIAS 1 21
> INDEX RANGE SCAN
> CID_SNEW 1 2
> SORT
> AGGREGATE 1
> 16
> TABLE ACCESS BY INDEX ROWID
> JP_HISTORY_TBL 1 16 6
> INDEX RANGE SCAN
> CID_SNEW 2 3
>
> Also I have attached both the queries for your reference :- (both of
> them are same except that one has the PSOPRALIAS and the other not
> having that table)
>
> List of Indexes Used
> *********************************
>
> CREATE INDEX JP_HISTORY_TBL_I3 ON JP_HISTORY_TBL
> (CASE_ID, ROW_LASTMANT_DTTM, STATUS_NEW)
>
> CREATE INDEX JP_HISTORY_IDX2 ON JP_HISTORY_TBL
> (ASSIGNED_TO_PG_NEW, CASE_ID, STATUS_NEW, ROW_LASTMANT_DTTM)
>
> CREATE INDEX CID_SNEW ON JP_HISTORY_TBL
> (CASE_ID, STATUS_NEW)
>
> CREATE INDEX JP_HISTORY_TBL_TEST ON JP_HISTORY_TBL
> (CASE_ID, ROW_LASTMANT_DTTM, ASSIGNED_TO_PG_FLAG)
>
> CREATE UNIQUE INDEX PS_PSOPRALIAS ON PSOPRALIAS
> (OPRID, OPRALIASTYPE)
>
> CREATE UNIQUE INDEX PS_JP_CYCLE_TIME ON PS_JP_CYCLE_TIME
> (CASE_ID, BUSINESS_UNIT)
>
> CREATE INDEX JP_DM_ALT_STG_IDX1 ON PS_JP_DM_ALT_STG
> (CASE_ID, PROVIDER_GRP_ID, ROW_LASTMANT_DTTM, CLOSED_DTTM,
> JP_REOPEN_DATE,
> RC_STATUS)
>
> qUERY WITH oPRALIAS TABLE
> ***************************
>
> SELECT c.business_unit, A.case_id, A.assigned_to_pg_old,
> A.assigned_to_pg_new,
> --A.ASSIGNED_TO_OLD,A.ASSIGNED_TO_NEW,
> Z.status_old, Z.status_new,
> Z.status_new "SNEW",
> CASE WHEN Z.row_lastmant_dttm >
> A.row_lastmant_dttm
> THEN z.row_lastmant_dttm
> ELSE
> A.row_lastmant_dttm
> END AS row_lastmant_dttm ,
> A.transaction_flag, c.rc_short_descr,
> sysadm.Datediff (jp_exp_dttm) AS "DIFF",
> c.rc_short_descr1,
> rptrules.Jp_Func_Tz_Conv
> ('EST',
> 'IST',
> DECODE (TRIM (c.jp_draft_to_status),
> NULL, c.row_added_dttm,
> c.jp_draft_chng_dttm
> )
> ) AS row_added_dttm,
> c.productdescr, c.jp_probtype_descr,
> c.rc_summary,
> DECODE(trim(TO_CHAR(c.jp_rsln_dtls)),NULL,'N/
> A',SUBSTR(TRIM(TO_CHAR(c.jp_rsln_dtls)),1,762)) AS jp_rsln_dtls,
> DECODE (TRIM (c.jp_error_desc),
> NULL, 'N/A',
> c.jp_error_desc
> ) AS jp_error_desc,
> c.descr1,
> REPLACE (INITCAP (c.name_display), ',', ', ') AS
> "NAME",
> c.jp_orig_pg_name,
> DECODE (TRIM (A.assigned_to_new_name),
> NULL, 'Unassigned',
> REPLACE(INITCAP
> (A.assigned_to_new_name),
> ',', ', ')
> ) AS "ASS",
> A.assigned_to_pg_new_name, c.company_name,
> DECODE (TRIM (c.jp_cause_desc),
> NULL, 'N/A',
> c.jp_cause_desc
> ) AS jp_cause_desc,
> c.jp_factor,
> DECODE (TRIM (c.jp_reason_for_use),
> NULL, 'N/A',
> c.jp_reason_for_use
> ) AS
> jp_reason_for_use,
> A.DURATION,
> rptrules.Jp_Func_Tz_Conv
> ('EST',
> 'IST',
> TO_DATE
> (CONCAT
> (TO_CHAR (c.jp_received_dt, 'dd-Mon-yyyy'),
>
> SUBSTR (TO_CHAR (c.jp_received_time,
>
> 'dd-Mon-yyyy hh24:mi:ss'
> ),
>
> 12
> )
> ),
> 'dd-Mon-
> yyyy hh24:mi:ss'
> )
> ) AS "RDATE",
> --addition of new owner fields
> REPLACE (INITCAP (c.BO_NAME), ',', ', ') AS "BO_NAME",
> c.OWNER_PG_ID,
> c.NAME1 AS OWNER_PG
> -- c.JP_STATUS_CONCAT,
> 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.JP_HISTORY_TBL x
> WHERE x.case_id = A.case_id
> AND Z.status_new = 'Open - In Progress'
> 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 < '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 z.case_id = b1.case_id
> AND b1.row_lastmant_dttm < '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 x.case_id = b1.case_id
> AND b1.row_lastmant_dttm <= '22 Jul 2007'
> AND b1.status_flag = 'Y')
> AND x.status_new != 'Cancelled'

Excellent post!!! Fully unformatted and SQL statements way below the EXPLAIN PLAN so everyone is forced to scroll big time!!!! Did you look at the results at all? Did you look at all at chain_cnt vs num_rows? So what are your conclusions? Or do you expect someone here is going to make up for your laziness and resolve your problems for free?

--
Sybrand Bakker
Senior Oracle DBA