34 Proven Oracle SQL Tricks to Boost Query Performance

Table and join ordering

Oracle parses the FROM clause from right to left. The table listed last (the driving table) is processed first. Choose the table with the smallest row count as the driving table, or use an intersection table when more than three tables are joined.

WHERE‑clause predicate ordering

Oracle evaluates WHERE conditions from bottom to top. Place join predicates before other filters and put the most selective predicates (those that eliminate the most rows) at the end of the clause.

Avoid * in SELECT

When * is used, Oracle expands it by consulting the data dictionary, which adds parsing overhead. List only the required columns explicitly.

Reduce round‑trips

Each execution incurs parsing, index‑usage estimation, bind‑variable processing and block reads. Consolidate related queries into a single statement whenever possible to minimise these repeated operations.

Increase ARRAYSIZE in client tools

In SQL*Plus, SQL*Forms and Pro*C set ARRAYSIZE to about 200 so that more rows are fetched per network round‑trip, improving bulk retrieval performance.

Use DECODE to avoid repeated scans

DECODE

can replace repetitive scans of the same rows or joins, reducing unnecessary work.

Combine independent queries

If several simple, unrelated SELECT statements exist, merge them into a single query (for example with UNION ALL) to cut the number of executions.

Delete duplicate rows efficiently

DELETE FROM EMP E
WHERE E.ROWID > (
  SELECT MIN(X.ROWID)
  FROM EMP X
  WHERE X.EMP_NO = E.EMP_NO
);

Prefer TRUNCATE for whole‑table removal

TRUNCATE

is a DDL operation that does not generate rollback information, making it much faster and less resource‑intensive than DELETE when the entire table is to be cleared.

Commit frequently

Issuing COMMIT releases rollback segments, locks and redo‑log buffer space, which can improve overall application throughput.

Replace HAVING with WHERE when possible

Filtering rows with WHERE occurs before aggregation, avoiding the extra sorting and grouping work required by HAVING.

Minimise sub‑query table accesses

SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER) = (
  SELECT TAB_NAME, DB_VER
  FROM TAB_COLUMNS
  WHERE VERSION = 604
);

Leverage built‑in functions

Functions such as NVL, DECODE, TO_NUMBER etc. are executed inside the engine and are usually faster than equivalent procedural logic.

Use table aliases

Assign short aliases to tables and prefix column references with the alias. This reduces parsing time and eliminates ambiguous column errors.

Prefer EXISTS / NOT EXISTS over IN / NOT IN

EXISTS

stops scanning as soon as a matching row is found, while IN often forces a full sort of the sub‑query result set.

SELECT * FROM EMP E
WHERE EMPNO > 0
  AND EXISTS (
    SELECT 1 FROM DEPT D
    WHERE D.DEPTNO = E.DEPTNO
      AND D.LOC = 'MELB'
  );

Identify low‑efficiency SQL with V$SQLAREA

SELECT EXECUTIONS,
       DISK_READS,
       BUFFER_GETS,
       ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) AS HIT_RATIO,
       ROUND(DISK_READS/EXECUTIONS,2) AS READS_PER_RUN,
       SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS > 0
  AND BUFFER_GETS > 0
  AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY READS_PER_RUN DESC;

Index maintenance

Indexes accelerate lookups but incur storage and maintenance costs. Rebuild a fragmented index with:

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>;

Replace DISTINCT with EXISTS for one‑to‑many queries

SELECT DEPT_NO, DEPT_NAME
FROM DEPT D
WHERE EXISTS (
  SELECT 1 FROM EMP E
  WHERE E.DEPT_NO = D.DEPT_NO
);

Write SQL keywords in uppercase

Oracle parses case‑insensitively, but converting the statement to uppercase first can marginally speed parsing.

Avoid string concatenation with ‘+’ in Java

Use StringBuilder or prepared statements to reduce overhead.

Do not apply NOT to indexed columns

Using NOT disables index usage and forces a full table scan.

Avoid functions on indexed columns in WHERE

Expressions such as SAL*12 > 25000 prevent index use. Rewrite as SAL > 25000/12.

Prefer >= over > when possible

>=

allows the optimizer to jump directly to the first qualifying row.

Replace OR with UNION (or UNION ALL ) on indexed columns

SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION ALL
SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE REGION = 'MELBOURNE';

Replace multiple OR conditions on the same column with IN

SELECT * FROM LOCATION
WHERE LOC_ID IN (10,20,30);

Avoid IS NULL / IS NOT NULL on indexed columns

Null checks prevent index usage. Use a non‑null predicate such as DEPT_CODE >= 0 instead.

Always reference the leading column of a composite index

If the first column of a multi‑column index is not present in the WHERE clause, the optimizer will ignore the index.

Prefer UNION ALL over UNION when duplicate elimination is not required

UNION ALL

skips the costly sort‑and‑dedup step, yielding faster results.

Use WHERE instead of ORDER BY when ordering can be expressed as a range predicate

If the required order matches the natural order of an indexed column, a WHERE range predicate can replace ORDER BY and avoid a separate sort.

Avoid implicit type conversion on indexed columns

Comparing a numeric column to a string forces Oracle to convert the column, disabling the index. Convert the literal explicitly, e.g., WHERE EMPNO = TO_NUMBER('123').

Beware of non‑selective predicates

Operators such as !=, concatenation ( ||), arithmetic ( +) or comparing one indexed column to another indexed column often cause full scans.

Indexes are less beneficial when they return more than ~30 % of rows

For large result sets a full table scan may be faster than using an index.

Avoid resource‑intensive set operators

Statements containing DISTINCT, UNION, MINUS, INTERSECT or ORDER BY trigger additional sorting phases. Rewrite them when possible.

Optimize GROUP BY by filtering early

Move selective predicates to the WHERE clause before grouping to reduce the number of rows that must be aggregated.