Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: need help with long query

Re: need help with long query

From: Anton Eskov <anton_at_msoft.ru>
Date: Thu, 7 May 1998 12:48:25 +0400
Message-ID: <894530779.678398@telecom.lek.ru>


Lynn,

Are you sure that the bottleneck is at the server side?

Still you can try to optimize your SQL. The general idea is to avoud sub-queries where possible, or at least IN (select...) conditions.

Try to use EXISTS statement instead of IN, as follows:

> AND A.county_id IN (select distinct F.id
> from chpa_county F
> where lower(F.county) IN ('broward', 'dade'))
AND EXISTS ( SELECT f.id FROM chpa_county f WHERE f.id = a.county_id AND LOWER(f.county) IN ('broward', 'dade'))

>AND A.ffn IN (select distinct D.ffn
> from chpa_county D
> where lower(D.county) IN ('broward','dade')
AND EXISTS (SELECT d.ffn FROM chpa_county d WHERE a.ffn=d.ffn AND lower(D.county) IN ('broward','dade'))

> AND D.ffn IN (select distinct C.ffn
> from chpa_insurer C));

AND EXISTS SELECT (c.ffn FROM chpa_insurer C WHERE d.ffn=c.ffn)

The further step could be to avoid 2nd level sub-query:

>AND A.ffn IN (select distinct D.ffn
> from chpa_county D
> where lower(D.county) IN ('broward','dade')
> AND D.ffn IN (select distinct C.ffn
> from chpa_insurer C));

AND EXISTS (SELECT d.ffn FROM chpa_county D, chpa_insurer C WHERE a.ffn=d.ffn AND c.ffn = d.ffn AND LOWER(d.county) IN ('broward', 'dade'))

BTW, can you please explain what is the reason of introducing this condition (I mean the additional link to chpa_county from chpa_test_product via ffn)?

HTH
Anton Received on Thu May 07 1998 - 03:48:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US