Re: SQL Statement

In article <4a41qe$mfb_at_mercury.starnet.gov.sg>, wisaac_at_starnet.gov.sg (Issac Wong Tuck Kwong) wrote:

> Hi,
>
> I have a sql statement which causes a very slow response. Can someone

<...>

> The problem SQL statement is as follows:
>
> Select A1,A2,B1 from A,B
> where A3 = B1 and
> (B1 = variable_1 or
> B3 = variable_2);
>
> However the performance is improved tremendously if the SQL statement is
> reconstructed in the following manner:
>
> Select A1,A2,B1 from A,B
> where A3 = B1 and
> (B1 = variable_1);
>
> Can someone suggest any possible way to improve the 1st mentioned SQL
> statement?

sure, try this:

Select A1,A2,B1 from A,B
where A3 = B1 and

     B1 in (variable_1,variable_2);

I'm new to Oracle, but in every relational DB implementation I've used so far, the IN clause will use the index on that column, while the OR in the above example immediately eliminates the possibility of using the index on B1.

Good luck!

Larry

>
> Thanks in Advance!!
>
> Isaac
> E-mail : wisaac_at_starnet.gov.sg

• Larry E. Snyder * *
• Monsanto Company * CLEVER QUOTE OR PHRASE GOES HERE *
• St. Louis, MO * lesnyd_at_monsanto.com *

  ---