Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








SQL best practices

Oracle design pattern tips by Burleson Consulting

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:
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational