Re: SQL Statement

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 Received on Mon Dec 11 1995 - 00:00:00 CET