There are
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
includes:
Applied SQL best Practices
There are
several guidelines for SQL best practices, and some that apply directly to
Oracle SQL. In general, Oracle SQL best practices include these
techniques:
SQL Best
Practices for SQL Tuning
These are just
a few of the SQL best practices and we also have SQL best practices for
SQL tuning.
- 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
indexes.
- 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
PROD).
-
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
"SQL
design patterns"
for complete details on
SQL best practices. Also see:
