Oracle tuning checklist...newbie effort, would like a critique

From: Bill Haverberg <haverber_at_visi.com>
Date: Sat, 12 Jun 1999 01:58:12 GMT
Message-ID: <3761be2f.1465410_at_news.visi.com>

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)

1. Use SQL syntax standards
2. Use bind variables and constants whenever possible
3. Use table aliases
4. Prefix all column names by their table aliases
5. Tables with fewer records should be last
6. The intersection table (with the most dependencies) should be last
7. Use the most efficient WHERE clauses early
8. Indexed comparisons against constants or bind variables are more efficient than indexed comparisons against other indexed columns
9. 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
10. A non-indexed AND based comparison will evaluate bottom to top; therefore put the most efficient AND clause last
11. A non-indexed OR based comparison will evaluate top to bottom; therefore put the most efficient OR clause first
12. 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
13. Use DECODE to avoid having to scan the same rows or join the same table repetitively.
14. Do not use functions, they disable the index
15. Use LIKE instead of SUBSTR over indexed columns
16. NOT, <>, and != disable the index
17. TRUNC disables the index; use ic BETWEEN TRUNC(b) AND TRUNC(b) + .99999 (use 5 decimals only).
18. || disables the index; use an AND clause instead
19. Arithmatic functions disable the index; move the calculation away from the indexed column
20. Do not have columns appear on both side of an operator, this will disable the index and result in a full table scan
21. Instead of "WHERE ic = NVL(b, ic)" use "WHERE ic LIKE NVL(b, '%')"
22. Combine simple, unrelated queries by using outer joins to DUAL to reduce network traffic
23. Combine subqueries when possible
24. Use a table join instead of an EXISTS subquery if the percentage of rows returned from the driving table is high
25. Use an EXISTS subquery instead of a table join if the percentage of rows returned from the driving table is low
26. Position EXISTS clauses in WHERE clauses first when chained with an AND and last when chained with an OR
27. NOT IN when applied to a subquery causes an internal sort; use NOT EXISTS instead
28. 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
29. Use EXISTS in combination with a subquery instead of selecting DISTINCT columns, it will eliminate the sorting and filtering DISTINCT imposes
30. 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 Sat Jun 12 1999 - 03:58:12 CEST