Hi All
I have the following query to be tuned..
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 )
with the plan as
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)
how do we tune this query..i tried writing somethin like
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,
(select max(CHANGEDDT) maxchdt,Person_key,AddressType_Key,Address_Key
from PersonAddress_h
group by Person_key,AddressType_Key,Address_Key) X,
(select max(CHANGEDDT) maxchdt, Address_IDX
from Address_h
where CHANGEDDT-to_date('10/22/2003 18:02:30','mm/dd/yyyy
hh24:mi:ss')<=0.001
group by Address_IDX) Y ,
(select max(CHANGEDDT) maxchdt,AddressType_IDX
from AddressType_h
group by AddressType_IDX) Z
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=X.maxchdt
and PA.PERSON_KEY=X.Person_key
and PA.AddressType_Key=X.AddressType_Key
and PA.Address_Key=X.Address_Key
and AT.CHANGEDDT=Y.maxchdt
and PA.AddressType_Key=Z.AddressType_IDX
and A.CHANGEDDT=Y.maxchdt
and PA.Address_Key=Y.Address_IDX
