query tuning
From: hrishy <hrishys_at_yahoo.co.uk>
Date: 1 Nov 2003 09:46:22 -0800
Message-ID: <4ef2a838.0311010946.10248ce4_at_posting.google.com>
Date: 1 Nov 2003 09:46:22 -0800
Message-ID: <4ef2a838.0311010946.10248ce4_at_posting.google.com>
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 ATwhere 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
any other suggestions Received on Sat Nov 01 2003 - 18:46:22 CET