| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> FLATTENED TABLE costing more than JOINING two tables
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 103FILTER
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
) 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
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'
Received on Wed Aug 08 2007 - 04:55:27 CDT
 |
 |