SQL Tuning

1. SQL Performance Tuning team recommends using COUNT(1) instead COUNT(*) for SQL query performance optimization.

Do not use:

SELECT COUNT(*) FROM master;

Use:

SELECT COUNT(1) FROM master;

2. If you are using more than one table, make sure to use table aliases.

SELECT COUNT(1) FROM master m, detail d WHERE m.id = d.master_id;

3. It is good practice to use table column names in an SQL query. This way the SQL statements will be more readable, but that is not the main reason. Example: If in INSERTstatement you use SELECT * FROM x and at some point you add a new column in table x, SQL will return an error. The third reason why it is better to use table column names is to reduce network traffic

4. In WHERE statements make sure to compare string with string and number with number, for optimal SQL query performance.

Do not use:

SELECT id, apn, charging_class FROM master WHERE id = '4343';

Use:

SELECT id, apn, charging_class FROM master WHERE id = 4343;

5.  Avoid using complex expressions.

Avoid:

WHERE serial_id = NVL(:a1, serial_id)

WHERE NVL(serial_id,-1) = ( 1, etc...).

6. SQL Performance Tuning recommends using CASE statements. It is more efficient to run a single SQL statement, rather than two separate SQL statements.

Do not use:

SELECT COUNT (1) FROM emp WHERE salary <= 1000;

SELECT COUNT (1) FROM emp WHERE salary BETWEEN 1000 AND 2000;

Use:

SELECT COUNT (CASE WHEN salary <= 1000

                   THEN 1 ELSE null END) count_1,

       COUNT (CASE WHEN salary BETWEEN 1001 AND 2000

                   THEN 1 ELSE null END) count_2

  FROM emp;

7. Use UNION ALL instead of UNION, if possible

Do not use:

SELECT id, name FROM emp_bmw

UNION

SELECT id, name FROM emp_bmw_welt

Use:

SELECT id, name FROM emp_bmw

UNION ALL

SELECT id, name FROM emp_bmw_welt

8. SQL Performance Tuning OR vs. IN.

Our tests showed that using IN in WHERE condition is a little faster then using OR.

Do not use:

SELECT * FROM CDRS_NR WHERE RECORD_TYPE = 'MTC' OR  RECORD_TYPE = 'MOC' OR  RECORD_TYPE = 'SMSO';

Use:

SELECT * FROM CDRS_NR WHERE RECORD_TYPE IN ('MTC', 'MOC', 'SMSO')

9. SQL Performance Tuning recommends to use minimal number of sub queries, if possible.

Do not use:

SELECT id, manufacturer, model

  FROM cars

 WHERE price = ( SELECT MAX(price)

                   FROM cars_bmw

               )

   AND year =  ( SELECT MAX(year)

                   FROM cars_bmw

               )

Use:

SELECT id, manufacturer, model

  FROM cars

 WHERE (price, year) = ( SELECT MAX(price), MAX(year)

                           FROM cars_bmw

                       )

10. Using the indexes carefully 

Having indexes on columns is the most common method of enhancing performance, but having too many of them may degrade the performance as well. So, it's very critical to decide wisely about which all columns of a table we should create indexes on. Few common guidelines are:- creating indexes on the columns which are frequently used either in WHERE clause or to join tables, avoid creating indexes on columns which are used only by functions or operators, avoid creating indexes on the columns which are required to changed quite frequently, etc.

11. Using WHERE instead of HAVING 

usage of WHERE clause may take advantage of the index defined on the column(s) used in the WHERE clause.

12. Indexed Scan vs Full Table Scan 

Indexed scan is faster only if we are selecting only a few rows of a table otherwise full table scan should be preferred. It's estimated that an indexed scan is slower than a full table scan if the SQL statement is selecting more than 15% of the rows of the table. So, in all such cases use the SQL hints to force full table scan and suppress the use of pre-defined indexes. Okay... any guesses why full table scan is faster when a large percentage of rows are accessed? Because an indexed scan causes multiple reads per row accessed whereas a full table scan can read all rows contained in a block in a single logical read operation.

13. Using ORDER BY for an indexed scan 

the optimizer uses the indexed scan if the column specified in the ORDER BY clause has an index defined on it. It'll use indexed scan even if the WHERE doesn't contain that column (or even if the WHERE clause itself is missing). So, analyze if you really want an indexed scan or a full table scan and if the latter is preferred in a particular scenario then use 'FULL' SQL hint to force the full table scan.

14. Using ROWID and ROWNUM wherever possible

These special columns can be used to improve the performance of many SQL queries. The ROWID search is the fastest for Oracle database and this luxury must be enjoyed wherever possible. ROWNUM comes really handy in the cases where we want to limit the number of rows returned.

15. Optimizing the WHERE clause 

There are many cases where index access path of a column of the WHERE clause is not used even if the index on that column has already been created. Avoid such cases to make best use of the indexes, which will ultimately improve the performance. Some of these cases are: COLUMN_NAME IS NOT NULL (ROWID for a null is not stored by an index), COLUMN_NAME NOT IN (value1, value2, value3, ...), COLUMN_NAME != expression, COLUMN_NAME LIKE'%pattern' (whereas COLUMN_NAME LIKE 'pattern%' uses the index access path), etc. Usage of expressions or functions on indexed columns will prevent the index access path to be used. So, use them wisely!

16. Using Bind Variables, Stored Procs, and Packages 

Using identical SQL statements (of course wherever applicable) will greatly improve the performance as the parsing step will get eliminated in such cases. So, we should use bind variables, stored procedures, and packages wherever possible to re-use the same parsed SQL statements.

Full Table Scans can be good when:

Used on a small table (< 500 rows)

Used in a query returning more than a few percent of the rows in a table

Used in a Sort-Merge or Hash join, but only when these join methods are intended.

Full Table Scans are bad when:

Used on a large table where indexed or hash cluster access is preferred.

Used on a medium-large table (> 500 rows) as the outer table of a Nested Loop join.

Used on a medium-large table (> 500 rows) in a Nested Sub-Query.

Used in a Sort-Merge or Hash join when a Nested Loop join is preferred.