Top 38 Oracle SQL Performance Tips for Faster Queries

Oracle SQL Performance‑Tuning Tips

The following guidelines focus on concrete techniques that improve the execution efficiency of Oracle SQL statements. They are applicable to rule‑based and cost‑based optimizers alike.

1. Choose an optimal driving table

Oracle parses the FROM clause from right to left; the last table is processed first. Select the table with the smallest row count, or an intersection table that is referenced by the others, as the driving table.

2. Order join predicates before other filters

Place table‑join conditions at the beginning of the WHERE clause and put the most selective non‑join predicates at the end so that they are applied after the joins.

3. List columns explicitly

Avoid SELECT *. Expanding * requires a dictionary lookup for every column and adds parsing overhead. Specify only the columns you need.

4. Reduce round‑trips

Combine related queries into a single statement whenever possible. Each round‑trip incurs parsing, index estimation, bind‑variable processing, and block reads.

5. Increase client fetch size

In tools such as SQL*Plus, SQL*Forms, or Pro*C set ARRAYSIZE to about 200 to fetch more rows per network round‑trip.

6. Use DECODE to avoid repeated scans

When the same row or table is accessed repeatedly, a DECODE expression can replace multiple scans.

7. Consolidate independent SELECTs

Merge simple, unrelated queries into a single statement (e.g., using UNION ALL) to reduce parsing and execution overhead.

8. 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);

9. Prefer TRUNCATE for full‑table removal

TRUNCATE

bypasses rollback segments, making it faster than DELETE. It is DDL, not DML, and cannot be rolled back.

10. Commit frequently when appropriate

Regular COMMIT releases rollback segment space, locks, redo‑log buffer space, and internal Oracle overhead, improving overall throughput.

11. Replace HAVING with WHERE when possible

HAVING

filters after aggregation and may require sorting. Moving the predicate to WHERE reduces the amount of data that must be aggregated.

12. Rewrite sub‑queries as joins or EXISTS

Sub‑queries that cause separate table scans should be rewritten to use joins or EXISTS clauses, which allow the optimizer to apply indexes more effectively.

13. Leverage built‑in functions

Use Oracle’s internal functions (e.g., NVL, DECODE, TRUNC) so the optimizer can generate better execution plans.

14. Use table aliases

Prefix column references with short aliases to reduce parsing time and avoid ambiguous column errors.

15. Prefer EXISTS / NOT EXISTS over IN / NOT IN

SELECT * FROM emp e
WHERE e.empno > 0
  AND EXISTS (SELECT 1 FROM dept d
              WHERE d.deptno = e.deptno
                AND d.loc = 'MELB');

EXISTS

stops scanning as soon as a matching row is found; NOT IN forces a full scan and sorting.

16. Identify inefficient SQL via 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 hit_ratio DESC;

17. Maintain indexes wisely

Indexes accelerate lookups but consume storage and incur maintenance cost. Rebuild fragmented indexes:

ALTER INDEX idx_name REBUILD tablespace_name;

18. 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);

19. Use >= instead of > when it enables an index jump

SELECT * FROM emp WHERE deptno >= 4;

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

SELECT loc_id, loc_desc, region FROM location WHERE loc_id = 10
UNION
SELECT loc_id, loc_desc, region FROM location WHERE region = 'MELBOURNE';

21. Replace OR with IN when possible

SELECT * FROM location WHERE loc_id IN (10, 20, 30);

22. Avoid IS NULL / IS NOT NULL on indexed columns

Null values are not stored in B‑tree indexes; such predicates force full table scans.

23. Do not apply functions to indexed columns

Rewrite expressions so the column appears alone on the left side of the predicate.

-- Inefficient
SELECT * FROM dept WHERE sal * 12 > 25000;

-- Efficient
SELECT * FROM dept WHERE sal > 25000/12;

24. Use UNION ALL when duplicate rows are acceptable

SELECT col1 FROM t1
UNION ALL
SELECT col1 FROM t2;

25. Prefer WHERE filtering over ORDER BY for performance

ORDER BY

can use an index only if all ordered columns appear in the same index in the same order and are defined NOT NULL. Filtering with WHERE is usually cheaper.

26. Avoid implicit type conversion on indexed columns

Cast values explicitly to match the column type.

SELECT * FROM emp WHERE empno = TO_NUMBER('123');

27. Use the leading column of a composite index

The optimizer will use a multi‑column index only if the first column is referenced in the WHERE clause.

28. Use UNION ALL instead of UNION when sorting is unnecessary

SELECT acct_num, balance_amt FROM debit_transactions WHERE tran_date = DATE '1995-12-31'
UNION ALL
SELECT acct_num, balance_amt FROM debit_transactions WHERE tran_date = DATE '1995-12-31';

29. Rewrite ORDER BY as WHERE when the ordering column is indexed

If the ordering column can be used to filter rows directly, replace the ORDER BY with a WHERE predicate.

30. COUNT variations

COUNT(*)

and COUNT(1) are usually equivalent; both count rows including NULLs. COUNT(column) excludes rows where the column is NULL.

When a primary‑key column is available, COUNT(pk_column) can be the fastest form.

31. Avoid operators that disable index usage

Operators such as !=, concatenation ( ||), arithmetic ( +), and NOT on indexed columns cause full scans.

32. Index selectivity guideline

If a query returns more than ~30 % of the rows, a full table scan may be faster than using an index.

33. Minimize resource‑intensive operations

Operations like DISTINCT, UNION, MINUS, INTERSECT, and ORDER BY trigger costly sorts. Rewrite or replace them when possible.

34. Optimize GROUP BY

Apply restrictive WHERE conditions before the GROUP BY to reduce the number of rows that need to be aggregated.

35. Proper NULL handling

Because NULLs are not indexed, predicates like col IS NULL or col IS NOT NULL prevent index usage. Design queries to avoid such predicates on indexed columns.

36. Write join predicates without concatenation

-- Inefficient
SELECT * FROM employee WHERE first_name||last_name = 'BillClinton';

-- Efficient
SELECT * FROM employee WHERE first_name = 'Bill' AND last_name = 'Clinton';

37. Position wildcards wisely in LIKE patterns

A leading % disables index use. Place the wildcard at the end (e.g., last_name LIKE 'c%') to allow the optimizer to use the index.

38. Avoid expressions in ORDER BY

Functions or calculations in the ORDER BY clause prevent index usage. Ensure the ordering columns are indexed and defined NOT NULL for optimal performance.