SQL Tuning Tips

SQL Tuning Tips

SQL Tuning Tips

Quick tips for SQL tuning tips:

• Do not modify indexed columns with functions such as RTRIM, TO_CHAR, UPPER, TRUNC as this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function (e.g NVL), consider creating a function based index
• Try to use an index if less than 5% of the data needs to be accessed from a data set. The exception is a small table (a few hundred rows) which is usually best accessed through a FULL table scan irrespective of the percentage of data required.
• Use equi-joins whenever possible, they improve SQL efficiency.
• Avoid the following kinds of complex expressions:
  • NVL (col1,-999) = ....
  • TO_DATE(), TO_NUMBER(), and so on

  These expressions prevent the optimizer from assigning valid cardinality or selectivity estimates and can in turn affect the overall plan and the join method.

• Read explain plan and try to make largest restriction (filter) as the driving site for the query, followed by the next largest, this will minimize the time spent on I/O and execution in subsequent phases of the plan.
• Use Table Joins Rather than Multiple Queries

  -- BAD (two separate queries when one would work)
  SELECT name, product_type_id
  FROM products
  WHERE product_id= 1;
  
  --Better
  SELECT name
  FROM product_types
  WHERE product_type_id= 1;
  

• Use CASE Expressions Rather than Multiple Queries Use CASE expressions rather than multiple queries when you need to perform many calculations on the same rows in a table. The following bad example uses multiple queries to count the number of products within various price ranges:

  -- BAD (three separate queries when one CASE statement would work)
  SELECT COUNT(*)
  FROM products
  WHERE price < 13;
  
  COUNT(*)
  ----------
  2
  
  SELECT COUNT(*)
  FROM products
  WHERE price BETWEEN 13 AND 15;
  
  COUNT(*)
  ----------
  5
  
  SELECT COUNT(*)
  FROM products
  WHERE price > 15;
  
  COUNT(*)
  ----------
  5
  Rather than using three queries, you should write one query that uses CASE expressions. This is shown in the following good example:
  -- GOOD (one query with a CASE expression)
  SELECT
  COUNT(CASE WHEN price < 13 THEN 1 ELSE null END) low,
  COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END) med,
  COUNT(CASE WHEN price > 15 THEN 1 ELSE null END) high
  FROM products;
  
  LOW MED HIGH
  ---------- ---------- ----------
  2 5 5
  

• Passing Hints to the Optimizer: You can pass hints to the optimizer. A hint is an optimizer directive that influences the optimizer's choice of execution plan. The correct hint may improve the performance of an SQL statement. You can check the effectiveness of a hint by comparing the cost in the execution plan of an SQL statement with and without the hint. Here's a link to SQL Optimizer Hints.