Re: query tuning

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" <hrishys_at_yahoo.co.uk> schreef in bericht
news:4ef2a838.0311010946.10248ce4_at_posting.google.com...


> 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