Re: Speeding up ORACLE queries .. HELP

From: Johan Streun <jstreun_at_nl.oracle.com>
Date: 15 Aug 1994 10:32:34 GMT
Message-ID: <32ng82$jid_at_nlsu110.nl.oracle.com>

Nicholas Kitchener (nkitchen_at_axion.bt.co.uk) wrote:

: As a student just in placement, I have a project to complete.
:
: My problem is to do with SQLplus (ORACLE 7) at speeding up SQL queries,
: the general jist of the set of SQL queries is
 

: WHERE ch_uid IN (SELECT ch_uid FROM pib_CR)  

: I have created indices for the three tables I have created by :
 

: CREATE INDEX pib_icr ON pib_CR (ch_uid)  

: The main point of my question is there a method for speeding up big multi
: table SQL queries ??
 

: If you have any thoughts Email me..
 

: Thanks in advance,
 

: Nick.

As a general rule of thumb you should avoid any

                     SELECT ... FROM A WHERE A.X IN (SELECT B.X from B)

type of queries.
These are slow because for every row in the A table the query for the B table is performed. Usually this type of queries can be rewritten to something like:
SELECT ... FROM TABLE1 a WHERE EXISTS
  (SELECT b.X FROM TABLE2 b WHERE
   b.X=a.X)
This is faster because the 'inner' query can stop as soon as one row is found that matches the WHERE clause.

Ofcourse creating approperiate indexes also helps (a lot)

Hope this helps,

Johan van Streun

---

|Any opinions expressed are just mine. I do not represent Oracle         |
|Corporation in this posting                                             |
--------------------------------------------------------------------------

Received on Mon Aug 15 1994 - 12:32:34 CEST