Re: SQL Statement

From: Virag Saksena <vsaksena_at_us.oracle.com>
Date: 1995/12/11
Message-ID: <4ag36k$aie_at_inet-nntp-gw-1.us.oracle.com>#1/1

In first case since you do not have an index on B3 you are getting a full table scan on B followed by an indexed scan on A. What you need is an index with B3 as a leading column in addition to the existing index with B1 as a leading column. If the optimizer had both the access paths available to it (B1 and B3) as leading columns on indexes it would have decomposed the query into an UNION clause.

What you need to do is create a single column index on B1 and you should get better performance.

Virag

---

Virag Saksena                                         vsaksena_at_us.oracle.com
Senior Consultant                                     415.506.5087

System Performance Group
Oracle Services

---

In article <4a41qe$mfb_at_mercury.starnet.gov.sg> wisaac_at_starnet.gov.sg (Issac Wong Tuck Kwong) writes:
>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
>
Received on Mon Dec 11 1995 - 00:00:00 CET