EXPERT RESPONSE
There are several rules for creating "efficient" queries, but not all of them apply to all situations.
- Cache frequently referenced tables and indexes in the KEEP pool.
- Use temporary tables (GTT) to improve SQL performance of complex queries.
- Implement query re-write within an Oracle database (materialized views)
- Use dbms_stats for good execution plans.
- Check to make sure that your query uses the most selective possible index (especially function-based indexes). Check the "where" clause of the query and compare indexes in dba_indexes.
- Avoid subqueries whenever possible, but if you must have them, use IN if most of the filtering conditions are in the subquery; use EXISTS otherwise.
Here is a good list of tips for writing efficient SQL.
|
