tuning a co-related query howto

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Tue, 28 Oct 2003 11:59:51 -0800
Message-ID: <F001.005D4D47.20031028115951@fatcity.com>

Hi All

Can somebody explain me how to tune this corealted subquery.how do we convert the co-related subquery into a inline if that helps

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 )

call     count       cpu    elapsed       disk     
query    current        rows

------- ------ -------- ---------- ----------
---------- ---------- ----------

Parse        1      0.30       0.30          0        
 0          0           0
Execute      1      0.00       0.00          0        
 0          0           0
Fetch        1     13.46      31.73      27979     
23786         31           0

------- ------ -------- ---------- ----------
---------- ---------- ----------

total        3     13.76      32.04      27979     
23786         31           0




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)

---

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk     
query    current        rows

------- ------ -------- ---------- ----------
---------- ---------- ----------

Parse        1      0.30       0.30          0        
 0          0           0
Execute      2      0.00       0.01          0        
 0          0           0
Fetch        1     13.46      31.73      27979     
23786         31           0

------- ------ -------- ---------- ----------
---------- ---------- ----------

total        4     13.76      32.05      27979     
23786         31           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1

regards
Hrishy

---

Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?hrishy?=
  INET: hrishys_at_yahoo.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Oct 28 2003 - 13:59:51 CST