query tuning

[hrishy]

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

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.google.c om...

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

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.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
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.AddressType_Key=X.AddressType_Key
and PA.Address_Key=X.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 )

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_PRSN_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_ALL (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