40 Proven Oracle SQL Optimization Tips to Supercharge Query Performance

1. SQL Execution Steps

Syntax analysis → semantic analysis → view rewrite → expression rewrite → optimizer selection → join method selection → join order selection → data access path selection → execution of the plan.

2. Choose the Right Oracle Optimizer

RULE (rule‑based), COST (cost‑based) and CHOOSE (hybrid) are the three optimizer modes available in Oracle.

3. Table Access Methods

Full table scan : Oracle reads multiple data blocks sequentially to scan the whole table.

ROWID access : The ROWID stores the physical location of a row; indexes provide fast ROWID lookup, speeding up queries that use indexed columns.

4. Shared SQL Statements

Parsed SQL statements are cached in the SGA shared pool.

Before executing a statement Oracle first checks the shared pool for a cached version.

Increasing the shared pool size can improve overall performance.

5. Order Tables for Maximum Efficiency

Oracle parses the FROM clause from right to left; the table appearing last (the driving table) is accessed first.

When multiple tables are joined, Oracle scans the driving table, sorts its rows, then merges rows from the next table, and so on.

This rule applies only to the rule‑based optimizer.

6. WHERE Clause Join Order

Oracle parses the WHERE clause from bottom‑up (right‑to‑left). Conditions that filter the most rows should be placed at the end of the clause.

7. Avoid Using ‘*’ in SELECT

Oracle expands ‘*’ by querying the data dictionary, which adds overhead.

Explicitly list required columns to reduce parsing cost.

8. Reduce the Number of Database Calls

Each SQL execution triggers parsing, index usage estimation, bind variable processing, and block reads; minimizing round‑trips cuts overall work.

9. Combine Unrelated Simple Queries

When possible, merge several simple SELECT statements into a single query to lower I/O, but be aware of readability trade‑offs.

10. Use TRUNCATE Instead of DELETE

DELETE writes undo information for possible rollback; TRUNCATE does not, making it faster and less resource‑intensive.

TRUNCATE can only remove all rows from a table.

11. Commit Frequently

Frequent COMMIT releases rollback segments, locks, and redo buffer space, improving concurrency.

12. Counting Rows Efficiently

SELECT COUNT(*) FROM tablename;</code>
<code>SELECT COUNT(1) FROM tablename;</code>
<code>SELECT COUNT(column) FROM tablename;

COUNT(1) is fastest without a primary‑key index; COUNT(*) can be faster when only one column exists; COUNT(column) is best when an indexed column is counted.

13. Replace HAVING with WHERE

HAVING filters after aggregation, requiring sorting; moving the filter to WHERE reduces work.

14. Minimize Table Lookups in Subqueries

/* Inefficient */
SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = (SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER = (SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);

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

15. Use Table Aliases

Aliasing tables and prefixing column names removes ambiguity and speeds up parsing.

16. Use EXISTS Instead of IN

/* Inefficient */
SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB');

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

17. Use NOT EXISTS Instead of NOT IN

/* Inefficient */
SELECT * FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT='A');

/* Efficient */
SELECT * FROM EMP E WHERE NOT EXISTS (SELECT 1 FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT='A');

18. Replace EXISTS with Joins When Possible

/* Inefficient */
SELECT ENAME FROM EMP E WHERE EXISTS (SELECT 1 FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT='A');

/* Efficient */
SELECT ENAME FROM DEPT D, EMP E WHERE E.DEPT_NO = D.DEPT_NO AND D.DEPT_CAT='A';

19. Replace DISTINCT with EXISTS

/* Inefficient */
SELECT DISTINCT D.DEPT_NO, D.DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO = E.DEPT_NO;

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

20. Identify Inefficient SQL

SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,
       ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_ratio,
       ROUND(DISK_READS/EXECUTIONS,2) 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 4 DESC;

21. Use EXPLAIN PLAN

EXPLAIN PLAN shows how Oracle will join tables, which indexes it will use, and whether it will perform an index or full‑table scan, without executing the query.

