This is a bunch of rules I cobbled together from the ORA Oracle tuning
book...not sure how accurate I am in compiling this, and I also don't
know what priority to put this in. Would anyone care to help?
Thanks.
ORACLE Rule-Based SQL Tuning Checklist
In the following, the expression:
ic represents an index column
c represents a non-index column
b represents a bind variable, constant, psuedo SQL columns (USER,
SYSDATE, LEVEL, COUNT(*), etc)
- Use SQL syntax standards
- Use bind variables and constants whenever possible
- Use table aliases
- Prefix all column names by their table aliases
- Tables with fewer records should be last
- The intersection table (with the most dependencies) should be last
- Use the most efficient WHERE clauses early
- Indexed comparisons against constants or bind variables are more
efficient than indexed comparisons against other indexed columns
- In order for indexed comparisions to merge and be more efficient,
they must both be equality comparisons. An equality and range
predicate will not merge
- A non-indexed AND based comparison will evaluate bottom to top;
therefore put the most efficient AND clause last
- A non-indexed OR based comparison will evaluate top to bottom;
therefore put the most efficient OR clause first
- When updating or deleting a record, select the ROWID into a bind
variable, and then use that bind variable against ROWID in the
following update or delete query.13. Reduce the number of database
accesses - a cursor with two fetches is more efficient than two
distinct queries; a table can be g
iven multiple aliases within the same query and each alias may be
tested separately in the WHERE clause, allowing a combined query which
is more efficient than either the cursor/fetch method or two distinct
query method
- Use DECODE to avoid having to scan the same rows or join the same
table repetitively.
- Do not use functions, they disable the index
- Use LIKE instead of SUBSTR over indexed columns
- NOT, <>, and != disable the index
- TRUNC disables the index; use ic BETWEEN TRUNC(b) AND TRUNC(b) +
.99999 (use 5 decimals only).
- || disables the index; use an AND clause instead
- Arithmatic functions disable the index; move the calculation away
from the indexed column
- Do not have columns appear on both side of an operator, this will
disable the index and result in a full table scan
- Instead of "WHERE ic = NVL(b, ic)" use "WHERE ic LIKE NVL(b,
'%')"
- Combine simple, unrelated queries by using outer joins to DUAL to
reduce network traffic
- Combine subqueries when possible
- Use a table join instead of an EXISTS subquery if the percentage
of rows returned from the driving table is high
- Use an EXISTS subquery instead of a table join if the percentage
of rows returned from the driving table is low
- Position EXISTS clauses in WHERE clauses first when chained with
an AND and last when chained with an OR
- NOT IN when applied to a subquery causes an internal sort; use NOT
EXISTS instead
- An OR clause on an indexed field forces a full table scan, an IN
or UNION clause may be more efficient, but only over an indexed column
- Use EXISTS in combination with a subquery instead of selecting
DISTINCT columns, it will eliminate the sorting and filtering DISTINCT
imposes
- A UNION forces all rows returned by each portion of the UNION to
be sorted, merged, and filtered for duplicates. A UNION ALL should be
considered instead if possible
32.
--
Flames and trolls cheerfully ignored
Received on Fri Jun 11 1999 - 20:58:12 CDT