---

Introduction

Most Java performance problems eventually become SQL problems.

Applications become slow because of:

• Missing indexes
• Poor joins
• Full table scans
• Excessive sorting
• Improper pagination
• Incorrect SQL design
• Large result sets

As a Java developer, understanding SQL execution and query optimization can significantly improve application performance.

This article covers:

✅ SQL execution order
✅ Joins
✅ Aggregations
✅ Subqueries
✅ Window functions
✅ Explain plans
✅ Index usage
✅ Query optimization techniques

---

Sample Schema

EMPLOYEE

EMP_ID	NAME	DEPT_ID	SALARY
1	John	10	5000
2	Mary	20	7000
3	Steve	10	6000
4	David	30	4000

---

DEPARTMENT

DEPT_ID	DEPT_NAME
10	IT
20	HR
30	Finance

---

SQL Execution Order

Although developers write:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Oracle executes:

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY

Understanding this helps explain many SQL errors.

---

SELECT

SELECT EMP_ID,
       NAME
FROM EMPLOYEE;

Avoid:

SELECT *
FROM EMPLOYEE;

Problems:

• More network traffic
• Larger result sets
• Reduced index usage

---

WHERE Clause

SELECT *
FROM EMPLOYEE
WHERE SALARY > 5000;

Filters rows before grouping.

---

ORDER BY

SELECT *
FROM EMPLOYEE
ORDER BY SALARY DESC;

Sorting is expensive.

Avoid unnecessary sorting.

---

Aggregate Functions

COUNT

SELECT COUNT(*)
FROM EMPLOYEE;

---

SUM

SELECT SUM(SALARY)
FROM EMPLOYEE;

---

AVG

SELECT AVG(SALARY)
FROM EMPLOYEE;

---

MAX

SELECT MAX(SALARY)
FROM EMPLOYEE;

---

GROUP BY

SELECT DEPT_ID,
       COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_ID;

Output:

DEPT_ID	COUNT
10	2
20	1
30	1

---

HAVING

SELECT DEPT_ID,
       COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_ID
HAVING COUNT(*) > 1;

HAVING filters grouped results.

---

INNER JOIN

SELECT E.NAME,
       D.DEPT_NAME
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D
ON E.DEPT_ID = D.DEPT_ID;

Only matching rows.

---

LEFT JOIN

SELECT E.NAME,
       D.DEPT_NAME
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D
ON E.DEPT_ID = D.DEPT_ID;

All employees returned.

---

RIGHT JOIN

Returns all department rows.

---

FULL OUTER JOIN

Returns rows from both tables.

---

CROSS JOIN

SELECT *
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;

Produces Cartesian products.

Avoid unless intentionally needed.

---

Self Join

SELECT E.NAME,
       M.NAME
FROM EMPLOYEE E
JOIN EMPLOYEE M
ON E.MANAGER_ID = M.EMP_ID;

Useful for hierarchies.

---

Subqueries

Scalar Subquery

SELECT *
FROM EMPLOYEE
WHERE SALARY =
(
    SELECT MAX(SALARY)
    FROM EMPLOYEE
);

---

IN Subquery

SELECT *
FROM EMPLOYEE
WHERE DEPT_ID IN
(
    SELECT DEPT_ID
    FROM DEPARTMENT
);

---

EXISTS

SELECT *
FROM EMPLOYEE E
WHERE EXISTS
(
    SELECT 1
    FROM DEPARTMENT D
    WHERE D.DEPT_ID = E.DEPT_ID
);

Often faster than IN.

---

Common Table Expressions (CTE)

WITH HIGH_SALARY AS
(
    SELECT *
    FROM EMPLOYEE
    WHERE SALARY > 5000
)
SELECT *
FROM HIGH_SALARY;

Benefits:

• Readability
• Reusability
• Simpler SQL

---

Window Functions

---

ROW_NUMBER

SELECT NAME,
       ROW_NUMBER()
       OVER(
           ORDER BY SALARY DESC)
FROM EMPLOYEE;

---

RANK

SELECT NAME,
       RANK()
       OVER(
          ORDER BY SALARY DESC)
FROM EMPLOYEE;

---

DENSE_RANK

SELECT NAME,
       DENSE_RANK()
       OVER(
          ORDER BY SALARY DESC)
FROM EMPLOYEE;

---

Top N Query

