optimization required

From: Nicsmart <nishantchandresh12_at_gmail.com>
Date: Mon, 25 Feb 2008 21:35:10 -0800 (PST)
Message-ID: <403074bf-1b94-47de-a42d-988f9b2eb9b8@64g2000hsw.googlegroups.com>


I executed the following query in toad

SELECT DISTINCT a.prv_cod

           FROM iw_rle_prv a, iw_prv_mst b
          WHERE a.rle_cod IN ('CEO')
            AND a.prv_cod = b.prv_cod
            AND (b.prv_cre_status = 'N' OR b.prv_cre_status IS NULL)
            AND a.del_ind = 'N'
            AND b.del_ind = 'N'


Explain plan for the above query is
SELECT STATEMENT Optimizer Mode=ALL_ROWS68  	 	6.55350208921878
  HASH JOIN				68  	2 K	6.55350208921878
    TABLE ACCESS FULL	IWISEPRDN.IW_RLE_PRV	73  	1 K	3.03524459882865
    TABLE ACCESS FULL	IWISEPRDN.IW_PRV_MST	199  	2 K	3.01541637294416

number of records in table iw_rle_prv is 991 and iw_prv_mst is 251

whats the reason for the above query to show such high cost?Any suggestions to reduce it Received on Mon Feb 25 2008 - 23:35:10 CST

Original text of this message