Re: SQL Statement

In article <4a3c6h$ekb_at_mercury.starnet.gov.sg> wisaac_at_starnet.gov.sg (Issac Wong Tuck Kwong) writes:

> 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?

Because of the OR clause in the first SQL statement, the optimizer is likely to split it out into something similar to

SELECT .....
WHERE A3 = B1 and B1 = variable_1
UNION
SELECT .....
WHERE A3 = B1 and B3 = variable_2

The first half of this SQL statement is identical to your second SQL statement, so the delta is in the second half. Assuming you do not have an index on A that starts with A3, the second half requires full table scans of both A and B.

If B3 alone is not selective, the following may improve performance:

SELECT A1,A2,A3
FROM A,B
WHERE A3 = B1
AND NOT (B1 != variable_1 not B3 != variable_2);

This may reduce the execution plan down to a full table scan of A plus an index scan of B (no table scan is necessary since you do not need any data stored in B).

Of course an index on A3 would help a lot.

Good luck.

--

 Alvin W. Law ........................................... Oracle Corporation
 Senior Technical Consultant ................ 300 Oracle Parkway, Box 659305
 Applications Design & Architecture ............... Redwood Shores, CA 94065
 Email: alaw_at_oracle.com ...... Voice: +1.415.506.8317 . Fax: +1.415.506.7294