Re: Using Flattened table COSTLIER than using JOINS with the SETUP tables.
Date: Wed, 08 Aug 2007 00:28:39 -0700
Message-ID: <1186558119.935234.182400_at_i38g2000prf.googlegroups.com>
On Aug 8, 2:20 am, "Paul Linehan" <plinehan__A_at_T__yahoo__D.OT__COM>
wrote:
> sybra..._at_hccnet.nl wrote:
> > The table will not be in a single block. records should be in a
> > single block.
> > You would need to use
> > analyze table .... compute statistics or better
> > exec
> > dbms_stats.gather_table_stats(user,'<table_name>',estimate_percent=>NU
> > LL)
> > doing so you will can query
> > select avg_row_len,chain_cnt, num_rows,
> > from user_tables where table_name = '<table_name>'
>
> Why? Can't you simply get the db_block_size parameter
> SQL> SHOW PARAMETER BLOCK and then check against avg_row_len?
>
> My point here is, why gather the stats (which could be useful
> for other reasons) to find out if the record size exceeds the
> size of a db block?
>
> Paul...
Thanks for your reply.. I am also attaching the results of the user tables and also attaching the Explain plan statements for the query with and without the PSOPRALIAS.The problem here as stated earlier is that the Cose of the query is drastically coming down when we JOIN with the second PSOPRALIAS TABLE.
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'
qUERY WITHOUT 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'
Thanks. Please tell me if you require more information..
KN Aravindh Received on Wed Aug 08 2007 - 09:28:39 CEST