Tips for more efficient SQL
Tips for more efficient SQL
Space doesn’t permit me to discuss every detail of Oracle tuning, but I can share some general rules for writing efficient SQL in Oracle regardless of the optimizer that is chosen. These rules may seem simplistic but following them in a diligent manner will generally relieve more than half of the SQL tuning problems that are experienced:
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 subqueries 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, a full-table scan may be better than an index scan.
Always use table aliases when referencing columns.
Space doesn’t permit me to discuss every detail of Oracle tuning, but I can share some general rules for writing efficient SQL in Oracle regardless of the optimizer that is chosen. These rules may seem simplistic but following them in a diligent manner will generally relieve more than half of the SQL tuning problems that are experienced:
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 subqueries 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, a full-table scan may be better than an index scan.
Always use table aliases when referencing columns.
posted by KISS Traders at
7:49 PM
|
0 comments
