-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|