query tuning

[hrishy]

Hi All

I have the following query to be tuned..

Select distinct PA.PersonAddres s_IDX, AT.Name AddressType,
A.Line1 Address1, A.Line2 Address2, A.City, A.State,
A.County, A.Country, A.PostalCode, A.AllowPostalSo ftYN, 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=5 92374 )
and PA.CHANGEDDT=(s elect max(CHANGEDDT) from PersonAddress_h
where PA.PERSON_KEY=P erson_key and
AddressType_Key = PA.AddressType_ Key
and Address_Key=PA. Address_Key)
and AT.CHANGEDDT=(s elect 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_CDAT E' (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.PersonAddres s_IDX, AT.Name AddressType,
A.Line1 Address1, A.Line2 Address2, A.City, A.State,
A.County, A.Country, A.PostalCode, A.AllowPostalSo ftYN, 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_Ke y
from PersonAddress_h
group by Person_key,Addr essType_Key,Add ress_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,Address Type_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=5 92374 )
and PA.CHANGEDDT=X. maxchdt
and PA.PERSON_KEY=X .Person_key
and PA.AddressType_ Key=X.AddressTy pe_Key
and PA.Address_Key= X.Address_Key
and AT.CHANGEDDT=Y. maxchdt
and PA.AddressType_ Key=Z.AddressTy pe_IDX
and A.CHANGEDDT=Y.m axchdt
and PA.Address_Key= Y.Address_IDX

any other suggestions

[Jasper Scholten]

Hrishy,

I did not look for long but why is the distinct in there?

Loose that one first if it is not necessary. Secondly, what's the problem?
Why do you want to tune it, how long is it taking?

I personally do not like inline views in the query, if possible take them up
in the main query.

Please post version of Oracle you are using and why youre tables are not
analyzed.

Best regards,
--
Jasper Scholten
DBA / Application Manager / Systems Engineer
"hrishy" <hr*****@yahoo. co.uk> schreef in bericht
news:4e******** *************** ***@posting.goo gle.com...

Hi All

I have the following query to be tuned..

Select distinct PA.PersonAddres s_IDX, AT.Name AddressType,
A.Line1 Address1, A.Line2 Address2, A.City, A.State,
A.County, A.Country, A.PostalCode, A.AllowPostalSo ftYN, 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=5 92374 )
and PA.CHANGEDDT=(s elect max(CHANGEDDT) from PersonAddress_h
where PA.PERSON_KEY=P erson_key and
AddressType_Key = PA.AddressType_ Key
and Address_Key=PA. Address_Key)
and AT.CHANGEDDT=(s elect 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_CDAT E' (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.PersonAddres s_IDX, AT.Name AddressType,
A.Line1 Address1, A.Line2 Address2, A.City, A.State,
A.County, A.Country, A.PostalCode, A.AllowPostalSo ftYN, 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_Ke y
from PersonAddress_h
group by Person_key,Addr essType_Key,Add ress_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,Address Type_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=5 92374 )
and PA.CHANGEDDT=X. maxchdt
and PA.PERSON_KEY=X .Person_key
and PA.AddressType_ Key=X.AddressTy pe_Key
and PA.Address_Key= X.Address_Key
and AT.CHANGEDDT=Y. maxchdt
and PA.AddressType_ Key=Z.AddressTy pe_IDX
and A.CHANGEDDT=Y.m axchdt
and PA.Address_Key= Y.Address_IDX

any other suggestions

[hrishy]

Hi Jasper

Thank you very much for taking your valuable time out and galncing
over this problem.If we do not use distinct we get a different result.

I have rewritten the query like this now the response time has come
down from 3min to 50 seconds .However what baffles me is why is this
query not using the index on two tables

Select distinct PA.PersonAddres s_IDX, AT.Name AddressType,
A.Line1 Address1, A.Line2 Address2, A.City, A.State,
A.County, A.Country, A.PostalCode, A.AllowPostalSo ftYN,
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_Ke y
from PersonAddress_h
group by Person_key,Addr essType_Key,Add ress_Key) X
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=5 92374 )
and PA.CHANGEDDT=X. maxchdt
and PA.AddressType_ Key=X.AddressTy pe_Key
and PA.Address_Key= X.Address_Key
and AT.CHANGEDDT=(s elect 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 )

The exaplain plan now is

Rows Row Source Operation
------- ---------------------------------------------------
3 SORT UNIQUE
8 FILTER
20 SORT GROUP BY
4256 TABLE ACCESS BY INDEX ROWID ADDRESS_H
8513 NESTED LOOPS
4256 NESTED LOOPS
1120 HASH JOIN
1120 HASH JOIN
560 HASH JOIN
560 TABLE ACCESS BY INDEX ROWID PERSONADDRESS_H
617 NESTED LOOPS
56 TABLE ACCESS BY INDEX ROWID INSURED_H
56 INDEX RANGE SCAN INDX_INSURED_H_ IDX_EDATE_CDATE
(object id 35548)
560 INDEX RANGE SCAN INDX_PRSNADDR_P RSN_ADDR_H (object
id 56328)
3 VIEW
3 SORT GROUP BY
6 INDEX FAST FULL SCAN CI_ADDRESSTYPE_ H (object id
34443)
6 TABLE ACCESS FULL ADDRESSTYPE_H
459380 VIEW
459380 SORT GROUP BY
462919 TABLE ACCESS FULL ADDRESS_H
4256 INDEX RANGE SCAN INDX_PRSNADDR_A LL (object id 56331)
4256 INDEX RANGE SCAN CI_ADDRESS_H (object id 34445)

what baffles me is why the full table scans on ADDRESSTYPE_H and
ADDRESS_H

The tables ADDRESSTYPE_H and ADDRESS_H contain 464080 and 8 records
respectively

Is ther a better way to rewrite thie query

regards
Hrishy

[hrishy]

Hi All

After some research i solved this problem..now the query takes about
30 seconds ..it ws slwo initially as the literals were not able to
make use of the histogram and they were missing.After caculatiung
histograms for the missing column the query takes about 30
seconds.Jasper thank you for your help.

regards
Hrishy