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.
- Materialize your aggregations and summaries for static
tables - One SQL best practice features of the Oracle 10g
SQLAccess advisor is
recommendations for new indexes and suggestions for materialized views.
Again, see the books "Oracle
Tuning: The Definitive Reference", and
"SQL
design patterns" for complete details on
SQL best practices. Also see:
