The Ten Commandments for Fast Queries

THOU SHALL ACCIDENTLY NOT DISABLE THE USE OF INDEXES  BY MODIFYING A COLUMN WITHIN THE WHERE CLAUSE

THOU  SHALL PUT THE TABLE THAT RETURNS THE  FEWEST ROWS LAST IN THE FROM LIST OF THE QUERY.

THOU WILL INVESTIGATE REWORDING THY QUERY IN SOME  OTHER WAY.

THOU SHALL USE THE EXISTS OPERATOR WHENEVER POSSIBLE.

THOU  SHALL  NOT  ASK THE KERNEL FOR  MORE  THAN  THOU WANTEST.

THOU  SHALL NOT FORCE REPARSING OF A QUERY WHEN ONLY CHANGING BIND VARIABLES

THOU  SHALL KNOW THE DATA VOLUMETRICS AND USE REALISTIC  TEST DATA.

THOU SHALL USE TABLE ALIASES IN THY QUERIES

THOU SHALL NOT USE THE != UNLESS ABSOLUTELY NECESSARY

THOU SHALL USE THE TRACE FACILITY TO MONITOR YOUR QUERIES
 

Intentionally disabling indexes.

When it has been decided, that certain indexes need to be disabled, for query optimisation, the following column modifiers will be used.

DatatypeInhibit expressionCharChar||''NumberNumber+0DateAdd_months(date,0)

The use of  nvl(column,0) works with all datatypes, however, it could be confusing in queries which perform  a  lot  of arithmetic calculation. 
See example below.
               
                                 SELECT    deptno
                                 FROM      dept
                                 WHERE     nvl(deptno,0) = '1234'     
 

Ordering of the FROM clause.

This rule is for when the oracle optimiser is stuck for a good idea.  The Oracle optimiser  works in  the following manner. It looks at each of  the WHERE clauses and assigns the tables concerned a number based on the type of predicate e.g. field = const'  or field(+) = field.  It then chooses the table with the lowest score as the driving  table. But, and its a big one, if more than one table has the same lowest score then it chooses the last table in the FROM list to be the driving table.

In this example there are indexes on the tables as follows

Indexes :
               
                                 unique on oe(id)
                                 unique on oe_link(oe)
                                 unique on oe_link_name(name)
                                 non unique on oe(oe)
               
               
                                 SELECT    count(*) 
                                 FROM      oe_link l,
                                           oe_link_name n,
                                           oe o
                                 WHERE     o.oe = l.oe
                                 AND       l.name = n.name
               
                                 Time 621.21 secs
               
                                 SELECT    count(*) 
                                 FROM      oe o,
                                           oe_link l,
                                           oe_link_name n
                                 WHERE     o.oe = l.oe
                                 AND       l.name = n.name
               
               
                                 Time 197.05 secs
                                 
Notice the difference in query timings.  The only difference in the two queries is the order of the tables in the FROM clause. Therefore the table returning the least number of rows should be last in the FROM list.

See Also
Guidelines For Using The Outer Join Syntax

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.