Efficient SQL Statements

• Check Your Stats
• Why Indexes Aren't Used
• Caching Tables
• EXISTS vs. IN
• Presence Checking
• Inequalities
• When Things Look Bad!
• Driving Tables (RBO Only)
• Improving Parse Speed
• Packages Procedures and Functions
• Further Reading

Check Your Stats

• Cost Based Optimizer (CBO) and Database Statistics
• Refreshing Stale Statistics
• Automatic Optimizer Statistics Collection

Why Indexes Aren't Used

• The optimizer decides it would be more efficient not to use the index. As a rough rule of thumb, on evenly distributed data an index will be used if it restricts the number rows returned to 5% or less of the total number of rows. In the case of randomly distributed data, an index will be used if it restricts the number of rows returned to 25% or less of the total number of rows.
• You perform a function on the indexed column i.e. WHERE UPPER(name) = 'JONES'
• You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001
• You concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
• You do not include the first column of a concatenated index in the WHERE clause of your statement. For the index to be used in a partial match, the first column (leading-edge) must be used. Note. Index-Skip-Scanning in Oracle9i and above allow indexes to be used even when the leading edge is not referenced.
• The use of 'OR' statements confuses the Cost Based Optimizer (CBO). It will rarely choose to use an index on column referenced using an OR statement. It will even ignore optimizer hints in this situation. The only way of guaranteeing the use of indexes in these situations is to use the /*+ RULE */ hint.

EXISTS vs. IN

PRODUCT - 1000 rows
ITEMS - 1000 rows

(A)
SELECT p.product_id
FROM   products p
WHERE  p.item_no IN (SELECT i.item_no 
                     FROM   items i);

(B)
SELECT p.product_id
FROM   products p
WHERE  EXISTS (SELECT '1' 
               FROM   items i 
               WHERE  i.item_no = p.item_no)

• If the majority of the filtering criteria are in the subquery then the IN variation may be more performant.
• If the majority of the filtering criteria are in the top query then the EXISTS variation may be more performant.

Presence Checking

SELECT Count(*)
INTO   v_count
FROM   items
WHERE  item_size = 'SMALL';

IF v_count = 0 THEN
  -- Do processing related to no small items present
END IF;

SELECT Count(*)
INTO v_count
FROM items
WHERE item_size = 'SMALL'
AND rownum = 1;

IF v_count = 0 THEN
  -- Do processing related to no small items present
END IF;

BEGIN
  SELECT '1'
  INTO   v_dummy
  FROM   items
  WHERE  item_size = 'SMALL'
  AND    rownum = 1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Do processing related to no small items present
END;

Inequalities

When Things Look Bad!

• Write sensible queries in the first place!
• Identify the specific statement(s) that are causing a problem. The simplest way to do this usually involves running the individual statements using SQLPlus and timing them (SET TIMING ON)
• Use EXPLAIN to look at the execution plan of the statement. Look for any full table accesses that look dubious. Remember, a full table scan of a small table is often more efficient than access by rowid.
• Check to see if there are any indexes that may help performance. A quick way to do this is to run the statement using the Rule Based Optimizer (RBO) (SELECT /*+ RULE */ ). Under the RBO, if an index is present it will be used. The resultant execution plan may give you some ideas as to what indexes to play around with. You can then remove the RULE hint and replace it by the specific index hints you require. This way, the CBO will still be used for table accesses where hints aren't present. Remember, if data volumes change over time, the hint that helped may become a hindrance! For this reason, hints should be avoided if possible, especially the /*+ RULE */ hint.
• Try adding new indexes to the system to reduce excessive full table scans. Typically, foreign key columns should be indexed as these are regularly used in join conditions. On occasion it may be necessary to add composite (concatenated) indexes that will only aid individual queries. Remember, excessive indexing can reduce INSERT, UPDATE and DELETE performance.

Driving Tables (RBO Only)

FROM  d, c, b, a
WHERE a.join_column = 12345
AND   a.join_column = b.join_column
AND   b.join_column = c.join_column
AND   c.join_column = d.join_column;

FROM  d, c, b, a
WHERE a.join_column = 12345
AND   a.join_column = b.join_column
AND   b.join_column = c.join_column
AND   c.join_column = d.join_column
AND   d.name = 'JONES';

FROM  c, b, a, d
WHERE d.name = 'JONES'
AND   d.join_column = 12345
AND   d.join_column = a.join_column
AND   a.join_column = b.join_column
AND   b.join_column = c.join_column

Caching Tables

Improving Parse Speed

• If an alias is not present, the engine must resolve which tables own the specified columns.
• A short alias is parsed more quickly than a long table name or alias. If possible, reduce the alias to a single letter.

SELECT first_name,
       last_name,
       country
FROM   employee,
       countries
WHERE  country_id = id
AND    lastname   = 'HALL';

SELECT e.first_name,
       e.last_name,
       c.country
FROM   employee e,
       countries c
WHERE  e.country_id = c.id
AND    e.last_name  = 'HALL';

Packages Procedures and Functions

Further Reading

• Oracle Server SQL Reference (Reference Manual)
• Oracle DBA Handbook (Oracle Press)
• Oracle Advanced Tuning and Administration (Oracle Press)
• Oracle PL/SQL Programming (Oracle Press)