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.”

 

Inline View FAQS

1. What Is an Inline View?

An Inline View is a subquery in the FROM clause that:

• Acts like a temporary table or view
• Provides a derived table for the outer query
• Exists only for the duration of the query execution

Syntax:

SELECT outer_columns

FROM (

    SELECT inner_columns

    FROM table_name

    WHERE conditions

) alias_name

WHERE outer_conditions;

2. Why Use an Inline View?

• Simplifies complex queries
• Allows aggregation before outer filtering
• Enables joining summarized results
• Avoids creating permanent views or temporary tables

3. Can Inline Views Have Aggregation?

Yes. Common use is aggregating data before filtering.

Example:

SELECT department_id, avg_sal

FROM (

    SELECT department_id, AVG(salary) AS avg_sal

    FROM employees

    GROUP BY department_id

) dept_avg

WHERE avg_sal > 5000;

Inner query calculates department averages.
Outer query filters departments with avg salary > 5000.

4. Can Inline Views Be Joined?

Yes. Inline views can be treated like regular tables in joins.

Example:

SELECT e.employee_id, d.avg_sal

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;

Joins employees with department averages and filters accordingly.

5. Difference Between Inline View and Subquery in WHERE

Feature	Inline View	WHERE Subquery
Location	FROM clause	WHERE/HAVING/SELECT
Acts Like	Temporary table	Single value or list
Can use	Aggregates, multiple columns	Single or multiple values
Can join	Yes	Usually not

6. Can Inline View Be Correlated?

❌ No.

• Inline views cannot reference outer query columns.
• If inner query references outer columns → becomes a correlated subquery.

7. Can Inline View Return Multiple Columns?

✅ Yes.

Example:

SELECT e.employee_id, d.department_id, d.avg_sal

FROM employees e

JOIN (

    SELECT department_id, AVG(salary) AS avg_sal, COUNT(*) AS emp_count

    FROM employees

    GROUP BY department_id

) d

ON e.department_id = d.department_id;

8. Inline View in UPDATE Statement

Example:

UPDATE employees e

SET e.salary = (

    SELECT d.avg_sal

    FROM (

        SELECT department_id, AVG(salary) AS avg_sal

        FROM employees

        GROUP BY department_id

    ) d

    WHERE e.department_id = d.department_id

)

WHERE e.department_id = 10;

Inner inline view calculates department averages; outer query updates salaries.

9. Inline View in DELETE Statement

Example:

DELETE FROM employees

WHERE employee_id IN (

    SELECT employee_id

    FROM (

        SELECT employee_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) rnk

        FROM employees

    ) temp

    WHERE rnk > 5

);

Deletes employees not in top 5 salaries per department.

10. Inline View vs WITH Clause (CTE)

Feature	Inline View	WITH Clause (CTE)
Scope	Only in FROM clause	Named temporary result, can be reused
Readability	Nested	Often cleaner
Reuse	No	Yes
Performance	Optimizer may merge	Optimizer may merge

11. Performance Considerations

• Optimized by Oracle; may merge with outer query
• Indexes on underlying tables still apply
• For large datasets:
• Avoid excessive nested inline views
• Use analytic functions if possible
• Check execution plan

12. Real-World Business Example

Requirement:

Find employees earning more than department average.

SELECT e.employee_id, e.salary, d.avg_sal

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;

13. Common Mistakes

• Forgetting alias for inline view → ORA errors
• Using correlated columns inside inline view
• Over-nesting inline views
• Assuming inline views always improve performance

14. Best Practices

• Always provide an alias
• Use inline views for aggregation before filtering
• Prefer analytic functions for per-row calculations
• Check execution plan for complex views
• Simplify nested inline views when possible

15. Short Interview Answer

“An inline view is a subquery in the FROM clause that acts as a temporary table. It allows aggregation, derived columns, and joins within a single query. It must have an alias and exists only for the duration of the query.”