Re: SQL Statement
Date: 1995/12/11
Message-ID: <4ahgk6$s1h_at_cisu2.jsc.nasa.gov>#1/1
Issac Wong Tuck Kwong asks:
>
> SQL Statement
>
> wisaac_at_starnet.gov.sg (Issac Wong Tuck Kwong)
> 6 Dec 1995 06:09:26 GMT
>
> Newsgroups:
> comp.databases.oracle
>
> 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
Issac,
Try adding a non-unique index to B3 (by itself). That should
speed up your first query.
BWT, you probably want to know why the thing works the way it
does. Well, in Oracle a Primary Key is implimented using a Unique
Index, so you already have an index on B1 alone. Your second query
uses that index for the join. Creating an index on B3 should give
_almost_ as much of an improvement. (But not quite as much because
of the OR.)
_ _ _ _ __ : Paul Roberts
|_ |_| | | : Programmer / Analyst
_| | | _|_ |__ : proberts_at_isc901.jsc.nasa.gov
Science Applications International Corporation
(An employee-owned company)
The opinions expressed are my own, and not necessarily the views of SAIC, NASA, or the U.S. government. Received on Mon Dec 11 1995 - 00:00:00 CET