many best practices for writing and tuning SQL and the best resources for Oracle
SQL include two books with practices and standards for implementing SQL best
practices. BC recommended SQL best practices techniques for Oracle
Applied SQL best Practices
several guidelines for SQL best practices, and some that apply directly to
Oracle SQL. In general, Oracle SQL best practices include these
Practices for SQL Tuning
These are just
a few of the SQL best practices and we also have SQL best practices for
- Remove unnecessary large-table full-table scans -
Unnecessary full-table scans cause a huge amount
of unnecessary I/O and can drag-down an entire database and it's a SQL best
practice to identify unnecessary full scans and remove them by adding
- Cache small-table full-table scans
- Caching is a SQL best practice in cases where a dedicated data buffer is
available for caching table rows.
- Verify optimal index usage
- This is one of the
most important SQL best practices where you examine your SQL and verify that
your SQL is using the most selective.
- Use Materialized Views: Materialize your aggregations and summaries for static
tables - One SQL best practice features of the Oracle
SQLAccess advisor is
recommendations for new indexes and suggestions for materialized views.
- Use subquery factoring and global temporary tables.
Use function-based indexes
Use subquery factoring and global temporary tables
Avoid using NOT IN and HAVING:
Instead use NOT EXISTS •Use MINUS instead of EXISTS
Never nest subqueries – use GTT’s instead
Never use “select *” in SQL: If the DBA changes the columns,
the SQL will fail
Always generate an execution plan for all production SQL
Always test your SQL with production CBO statistics (migrated from
Always place SQL inside PL/SQL packages.
Freeze SQL execution plans (using optimizer stability) when
performing an upgrade.
Only analyze with dbms_stats when you want execution plans to change.
Tune vendor SQL by swapping execution plans
Again, see the books "Oracle
Tuning: The Definitive Reference", and
for complete details on
SQL best practices. Also see: