Subquery Performance FAQS

1. What Is a Subquery in Oracle?

A subquery is a query nested inside another SQL statement. Subqueries can be:

·        Single-row (returns one value)

·        Multi-row (returns multiple values)

·        Scalar (returns a single value used in expressions)

·        Correlated (references outer query)

·        Non-Correlated (independent of outer query)

·        Inline View (used in FROM clause as a derived table)

Performance depends on type, execution frequency, and indexing.

2. How Does Oracle Execute Subqueries?

Non-Correlated Subquery

·        Executed once; result reused by outer query

Example:

SELECT employee_id

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

Correlated Subquery

·        Executed once per outer row; can be expensive for large tables

Example:

SELECT e.employee_id

FROM employees e

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

);

3. Factors Affecting Subquery Performance

1.    Subquery Type: Non-correlated usually fast; correlated can be slow

2.    Indexes: Indexed columns improve performance

3.    Aggregation: Aggregates like AVG(), MAX(), COUNT() are optimized

4.    Number of Rows Returned: Single-row safe with =, >, <; multi-row requires IN, ANY, ALL

5.    Nested Subqueries: Deep nesting may slow queries; optimizer may merge

6.    Analytic Functions: Can replace correlated subqueries efficiently

4. Performance Tips: Correlated Subquery

·        Use EXISTS instead of IN

·        Add indexes on correlated columns

·        Consider JOIN or analytic functions

·        Limit outer query rows early

5. Performance Tips: Non-Correlated Subquery

·        Executed once; usually acceptable performance

·        Can use JOIN or inline view for optimization

Example:

SELECT e.employee_id, e.salary

FROM employees e

JOIN (

    SELECT department_id, AVG(salary) AS avg_sal

    FROM employees

    GROUP BY department_id

) d

ON e.department_id = d.department_id

WHERE e.salary > d.avg_sal;

6. Inline View Performance

·        Optimizer may merge inline view with outer query

·        Avoid excessive nesting

·        Prefer analytic functions for repeated aggregation

7. EXISTS vs IN Performance

Feature	EXISTS	IN
Checks existence	Yes	Compares values
Optimized for large datasets	Often better	May be slower
Handles NULLs	Yes	NOT IN may fail if NULL present
Execution	Stops at first match	Compares against all

8. NOT IN Considerations

·        NOT IN can return no rows if subquery contains NULL

·        Use NOT EXISTS as safer alternative:

SELECT e.employee_id

FROM employees e

WHERE NOT EXISTS (

    SELECT 1

    FROM departments d

    WHERE e.department_id = d.department_id

);

9. Using Aggregate Functions

·        Aggregates reduce result size → faster outer query

Example:

SELECT employee_id

FROM employees

WHERE salary > (SELECT MAX(salary) FROM employees);

10. Subquery Unnesting

·        Optimizer may transform subqueries into JOINs internally

·        Correlated → Non-correlated using JOIN or analytic function

Example:

-- Correlated subquery

SELECT e.employee_id

FROM employees e

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

);

-- Optimized using analytic function

SELECT employee_id

FROM (

    SELECT employee_id,

           AVG(salary) OVER (PARTITION BY department_id) dept_avg,

           salary

    FROM employees

)

WHERE salary > dept_avg;

11. Common Mistakes That Hurt Performance

·        Correlated subquery without index

·        Using NOT IN with potential NULLs

·        Returning more rows than needed

·        Deeply nested inline views

·        Assuming subquery always slower than JOIN

12. Best Practices for Subquery Performance

·        Use indexes on subquery columns

·        Use analytic functions for per-group calculations

·        Prefer EXISTS over IN for large correlated datasets

·        Avoid NOT IN with NULLs

·        Use inline views for aggregation before filtering

·        Check execution plan with EXPLAIN PLAN

·        Limit result set early

13. Real-World Examples

Example 1: Correlated Subquery (Slower)

SELECT e.employee_id

FROM employees e

WHERE salary > (

    SELECT AVG(salary)

    FROM employees

    WHERE department_id = e.department_id

);

Example 2: Using Analytic Function (Faster)

SELECT employee_id

FROM (

    SELECT employee_id, salary,

           AVG(salary) OVER (PARTITION BY department_id) dept_avg

    FROM employees

)

WHERE salary > dept_avg;

14. Short Interview Answer

“Subquery performance in Oracle depends on type, correlation, indexing, and result size. Non-correlated subqueries execute once and are usually fast. Correlated subqueries execute per row and may be slow. Performance can be improved using indexes, EXISTS, analytic functions, inline views, or JOINs. Avoid NOT IN with NULLs and always check the execution plan.”