Tips of Oracle SQL Performance Tuning:

Tips of Oracle SQL Performance Tuning

These are the tips that needs to be followed to performance tune your SQL scripts;

·    Never do a calculation on an indexed column (e.g., WHERE salary*5 > :myvalue)

·    Whenever possible, use the UNION statement instead of OR conditions

·    Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS clause

·    Always specify numeric values in numeric form and character values in character form (e.g., WHERE emp_number = 565, WHERE emp_name = ?Jones?)

·    Avoid specifying NULL in an indexed column

·    Avoid the LIKE parameter if = will suffice. Using any Oracle function will invalidate the index, causing a full-table scan

·    Never mix data types in Oracle queries, as it will invalidate the index. If the column is numeric, remember not to use quotes (e.g., salary = 50000). For char index columns, always use single quotes (e.g., name = ?BURLESON?)

·    Remember that Oracle’s rule-based optimizer looks at the order of table names in the FROM clause to determine the driving table. Always make sure that the last table specified in the FROM clause is the table that will return the smallest number of rows. In other words, specify multiple tables with the largest result set table specified first in the FROM clause

·    Avoid using sub-queries when a JOIN will do the job

·    Use the Oracle decode function to minimize the number of times a table has to be selected

·    To turn off an index you do not want to use (only with a cost-based optimizer), concatenate a null string to the index column name (e.g., name||') or add zero to a numeric column name (e.g., salary+0). With the rule-based optimizer, this allows you to manually choose the most selective index to service your query

·    If your query will return more than 20 percent of the rows in the table, use a full-table scan rather than an index scan

·    Always use table aliases when referencing columns
·    Understand the data. Look around table structures and data. Get a feel for the data model and how to navigate it.
·    If a view joins 3 extra tables to retrieve data that you do not need, don't use the view!
·    When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!
·    Avoid multiple layers of view. For example, look for queries based on views that are themselves views. It may be desirable to encapsulate from a development point of view. But from a performance point of view, you loose control and understanding of exactly how much task loading your query will generate for the system.
·    Look for tables/views that add no value to the query. Try to remove table joins by getting the data from another table in the join.