22. SQL*Plus AUTOTRACE

SQL> SET AUTOTRACE TRACEONLY
SQL> /

Displays the execution plan and statistics without returning query results.

23. Index Usage Basics

Unique‑scan and range‑scan are the two access methods.

24. Choose the Driving Table Wisely

In a cost‑based optimizer Oracle evaluates table size and index statistics to pick the lowest‑cost path.

In a rule‑based optimizer the last table in the FROM clause becomes the driving table if all join predicates are indexed.

25. Multiple Equal Indexes

When several indexes can satisfy a predicate, Oracle may use them all and merge the results; the last table’s index gets the highest priority.

26. Equality vs. Range Comparisons

Equality predicates are preferred over range predicates when both are possible.

27. Unclear Index Ranking

If Oracle cannot decide which index is better, it uses the first index appearing in the WHERE clause.

28. Avoid Functions on Indexed Columns

/* Inefficient */
SELECT * FROM DEPT WHERE SAL*12 > 25000;

/* Efficient */
SELECT * FROM DEPT WHERE SAL > 25000/12;

29. Automatic Index Selection

If a unique index exists on a column used in the predicate, Oracle prefers it over non‑unique indexes.

30. Force Index Usage When Needed

SELECT ENAME FROM EMP WHERE EMPNO = 7935 AND DEPTNO+0 = 10;  -- DEPTNO index disabled
SELECT ENAME FROM EMP WHERE EMPNO = 7935 AND DEPTNO = 10;   -- index used

31. Do Not Apply Functions to Index Columns

/* Inefficient */
SELECT * FROM DEPT WHERE SAL*12 > 25000;

/* Efficient */
SELECT * FROM DEPT WHERE SAL > 25000/12;

32. Use Hints Sparingly

FULL – force a full table scan.

ROWID – force ROWID access.

CACHE – keep result set in SGA.

INDEX – force index usage.

Other hints: ALL_ROWS, FIRST_ROWS, RULE, USE_NL, USE_MERGE, USE_HASH, etc.

Apply hints only to a few critical statements.

33. WHERE Clauses That Bypass Indexes

‘!=’ or ‘<>’ cannot use an index.

String concatenation (||) disables index usage.

Arithmetic on indexed columns (e.g., COL+0) disables the index.

Comparisons between the same indexed column (e.g., COL = NVL(:val, COL)) prevent index use.

34. Replace OR with UNION

/* Efficient */
SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10
UNION
SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE REGION = 'MELBOURNE';

/* Inefficient */
SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = 'MELBOURNE';

35. Replace OR with IN on Indexed Columns

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

/* Inefficient */
SELECT * FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

36. Avoid IS NULL / IS NOT NULL on Indexed Columns

/* Inefficient */
SELECT * FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

/* Efficient */
SELECT * FROM DEPARTMENT WHERE DEPT_CODE >= 0;

37. Always Use the Leading Column of a Composite Index

CREATE INDEX multindex ON multiindexusage(inda, indb);
SELECT * FROM multiindexusage WHERE indb = 1;  -- full scan, leading column missing
SELECT * FROM multiindexusage WHERE inda = 1; -- index used

38. Replace >= with > When Possible

/* Efficient */
SELECT * FROM EMP WHERE DEPTNO >= 4;

/* Inefficient */
SELECT * FROM EMP WHERE DEPTNO > 3;

The >= predicate lets the optimizer jump directly to the first matching row.

39. Use UNION ALL Instead of UNION When Duplicates Are Not a Concern

UNION ALL skips the sorting step required to eliminate duplicates, yielding better performance.

40. Use Hints to Guide the Optimizer

FULL – force full table scan.

ROWID – force ROWID access.

CACHE – keep results in memory.

INDEX – force index scan.

ALL_ROWS, FIRST_ROWS, RULE, USE_NL, USE_MERGE, USE_HASH, etc.

Apply hints only to a few critical queries to avoid over‑tuning.