GENERAL SQL PERFREED USAGE

1. If it is unnecessary to read an entire table and a suitable index is available ensure that it is being used.
a. Typically speaking a concatenated index on last-name and first-name for example is faster than separate indexes on last-name and first-name.
i. Generally the more columns which are specified in a concatenated index make it more selective and thereby tend to improve its performance. For instance you could add dob to the exampled concatenated index.
b. Oracle cannot use an index to search for NULL values; however it can use an index to find values which are NOT NULL.
c. An index which contains the columns in the ORDER BY clause and the columns in the SELECT list will provide pretty good performance for ‘first row’ and ‘all rows’. However, when an index on ORDER BY columns alone is issued, the speed will be pretty good for ‘first row’ however; it will often be slower than a full table scan when retrieving ‘all rows’.

2. When using LIKE command with a wildcard ‘%’, the placement of that wildcard can make a difference in the amount of I/O required to answer the query.
a. If (LIKE ‘TIM%’) or if (LIKE ‘%ME’) with a full table scan there is no difference in the I/O required to satisfy the query.
b. If (LIKE ‘TIM%’) is used with an index it will be much faster than a full table scan.
c. If (LIKE ‘%ME’) is used with an index it will be much faster than that using (LIKE ‘TIM%’) is used with an index.

3. When considering JOIN Performance HASH JOIN’s tend to be faster, then SORT and lastly NESTED LOOPS.

4. A JOIN is usually faster than an IN as a JOIN gives the optimizer more choices for access paths.

5. If applicable the START WITH clause is much faster than a WHERE clause in eliminating rows in a hierarchal query.

6. A sub-query with an EXISTS will beat a sub-query with an IN which will beat an equivalent JOIN.
a. When using an EXISTS with a Correlated Sub-query it is best if the sub-query can be satisfied using an index lookup only.

7. When you use COUNT(*) it performs a full table scan; if the table has an index on a NOT NULL column then you can use COUNT() to reduce the count time substantially on large tables.
a. If a NOT NULL indexed column is not available then it is better to use COUNT(*) rather than COUNT() in regard to speed.

8. You can use an index to resolve a GROUP BY if the index contains the columns in the GROUP BY list, and all the columns are aggregated in the SELECT list.

9. Using WHERE instead of HAVING (where possible of course) reduces I/O requirements by eliminating rows before they are grouped.

10. If you don’t need to get rid of duplicate rows in a UNION operation that you are performing, you should use UNION ALL instead of UNION to avoid sorts.

11. Hash Joins tend to perform much better than INTERSECT’s; therefore you should consider recoding INTERSET’s into HASH JOINs.

12. In the same fashion you should consider recoding a MINUS into an ANTI-JOIN using the HASH_AJ hint.

13. Where applicable (removing all rows) TRUNCATE TABLE is preferred over a DELETE.

14. Remember that as great as indexes can be, they also add overhead to INSERT, DELETE and some UPDATE’s. Therefore avoid over-indexing, especially on columns which are frequently changed.

15. Parallel SQL can be a utilized to achieve great performance gains. The following are types of statements that can be parallelized:
a. SQL queries (especially if they contain large full table scans).
b. Building or the rebuilding of indexes.
c. Creating Tables as SELECT (CTAS).
d. An UPDATE, DELETE or INSERT statement.

16. Trying to accomplish a lot within one complex query tends to be much slower in Oracle than breaking it down via temporary tables that are cleaned up once the result set is achieved.

17. When DELETE’s are required on large tables, if possible use a CTAS to create the table without the rows which would have been deleted, thereby greatly reducing the overall time that would have been incurred by the DELETE.