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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

SQL Formatting best practice tips

Oracle Tips

Coriolis Publishing
 

Oracle SQL Statement Formatting Best Practices

Also see my general notes on Oracle standards and SQL best practices.

The formatting of SQL statements is one of the few areas in which this standard can be 'the law.? All SQL statements should conform precisely to these standards to the maximum extent possible; even slight deviations can have a performance impact on an Oracle database.

Oracle caches individual SQL statements within its shared global area (SGA). When a new statement is issued, Oracle does a block comparison of the statement against statements in the SGA. If a match is found, Oracle re-executes the stored version of the statement, rather than parsing the new statement and then executing it.

For instance, presume that the SGA contains the following SQL statement:

SELECT name, ssn, grade
FROM   STUDENT_GRADES
WHERE  grade > 90;

Now presume that someone executes this statement:

SELECT name, ssn, grade
 FROM STUDENT_GRADES
 WHERE grade > 90;

The second statement, although functionally identical to the statement already existing in the SGA, would not find a match in the SGA and would be parsed to determine the proper execution plan for the statement, thus increasing the overhead of the statement and slowing the response to the user.

To make matters worse, the first (and the correct) statement might be pushed out of the SGA to make room for the new statement, further impacting the processing of code that does follow the standard. The placement of a single space or shifting the case of a single character will force an SQL statement to be parsed and placed in the SGA instead of finding a matching statement in the SGA, due to the nature of the block comparison algorithm that Oracle uses.

Admittedly, the time required to parse a single statement to determine an execution plan is very small, but with tens or hundreds of users hitting the database at the same time, those milliseconds add up very quickly. Performance improvements are sometimes achieved in one fell swoop, but a lot of performance tuning work is the result of painstakingly wringing small improvements out of a lot of different pieces of code.

A number of examples are listed in the following sections. In each section, one example is labeled Correct and the other examples are labeled Incorrect. The example labeled correct is the only acceptable format for statements of the type, even though the other statements shown (and many variations not shown) are functionally equivalent to the ?correct? statement.

DELETE Statements

Correct:

DELETE
FROM   STUDENTS
WHERE  ssn = 999999999;

Incorrect:

DELETE
  FROM STUDENTS
 WHERE ssn = 999999999;

Incorrect:

DELETE STUDENTS WHERE ssn = 999999999;

INSERT Statements

Correct:

INSERT
INTO   STUDENTS
       (ssn,
       first_name,
       last_name,
       ...
       most_recent_gpa)
VALUES (999999999,
       'Roger',
       'Smith',
       ...
       NULL);

Incorrect:

INSERT
INTO STUDENTS (ssn, first_name, last_name,... most_recent_gpa)
VALUES (999999999, 'Roger', 'Smith',...NULL);

Incorrect:

INSERT INTO STUDENTS
VALUES (999999999, 'Roger', 'Smith', ... NULL);

Incorrect:

INSERT INTO STUDENTS VALUES (999999999, 'Roger', 'Smith',...NULL);

SELECT Statements

Correct:

SELECT last_name, first_name, middle_name, ssn, most_recent_gpa
FROM   STUDENTS
WHERE  ssn = 999999999;

Incorrect:

SELECT last_name, first_name FROM STUDENTS
WHERE
ssn = 999999999;

Incorrect:

SELECT ssn, most_recent_gpa
FROM STUDENTS
WHERE most_recent_gpa < 2.0;

UPDATE Statements

Correct:

UPDATE STUDENTS
SET     apartment_number = 'H',
        street_address   = '16 Northwest Main Street'
WHERE   ssn = 999999999;

Incorrect:

UPDATE STUDENTS
SET apartment_number = 'H',
street_address = '16 Northwest Main Street'
WHERE ssn = 999999999;

Incorrect:

UPDATE STUDENTS
SET    apartment_number = 'H', street_address = '16 Northwest Main
Street'
WHERE ssn = 999999999;

This is an excerpt from "High Performance Oracle Database Automation", by Jonathan Ingram and Donald K. Burleson, Series Editor.
 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.