Re: query tuning

From: hrishy <hrishys_at_yahoo.co.uk>
Date: 3 Nov 2003 01:31:28 -0800
Message-ID: <4ef2a838.0311030131.239ec550_at_posting.google.com>

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 Received on Mon Nov 03 2003 - 10:31:28 CET