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.
