Home -> Usenet -> comp.databases.oracle -> Can you improve this SQL

Can you improve this SQL

From: gabisa <gabisa_at_amdocs.com>
Date: 22 Oct 2001 00:31:26 -0700
Message-ID: <23071fab.0110212331.b37891e_at_posting.google.com> 






Hi


my question is


I have join between 3 tables 


LISTING    8511273



ADDRESS   11872238



LOCALITY       1751


the join is


SELECT listing_id,listing_version,NVL(listed_name,''),
NVL(LISTING.customer_id,0),NVL(account_tn_npa,''),
NVL(account_tn_cop,''),NVL(account_tn_line_no,''),
NVL(ali_code,''),NVL(listed_tn_npa,''),




NVL(listed_tn_cop,''),NVL(listed_tn_line_no,''),NVL(lni,''),
NVL(style_code,''),NVL(listing_status,''),
TO_CHAR(listing_eff_date,'YYYYMMDD'),

ADDRESS.house_num_prefix,ADDRESS.house_no,
ADDRESS.street_directional,ADDRESS.street_name,
ADDRESS.street_name_suffix,ADDRESS.post_directional,
ADDRESS.apartment,ADDRESS.po_box,




LOCALITY.locality_abbr,ADDRESS.state_override,
ADDRESS.country,LOCALITY.state_code,reject_id,
last_version_ind,language_type 


FROM LOCALITY,ADDRESS,LISTING 


WHERE LISTING.listed_address_id = ADDRESS.address_id(+) 
  AND ADDRESS.locality_id = LOCALITY.locality_id(+) 
  AND LISTING.last_version_ind = 'Y' 
  AND LISTING.listing_status = 'A' 
  AND LISTING.search_listed_name LIKE UPPER(':1%')

the explain plan is
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
      0   NESTED LOOPS (OUTER)
    329    NESTED LOOPS (OUTER)
    350     TABLE ACCESS (BY INDEX ROWID) OF 'LISTING'
    351      INDEX (RANGE SCAN) OF 'LISTING_1IX' (NON-UNIQUE) 
    329     TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS'
    329      INDEX (UNIQUE SCAN) OF 'ADDRESS_PK' (UNIQUE)
      0    TABLE ACCESS (BY INDEX ROWID) OF 'LOCALITY'
      0     INDEX (UNIQUE SCAN) OF 'LOCALITY_PK' (UNIQUE)





this SQL run in online (on web)


listing_1ix is on search_listed_name 


address_pk = address_id


locality_pk = locality_id


the time take about 30 sec for 350 record 
can we improve this sql?



thanks


gabby
Received on Mon Oct 22 2001 - 09:31:26 CEST












Original text of this message