Re: Can you improve this SQL

Are you sure it's the complete "tkprof" output? Try adding "function based" index on LISTING.search_listed_name column. That may improve the performance.

"gabisa" <gabisa_at_amdocs.com> wrote in message news: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 Tue Oct 23 2001 - 07:04:51 CEST