Re: SQL Statement

I believe that the SQL compiler works from the bottom of the code up to the top. By putting the B3=... part at the bottom, you are nullifying use of your index. Try reversing the order of the OR clause and see if that helps.

Good luck,

Barry Drummond

In <4a3c2m$ekb_at_mercury.starnet.gov.sg>, wisaac_at_starnet.gov.sg (Issac Wong Tuck Kwong) writes:
>Subject: SQL Statement
>Newsgroups: comp.databases.oracle
>Summary:
>Keywords:
>
>Hi,
>
>I have a sql statement which causes a very slow response. Can someone
>here help to check if the statement can be improved or better structured
>table.
>
>I have 2 tables with the fillowing structures:
>Table A Table B
>------- -------
>A1 PK B1 PK
>A2 B2
>A3 B3
>
>PK - Primary Key
>
>The characteristic of Table B is that for B2 and B3 has a lot of repeated
>values.
>
>Table B has a index on B1,B2,B3.
>
>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?
>
>Thanks in Advance!!
>
>Isaac
>E-mail : wisaac_at_starnet.gov.sg
>
Received on Fri Dec 08 1995 - 00:00:00 CET