Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Query Question (Performance) CORRECTION

Re: SQL Query Question (Performance) CORRECTION

From: Tad Glines <tadg_at_metrolink.net>
Date: 1997/07/05
Message-ID: <33BE7118.C6A52C38@metrolink.net>#1/1

Seán Hanly wrote:

> > (CORRECTION TO THE SECOND QUERY, CUT AND PASTE ERROR]
> >
> > Given a table structure, (with sample data),
> >
> > INVOICE
> > =======
> > ID COL1 COL2
> > 1 aaaa aaaa
> > 2 bbbb bbbb
> > 2 cccc cccc
> >
> > JOIN_TABLE
> > ==========
> > CUST_ID INVOICE_ID
> > 1 1
> > 1 2
> > 1 3
> >
> > I was wondering if somebody could tell me which is more efficent, or
 a
> > possible 3rd
> > more efficent query, of the below:
> >
> > SELECT
> > INVOICE.OID,
> > INVOICE.COL1,
> > INVOICE.COL2,
> > INVOICE.COL3
> > FROM INVOICE WHERE INVOICE.OID IN
> > (SELECT
> > JOIN_TABLE.INVOICE_ID
> > FROM JOIN_TABLE
> > WHERE
> > JOIN_TABLE.CUST_ID = '1');
> >
> >
> > SELECT
> > DISTINCT
> > INVOICE.OID,
> > INVOICE.COL1,
> > INVOICE.COL2,
> > INVOICE.COL3
> > FROM INVOICE, JOIN_TABLE
> > WHERE
> > JOIN_TABLE.CUST_ID='1'
> > AND
> > JOIN_TABLE.INVOICE_ID = INVOICE.OID;
> >
> > Thanks in advace
> >
> > ....Seán
> >

 If you are using Version 7.3.X, and you have an index on OID for INVOICE and an index on CUST_ID for JOIN_TABLE, then the server will convert the first form into the second form minus the 'DISTINCT'. I recomend the
second form since this is what the server will do anyway. If you are looking for the best performance then you need must have the two indexes. Without the indexes, the server will resort to a full table scan on both tables. Also, if the OID's in INVOICE are unique then you don't need the DISTINCT clause. Received on Sat Jul 05 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US