SQL Tips and Tricks #1

1) Resource intensive operations
Queries which  use DISTINCT,  UNION,  MINUS, INTERSECT,  ORDER  BY  and GROUP BY call upon  the kernel  to  perform resource intensive sorts.   A  DISTINCT   requires  one  sort,  the   other   set operators  require at least two sorts. Other  ways of  writing these queries should be  found.  Most queries  that use the set  operators,  UNION,MINUS and INTERSECT, can be rewritten in other ways.

2) Use realistic test data.

Realistic  test data, which matches  both  in volume  and values, that shown in the ER  diagrams will be used.

The following is based on the example used in Investigate and Rewriting the Query about  rewording  queries. There are  now  999 people  in the table and 9990 jobs.  Thus  we  have  increased our people by a factor of ten. Notice how the query performed with an  outer join  is much quicker than that performed  by  the subquery.  This  is  a complete  reversal  of  the result in Investigate and Rewriting the Query.

This demonstrates quite clearly that the  sql developer  must  have a  knowledge  of  both  the structure  of the data and the typical number  of  rows in each table.

SELECT    p.name
FROM      people p, job j
WHERE      p.name = j.name(+)
AND        j.name is null
fred the 218 
Time 23.20 secs.
SELECT    name 
                                 FROM      people
                                 WHERE     not name in 
                                           (SELECT name 
                                           FROM job)
                                 fred the 218 
                                 Time 193.46 secs.
SELECT    name 
FROM      people p
WHERE     not name in 
(SELECT   name 
FROM      job j
                                           WHERE     p.name = j.name)
fred the 218 
Time  8.66 secs.

3) Use of != operator
Unless it is absolutely necessary avoid using  the != operator. 
The use of this operator disables the  use  of indexes, because 
 the  Oracle  kernel assumes that the query will be retrieving most of  the rows in the table.

4) Use Oracle's trace facility.

For every query, even the simplest, check the output  from  oracle's trace facility.  The  first step in optimising queries is to eliminate FTS's, this can be done using trace. See Explain Facility for details on the trace facility.

5) The 10,15,20 percent rule

Only  use  indexes for retrieving  data  from tables,  where your want less than 15  percent  of the rows. FTS's are quicker if you want more  than 15 percent of a tables data. The rule is called the 10,15,20 percent rule, because  it depends on which oracle  document  you  read, as to whether its 10,15 or 20 percent.

6) Insert statements.
Use of * in insert statements

Although  the use of the shorthand  character  '*'  is allowed by the oracle kernel,  it is considered  to be bad programming practice. It  is  not allowed in  the WMS project. The reason for it being bad practice, is that  if  a table is modified during an upgrade  to  the database, subsequent inserts could fail out in the field.

Oracle Database

See Also

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

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.