- Optimize access structures:
- Database design and normalization.
- Tables: heap or index-organized tables, and table or indexed clusters.
- Materialized views.
- Partitioning schemes.
- Statistics, including a comprehensive refresh strategy.
- Rewrite SQL statements:
- Exclude projections that are not required.
- Minimize the amount of work done more than once.
- Factor subqueries that are used multiple times in the same statement.
INbecause the former stops processing once it has found a match.
DECODEto avoid having to scan the same rows over and over again, especially for aggregation functions that act on different subsets of the same data.
- Use analytic functions to do multiple or moving/rolling aggregations with a single pass through the data.
- Avoid scalar subqueries in the
- Use joins instead of subqueries, as it gives the optimizer more room to play around in.
- Say what you mean and pick the right join: if you only need an inner join don’t write an outer join.
- Add logically superfluous predicates that may still aid in the search for an optimal execution plan, particularly for outer joins.
- Avoid implicit conversions of data types, especially in the
WHEREclause predicates with a close eye on the indexes available, including the leading edge of a composite index.
- Avoid, whenever possible, comparison operators such as
NOT EXISTS, and
LIKEwithout a leading
'%'for indexed columns in predicates.
- Do not apply functions on indexed columns in the
WHEREclause when there is no corresponding function-based index.
- Don’t abuse
HAVINGto filter rows before aggregating.
- Avoid unnecessary sorts, including when
UNION ALLrather than
DISTINCTunless you have to use it.
- Use PL/SQL, especially packages with stored procedures (and bind variables) and shared cursors to provide a clean interface through which all data requests are handled.
- Add hints once you have determined that it is right and necessary to do so.