DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: sql tuning

  1. #1
    Join Date
    Feb 2001
    Posts
    83
    Hello,

    the following sql takes 20secs, i got to dec, here master_sample has 500,000 and company has 20,000 records

    SELECT DISTINCT vendor.company_id, vendor.co_name
    FROM company vendor, master_sample
    WHERE master_sample.company_id = 1
    AND vendor.company_id = master_sample.ms_submitter_id
    UNION select -1, ' < None >' from dual
    ORDER BY 2

    Explain plan

    SELECT STATEMENT CHOOSE
    SORT UNIQUE
    UNION-ALL
    NESTED LOOPS
    TABLE ACCESS BY ROWID MASTER_SAMPLE ANALYZED
    INDEX RANGE SCAN IDX_MS_COMPANY_ID NON-UNIQUE ANALYZED
    TABLE ACCESS BY ROWID COMPANY ANALYZED
    INDEX UNIQUE SCAN PK_COMPANY UNIQUE ANALYZED
    TABLE ACCESS FULL DUAL

    So, Is there anyway to optimize this.


    with regards
    Prasanna S

  2. #2
    Join Date
    May 2000
    Posts
    58
    Try this

    SELECT DISTINCT vendor.company_id, vendor.co_name
    FROM company vendor
    where exists ( select 'x' from master_sample
    WHERE master_sample.company_id = 1
    AND master_sample.ms_submitter_id = vendor.company_id )
    UNION select -1, ' < None >' from dual
    ORDER BY 2


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width