SELECT *
FROM
(
    SELECT E.*,
           ROW_NUMBER()
           OVER(
             ORDER BY SALARY DESC) RN
    FROM EMPLOYEE E
)
WHERE RN <= 5;

---

Second Highest Salary

SELECT MAX(SALARY)
FROM EMPLOYEE
WHERE SALARY <
(
    SELECT MAX(SALARY)
    FROM EMPLOYEE
);

---

Explain Plan

EXPLAIN PLAN FOR
SELECT *
FROM EMPLOYEE
WHERE EMP_ID = 10;

View:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

---

Common Operations

Operation	Meaning
TABLE ACCESS FULL	Full scan
INDEX RANGE SCAN	Uses index
INDEX UNIQUE SCAN	PK lookup
HASH JOIN	Hash join
SORT ORDER BY	Sorting
NESTED LOOP	Loop joins

---

Full Table Scan

Oracle reads the entire table.

Occurs when:

• No index
• Small table
• Poor query

Example:

SELECT *
FROM EMPLOYEE
WHERE NAME = 'JOHN';

Without index.

---

Index Scan

CREATE INDEX IDX_EMP_NAME
ON EMPLOYEE(NAME);

Oracle can now locate rows faster.

---

Composite Index

CREATE INDEX IDX_EMP
ON EMPLOYEE
(
    DEPT_ID,
    STATUS
);

Useful:

WHERE DEPT_ID = ?
AND STATUS = ?

---

Functions Prevent Index Usage

Bad:

SELECT *
FROM EMPLOYEE
WHERE UPPER(NAME) = 'JOHN';

Oracle cannot use a normal index efficiently.

Better:

Store normalized values.

---

Leading Wildcards

Bad:

WHERE NAME LIKE '%JOHN%'

Index often ignored.

Better:

WHERE NAME LIKE 'JOHN%'

---

Pagination

Oracle 12c:

SELECT *
FROM EMPLOYEE
ORDER BY EMP_ID
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY;

Avoid:

SELECT *
FROM EMPLOYEE;

for APIs.

---

Fetch Only Required Columns

Bad:

SELECT *
FROM EMPLOYEE;

Good:

SELECT EMP_ID,
       NAME
FROM EMPLOYEE;

Benefits:

• Reduced memory
• Reduced network traffic

---

Bind Variables

Bad:

WHERE EMP_ID = 100

Good:

WHERE EMP_ID = :empId

Benefits:

• Execution plan reuse
• Reduced parsing

---

Oracle Query Optimization Checklist

Filter Early

WHERE STATUS='ACTIVE'

---

Return Few Columns

Avoid SELECT *.

---

Index Search Columns

• IDs
• Foreign keys
• Search columns

---

Avoid Functions

UPPER(NAME)

---

Use Pagination

OFFSET/FETCH.

---

Analyze Execution Plans

Always inspect:

• Full scans
• Sorts
• Hash joins

---

Real Production Example

Slow query:

SELECT *
FROM ORDERS
WHERE CUSTOMER_NAME LIKE '%JOHN%';

Problems:

• Full scan
• Large table

Better:

WHERE CUSTOMER_NAME LIKE 'JOHN%'

or:

CUSTOMER_NAME_SEARCH

indexed column.

---

SQL Performance Checklist

Question	Yes/No
Using SELECT *?	❌
Index available?	✅
Using pagination?	✅
Returning required columns only?	✅
Avoiding functions?	✅
Using bind variables?	✅
Execution plan analyzed?	✅

---

Interview Questions

Difference between WHERE and HAVING?

WHERE filters rows.

HAVING filters groups.

---

Difference between RANK and DENSE_RANK?

RANK skips numbers.

DENSE_RANK does not.

---

What causes a full table scan?

Missing indexes.

---

Why avoid SELECT *?

More data transferred.

---

Why use bind variables?

Execution plan reuse.

---

What is a CTE?

Temporary named result set.

---

Summary

This article covered:

✅ SQL execution order

✅ Joins

✅ Aggregation

✅ Group By

✅ Having

✅ Subqueries

✅ CTE

✅ Window functions

✅ Explain plans

✅ Index usage

✅ Pagination

✅ Query optimization

---

Next Article

Part 4 – Oracle JDBC Connectivity

Topics:

• JDBC architecture
• Driver types
• Oracle JDBC drivers
• Connection pooling
• PreparedStatement
• Batch processing
• Transactions
• Exception handling
• HikariCP
• Spring Boot integration