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

On Aug 8, 2:20 am, "Paul Linehan" <plinehan__A@xxxxxxxxxxxxxxxxxxx>
wrote:
sybra...@xxxxxxxxx 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







.

Relevant Pages

  • Re: The Barrister and me and myself and I
    ... trim to that line only? ... That way the query and your reply jumps straight ... off the screen and smacks the reader between the eyes; ...
    (alt.usage.english)
  • howto ignore whitespace in select query
    ... I understand my table definition has stated a "char" with ... extra 5 spaces returned in my query. ... returns "Santuon " instead of "Santuon". ... I do not wish to trim the string afterwards. ...
    (microsoft.public.sqlserver.mseq)
  • Re: The Barrister and me and myself and I
    ... > you're responding to the last line only, that it would be sensible to ... > trim to that line only? ... That way the query and your reply jumps straight ... > letters, memos, novels, and see no reason why Usenet shouldn't receive ...
    (alt.usage.english)
  • Re: How to trim substrings?
    ... I know ltrim() or rtrimonly trim out one character/digit once. ... This must be done purely by SQL*Plus query. ... SQL> select ltrimfrom dual; ...
    (comp.databases.oracle.misc)
  • I need to compare a value from one table and return a result from another ie Decode
    ... I am trying to use the design view in Access XP to construct a query and ... each column of the query with the code_decode table and then returning ... Table name decode ...
    (microsoft.public.access.gettingstarted)