|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
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.
The use of nvl(column,0) works with all
datatypes, however, it could be confusing in queries which perform
a lot of arithmetic calculation.
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
Have a Oracle Question
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.