Re: SQL Statement

From: Paul Roberts <proberts_at_isc901.jsc.nasa.gov>
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

Original text of this message