Re: SQL Statement

Andrew Braun asks:
>
> Re: SQL Statement
>
> Andrew Braun <dare_at_acpub.duke.edu>
> Mon, 11 Dec 1995 22:58:19 -0800
> Duke University, Durham, NC, USA
>
> Newsgroups:
> comp.databases.oracle
> References:
> <4a3c2m$ekb_at_mercury.starnet.gov.sg> <4ahgk6$s1h_at_cisu2.jsc.nasa.gov>
>
> Paul Roberts wrote:
> >
> > 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.)
> I had a very similar problem the other day. I used a union
 instead of
> the or and had a huge performance upgrade. Is there anything wrong
 with this
> procedure.
> Andy

Nothing at all! Any time you can use Set Operators ( like UNION, INTERSECT, MINUS ) to optimize your SQL you should!

Use MINUS, for instance, instead of "NOT IN ( SELECT xxxxxx etc." wherever possible.

Thanks for the reminder!

    _   _  _ _  __    : 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 Tue Dec 12 1995 - 00:00:00 CET