SQL Coding Standards

SQL Statements 

  • Table/view names used in SQL statements should be the synonym for the table/view.           
  • Avoid using SQL functions (e.g. concatenation, substr, decode, rtrim, ltrim etc.) on indexed columns, as this prevents the use of the index.
  • Do not use SELECT * in your queries. Always write the required column names after the SELECT statement, like SELECT CustomerID, CustomerFirstName, City. This technique results in fewer disks IO and less network traffic and hence betters performance.
  • Always use a column list in your INSERT statements. This helps in avoiding problems when the table structure changes (like adding a column).
  • Always store 4 digit years in dates (especially, when using char or int data type columns), instead of 2 digit years to avoid any confusion and problems.
  • Do not use the column numbers in the ORDER BY clause as it impairs the readability of the SQL statement. Further, changing the order of columns in the SELECT list has no impact on the ORDER BY when the columns are referred by names instead of numbers. Consider the following example, in which the second query is more readable than the first one:

SELECT emp_id, emp_name

FROM mst_employees ORDER BY 2

SELECT emp_id, emp_name

FROM mst_employees ORDER BY emp_name

  • The following constructs should not be normally used in SQL WHERE clauses:        

IS NULL, IS NOT NULL, NOT IN, NOT EXISTS. SQL Functions also should not be normally used in     WHERE clause.

  • When a Nullable field is used in where clause of a query, NVL check should be used as appropriate to the Logic.
  • If the same table is used more than once in a SQL statement then the alias should be like CUSA, CUSB etc.

E.g.,                                                                            

SELECT CUSA.CUST_ID          

            FROM  CUS CUSA, CUS CUSB

                        WHERE EXISTS (SELECT CUSB.CUST_ID 

                        FROM  CUS CUSB

WHERE CUSB.CUST_ID = CUSA.VEND_ID);

/*CORELATED SUBQUERY*/

  • If identical SQL statements are used in different places it is to be ensured that at all the places the statements should be identical. The use of shared SQL depends on the exact text match of SQL statements and has a major impact on performance.
  • In a SQL join statement all columns should be qualified with the table name. 
  • Join via indexed columns only.
  • Smallest table in a join should be specified in the end. For example, if a join statement has three tables X, Y, Z with table sizes increasing in the above order, it should be coded as:

 

SELECT …………..                                                                                                        

FROM       Z, Y, X

WHERE ………….                                                                                                                     

This improves the performance of the join by eliminating many rows quickly.

 

Where clause sequence should be specified so as to make use of indices. For example, with a table containing 3 columns in primary key A, B, C in the above order, the WHERE clause should be written as:                             

 

          SELECT ………….                                                                                                                                  FROM     X                                                                                                                                           WHERE ………….                                                                                                        AND X.C=…..                                                                                                                                                AND X.B=……                                                                                                                                               AND X.A=…….                     

This approach should also be followed if there are secondary indices on the table. This ordering is important as databases like ORACLE parses the statement bottom up. Note that the index will be used in the above example if the WHERE clauses use any of the three combinations of the columns:        

 

X.A, X.B, X.C                                                                     

X.A, X.B                                                                            

X.A                                                                                                            

  • Use joins instead of correlated queries wherever possible. For example, the construct [a] can be replaced by the construct [b].                                                                                 

[a] SELECT ……

                        FROM X

                        WHERE X.A IN (SELECT Y.B FROM Y)

 

[b] SELECT ……

                        FROM X, Y                                                                                                      

                        WHERE X.A=Y.B

Advertisements

About Oracle Technology Blogs
Oracle Application Development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: