SQL is a very expressive language and there are normally several ways of performing the same query.  Developers should investigate different wordings of the same query, so as to identify the optimal query. This can be demonstrated by the following example, which is about finding all the people who don't have jobs to do. There 
are 99 people in  the system and 9900 jobs.

There are indexes on name on both tables. 

The first attempt:

               SELECT    p.name
                                 FROM      people p,
                                           job j
                                 WHERE     p.name = j.name(+)
                                 AND       j.name is null

                                 fred the 27 

                                 1 record selected.

                                 Time 51.40 secs.

                                 Not a very good result this time. 

The second attempt:

                                 SELECT    name 
                                 FROM      people
                                 WHERE     name not in 
                                           (SELECT   name 
                                            FROM     job)

                                 fred the 27 

                                 1 record selected.

                                 Time 6.11 secs

A  much  better attempt, there is  nearly  an order  of magnitude improvement. We might  now  be inclined to rest on our laurels, make a cup of tea and put our feet up, however, if we correlate  the sub query to get.

SELECT    name 
                                 FROM      people p
                                 where     not name in 
                                           (SELECT   name 
                                           FROM      job j
                                              WHERE     p.name = j.name)
fred the 27
 

1 record selected.
Time 1.08 secs.
 

Use of the Exists operator

One operator which seems to be ignored is the EXISTS  operator. 

This can be particularly useful in forms work for validating foreign keys. In the following example we wish to know whether 'fred  the 45' has any jobs. 

The first example is:

SELECT    distinct 'x'
                                 FROM      job
                                 WHERE     name = 'fred the 45'

                             1 record selected.

Time  0.45 secs.

The second example uses the exists operator and is almost twice as fast.

                                 SELECT    'x'
                                 FROM      dual 
                                 WHERE     exists 
                                           ( SELECT  'x' 
                                           FROM      job
                                           where     name = 'fred the 45')
1 record selected.

Time 0.26 secs.

The  reason this is faster is that  with  the exists operator the oracle kernel knows that once it has found one match it can stop. 
It therefore doesn't have to continue the FTS (a  TLA  (Three Letter Acronym) for Full Table Scan).

Things start becoming interesting,  now the actual value of the data item being searched on determines which query to use. Consider the people table with 10,000 entries. 

Enquiring about 'fred the 34' and 'fred the 9999' gives the following.

                                 SELECT    distinct 'x'
                                 FROM      job
                                 WHERE     name ='fred the 34'

                                 Time 6.65 secs.

                                 SELECT    'x'
                                 FROM      dual 
                                 WHERE     exists 
                                           (SELECT   'x' 
                                           FROM      job
                                           WHERE     name = 'fred the 34')

                                 Time 0.28 secs.

                                 SELECT    'x'
                                 FROM      dual 
                                 WHERE     exists 
                                           (SELECT 'x' 
                                           FROM job
                                           WHERE name = 'fred the 9999')

                                 Time 8.28 secs.

Ok, I cheated somewhat the data goes into the table in name order. Thus 'fred the 1' goes in first data block and 'fred the 9999' goes in last one.

Developers should be aware that, the efficiency of EXISTS and IN is dependant on the amount of data in each table. A query with IN  in it drives from the subquery accessing the main query for each row returned, when, a query uses EXISTS it drives from the main query accessing the subquery for each row returned. So if the subquery returns few rows, but, the main query  returns a lot of rows for each row from the subquery use the IN operator.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips