Hi All
Can somebody explain me how to tune this corealted
subquery.how do we convert the co-related subquery
into a inline if that helps
Select distinct PA.PersonAddress_IDX, AT.Name
AddressType,
A.Line1 Address1, A.Line2 Address2, A.City, A.State,
A.County, A.Country, A.PostalCode,
A.AllowPostalSoftYN, PA.ChangedBy,
PA.ChangedDT, PA.DeletedYN ,PA.Person_Key,
PA.Address_Key,
PA.AddressType_Key
FROM PersonAddress_h PA,Address_h A,AddressType_h AT
where PA.AddressType_Key IN (1,2,3) AND
AT.AddressType_IDX = PA.AddressType_Key
And A.Address_IDX = PA.Address_Key and PA.DeletedYN =
0
and PA.Person_KEY in (SELECT PERSON_KEY FROM
INSURED_h I where I.insured_idx=592374 )
and PA.CHANGEDDT=(select max(CHANGEDDT) from
PersonAddress_h
where PA.PERSON_KEY=Person_key and
AddressType_Key= PA.AddressType_Key
and Address_Key=PA.Address_Key)
and AT.CHANGEDDT=(select max(CHANGEDDT) from
AddressType_h
where AddressType_IDX = PA.AddressType_Key)
and A.CHANGEDDT= (Select max(CHANGEDDT) from
Address_h
where Address_IDX = PA.Address_Key and
(CHANGEDDT-to_date('10/22/2003
18:02:30','mm/dd/yyyy hh24:mi:ss'))<=0.001 )
call count cpu elapsed disk
query current rows
------- ------ -------- ---------- ----------
---------- ---------- ----------
Parse 1 0.30 0.30 0
0 0 0
Execute 1 0.00 0.00 0
0 0 0
Fetch 1 13.46 31.73 27979
23786 31 0
------- ------ -------- ---------- ----------
---------- ---------- ----------
total 3 13.76 32.04 27979
23786 31 0
Rows Execution Plan
-------
---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF
'ADDRESS_H'
1 NESTED LOOPS
0 HASH JOIN
1100 HASH JOIN
550 HASH JOIN
550 TABLE ACCESS GOAL: ANALYZED (BY
INDEX ROWID) OF
'PERSONADDRESS_H'
606 NESTED LOOPS
55 TABLE ACCESS GOAL: ANALYZED (BY
INDEX ROWID)
OF 'INSURED_H'
55 INDEX (RANGE SCAN) OF
'INDX_INSURED_H_IDX_EDATE_CDATE' (NON-UNIQUE)
550 INDEX (RANGE SCAN) OF
'INDX_PRSNADDR_PRSN_ADDR_H'
(NON-UNIQUE)
3 VIEW OF 'VW_SQ_2'
3 SORT (GROUP BY)
6 INDEX (FAST FULL SCAN) OF
'CI_ADDRESSTYPE_H'
(NON-UNIQUE)
6 TABLE ACCESS GOAL: ANALYZED (FULL)
OF
'ADDRESSTYPE_H'
74421 VIEW OF 'VW_SQ_3'
74421 SORT (GROUP BY)
462900 TABLE ACCESS GOAL: ANALYZED (FULL)
OF
'ADDRESS_H'
0 INDEX (RANGE SCAN) OF 'CI_ADDRESS_H'
(NON-UNIQUE)
0 VIEW OF 'VW_SQ_1'
0 SORT (GROUP BY)
0 INDEX (FULL SCAN) OF 'INDX_PRSNADDR_ALL'
(NON-UNIQUE)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk
query current rows
------- ------ -------- ---------- ----------
---------- ---------- ----------
Parse 1 0.30 0.30 0
0 0 0
Execute 2 0.00 0.01 0
0 0 0
Fetch 1 13.46 31.73 27979
23786 31 0
------- ------ -------- ---------- ----------
---------- ---------- ----------
total 4 13.76 32.05 27979
23786 31 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
regards
Hrishy
________________________________________________________________________
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?hrishy?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
