Oracle Database — Advanced Query Processing Fundamentals | Page

ORACLE DATABASE
Advanced Query Processing Fundamentals
Comprehensive Study Notes

COVERS:
Part A: Overview of Query Processing
Part B: SQL Statement Processing in Oracle
Part C: Algorithms for Executing Query Operations

Oracle SQL & PL/SQL | Query Optimization | Execution Algorithms

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

PART A: Overview of Query Processing

1. Definition of Query Processing

Query processing refers to the set of activities involved in extracting data from a database in response
to a query. It encompasses all steps that a Database Management System (DBMS) undertakes from
the moment a query is submitted until the final result set is returned to the user.

In Oracle Database, query processing is a sophisticated, multi-layered process that involves translating
high-level SQL statements into low-level data retrieval operations, optimizing the chosen approach, and
executing the resulting plan efficiently.

💡 KEY POINT
Query processing = the complete lifecycle of a SQL statement from submission to result delivery.
It involves parsing, optimization, plan generation, and execution — all managed by Oracle's Query
Processor.

Formal Definition
Query processing is defined as: the translation of high-level query language statements (SQL) into
sequences of operations that can be executed on the physical storage layer, while applying
optimization techniques to minimise the cost of execution in terms of CPU time, I/O operations, and
memory consumption.

2. Objectives of Query Processing

The primary objectives of query processing in Oracle and other RDBMS systems are:

Objective Description Oracle Mechanism

Correctness Ensure the query result is Query Parser & Semantic
semantically correct and complete Analyzer
Efficiency Minimise response time and Cost-Based Optimizer (CBO)
resource consumption
Optimisation Select the most efficient execution Oracle Query Optimizer
plan from many alternatives
Scalability Handle growing data volumes Parallel Query Execution
without performance degradation
Transparency Hide physical storage details from Abstraction Layers / SGA
the user
Concurrency Support multiple simultaneous Oracle Concurrency Control /
queries safely MVCC
Resource Mgmt Prevent any single query from Oracle Resource Manager
monopolising resources

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

3. Stages of Query Processing

Oracle processes a SQL query through the following sequential stages:

QUERY PROCESSING ARCHITECTURE

┌─────────────────────────────────────────────────────────────────┐
│ ORACLE QUERY PROCESSING PIPELINE │
├─────────────────────────────────────────────────────────────────┤
│ │
│ [1] SQL STATEMENT SUBMITTED BY USER / APPLICATION │
│ │ │
│ ▼ │
│ ┌──────────────────────────────┐ │
│ │ PARSING │ ← Lexical, Syntax, │
│ │ • Tokenisation │ Semantic Analysis │
│ │ • Syntax Check │ │
│ │ • Semantic Check │ │
│ └──────────────┬───────────────┘ │
│ │ Parse Tree │
│ ▼ │
│ ┌──────────────────────────────┐ │
│ │ QUERY OPTIMISATION │ ← Cost-Based Optimizer │
│ │ • Logical Rewrite │ (CBO) evaluates 100s │
│ │ • Statistics Evaluation │ of plans │
│ │ • Plan Generation │ │
│ └──────────────┬───────────────┘ │
│ │ Execution Plan │
│ ▼ │
│ ┌──────────────────────────────┐ │
│ │ ROW SOURCE GENERATION │ ← Converts plan into │
│ │ • Row Source Tree │ executable row sources │
│ │ • Iterator Creation │ │
│ └──────────────┬───────────────┘ │
│ │ Row Source Tree │
│ ▼ │
│ ┌──────────────────────────────┐ │
│ │ EXECUTION │ ← Execution Engine │
│ │ • Data Fetching │ fetches data via │
│ │ • Join Processing │ Storage Manager │
│ │ • Sorting / Aggregation │ │
│ └──────────────┬───────────────┘ │
│ │ Result Set │
│ ▼ │
│ [5] RESULT RETURNED TO USER / APPLICATION │
└─────────────────────────────────────────────────────────────────┘

4. Query Processing Architecture & Component Roles

4.1 The Parser
The Parser is the first stage of query processing. It receives the raw SQL text and performs three
checks:
• Lexical Analysis: Breaks the SQL text into tokens (keywords, identifiers, literals, operators).
• Syntax Check: Validates the query against Oracle SQL grammar rules (e.g., correct use of
SELECT, FROM, WHERE).

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

• Semantic Check: Verifies that referenced objects (tables, columns) exist and the user has
appropriate privileges.

Example — when Oracle receives the following:

-- Oracle parses each element of this statement
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE [Link] > 5000
ORDER BY e.last_name;

-- Lexical tokens:
-- SELECT | e.employee_id | , | e.last_name | , | d.department_name
-- FROM | employees | e | JOIN | departments | d | ON | ...
-- WHERE | [Link] | > | 5000 | ORDER BY | e.last_name

4.2 The Query Optimizer

Oracle's Cost-Based Optimizer (CBO) is the most critical component. It evaluates multiple execution
plans and selects the one with the lowest estimated cost (I/O + CPU).
• Gathers statistics on tables, indexes, and columns.
• Considers all possible access paths: full table scans, index scans, etc.
• Evaluates join orders and join methods.
• Produces the optimal execution plan.

-- View execution plan using EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE [Link] > 10000;

-- Display the plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Sample Output:
-- Plan hash value: 3256081968
-- -------------------------------------------------------
-- | Id | Operation | Name | Rows |
-- -------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 10 |
-- | 1 | HASH JOIN | | 10 |
-- | 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 |
-- | 3 | TABLE ACCESS BY INDEX| EMPLOYEES | 10 |
-- | 4 | INDEX RANGE SCAN | EMP_SAL_IDX | 10 |
-- -------------------------------------------------------

4.3 Execution Engine

The Execution Engine carries out the instructions in the execution plan produced by the optimizer. It:
• Iterates through row sources in a tree structure (iterator model).
• Fetches data blocks from the Buffer Cache or directly from disk.
• Performs join operations, aggregations, sorting, and filtering.

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

• Returns rows to the calling process in batches.

4.4 Storage Manager

The Storage Manager manages physical data storage and retrieval. In Oracle it consists of:
• Buffer Cache: Caches frequently accessed data blocks in memory (SGA).
• Redo Log Buffer: Records all changes for recovery purposes.
• DB Writer (DBWn): Writes dirty buffers from cache to disk.
• Data Files: Physical OS files containing the database.

Component Function Location

Buffer Cache Holds recently accessed data SGA (RAM)
blocks
Shared Pool Stores parsed SQL, execution SGA (RAM)
plans, data dictionary
Data Files Persistent storage of all data Disk (OS Level)
Redo Logs Change vectors for recovery Disk (OS Level)
Control Files DB structure metadata Disk (OS Level)

5. Logical vs Physical Query Processing

Query processing is divided into two conceptual layers — the logical and the physical:

Aspect Logical Query Processing Physical Query Processing

Definition Describes WHAT the query does Describes HOW the query is executed
(semantics) (mechanics)
Order Follows the conceptual SQL clause order Can deviate — optimizer chooses the most
efficient path
SQL Order FROM → WHERE → GROUP BY → May start with index, push down
HAVING → SELECT → ORDER BY predicates, reorder joins
User Visible? Yes — query result matches logical No — internal to the engine
expectations
Optimisation? No optimisation — pure semantics Full cost-based optimisation applied
Example SELECT salary FROM emp WHERE Oracle may use INDEX RANGE SCAN on
dept=10 dept_id
Oracle Tool SQL language standard EXPLAIN PLAN, DBMS_XPLAN, AWR

Example illustrating the difference:

-- Logical order of processing this query:
-- 1. FROM employees, departments → identify row sources
-- 2. JOIN ON department_id → combine rows
-- 3. WHERE salary > 5000 → filter rows
-- 4. GROUP BY department_name → group remaining rows
-- 5. HAVING COUNT(*) > 3 → filter groups
-- 6. SELECT department_name, AVG(sal) → project columns
-- 7. ORDER BY AVG(salary) DESC → sort result

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

SELECT d.department_name, AVG([Link]) AS avg_salary

FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE [Link] > 5000
GROUP BY d.department_name
HAVING COUNT(*) > 3
ORDER BY avg_salary DESC;

-- PHYSICAL execution (Oracle may choose):

-- 1. Index Range Scan on emp_salary_idx (WHERE predicate pushed down)
-- 2. Hash Join with departments
-- 3. Hash Group By
-- 4. Sort (ORDER BY)

6. Importance of Efficient Query Processing in Large Databases

As data volumes grow to terabytes and petabytes, the difference between an optimised and
unoptimised query can be the difference between seconds and hours of execution time.

Problem Impact Oracle Solution

Full table scans on large tables Millions of disk reads, slow Indexes, partitioning, parallel
response query
Inefficient join orders Cartesian explosion of CBO evaluates optimal join
intermediate results sequence
Missing statistics Optimizer chooses wrong plan DBMS_STATS.GATHER_TABLE
_STATS
High parse overhead CPU waste on repeated parsing Shared SQL Area + Bind
Variables
Memory pressure Excessive disk I/O (sort spills) PGA sizing, memory-adaptive
algorithms
Concurrency issues Lock contention, serialisation MVCC, row-level locking

7. How Oracle Improves Query Performance

7.1 Cost-Based Optimizer (CBO)
Oracle's CBO analyses table statistics (row counts, column distributions, histograms) to estimate the
cost of different execution plans and select the cheapest one.
-- Gather statistics for the optimizer
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'HR',
tabname => 'EMPLOYEES',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);
END;
/

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

7.2 Indexes
-- B-Tree Index (default)
CREATE INDEX emp_dept_idx ON employees(department_id);

-- Bitmap Index (low-cardinality columns)

CREATE BITMAP INDEX emp_gender_idx ON employees(gender);

-- Function-Based Index
CREATE INDEX emp_upper_name ON employees(UPPER(last_name));

-- Composite Index
CREATE INDEX emp_dept_sal ON employees(department_id, salary);

7.3 Partitioning
-- Range Partitioning (common for date-based data)
CREATE TABLE sales_data (
sale_id NUMBER,
sale_date DATE,
amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
PARTITION p2022 VALUES LESS THAN (DATE '2023-01-01'),
PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01')
);

-- Partition pruning: Oracle only scans relevant partition

SELECT * FROM sales_data
WHERE sale_date BETWEEN DATE '2023-01-01' AND DATE '2023-12-31';
-- Oracle scans ONLY p2023 partition!

7.4 Parallel Query

-- Enable parallel execution for a query
SELECT /*+ PARALLEL(e, 4) */ department_id, AVG(salary)
FROM employees e
GROUP BY department_id;

-- Set table-level parallelism

ALTER TABLE employees PARALLEL 4;

7.5 Result Cache

-- Cache query results to avoid re-execution
SELECT /*+ RESULT_CACHE */ department_id, COUNT(*)
FROM employees
GROUP BY department_id;

-- Subsequent identical queries return cached results instantly

-- Cache invalidated when underlying data changes

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

8. Practical Case Studies

Case Study 1: E-Commerce Order Reporting
Scenario: A retail company has an ORDERS table with 50 million rows. Daily reports joining ORDERS,
CUSTOMERS, and PRODUCTS were taking 45 minutes.
Solution Applied:
1. Partitioned ORDERS table by RANGE on ORDER_DATE.
2. Created composite index on (CUSTOMER_ID, ORDER_DATE).
3. Gathered fresh statistics using DBMS_STATS.
4. Used RESULT_CACHE hint for frequently-run summary reports.
Result: Query time reduced from 45 minutes to under 2 minutes — a 95% improvement.

Case Study 2: Banking Transaction Analysis

Scenario: A bank needed real-time analysis of 200 million transaction records for fraud detection.
Solution Applied:
5. Applied Bitmap indexes on low-cardinality columns (TRANSACTION_TYPE, STATUS).
6. Enabled Parallel Query with degree 8 on the TRANSACTIONS table.
7. Implemented Automatic Workload Repository (AWR) monitoring to identify top SQL.
8. Rewritten ad-hoc queries to use bind variables, reducing parse overhead by 70%.
Result: Fraud detection queries now complete in under 30 seconds, enabling real-time alerts.

📋 SUMMARY
Query processing transforms SQL into data retrieval operations through: Parsing → Optimisation →
Row Source Generation → Execution.
The Parser validates SQL; the Optimizer selects the best plan; the Execution Engine fetches data;
the Storage Manager manages physical I/O.
Logical processing defines WHAT a query does; Physical processing defines HOW Oracle executes
it.
Oracle improves performance through: CBO, indexes, partitioning, parallel query, result caching, and
bind variables.
Efficient query processing is critical for large databases to avoid full scans, reduce I/O, and improve
concurrency.

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

PART B: SQL Statement Processing in Oracle

1. How Oracle Processes SQL Statements Internally

Every SQL statement submitted to Oracle goes through a well-defined internal lifecycle. Understanding
this lifecycle is essential for writing performant applications and for database administration.

SQL PROCESSING INTERNAL FLOW

┌───────────────────────────────────────────────────────────────────┐
│ ORACLE SQL STATEMENT INTERNAL PROCESSING │
├───────────────────────────────────────────────────────────────────┤
│ │
│ Application / SQL*Plus / SQL Developer │
│ │ SQL Text │
│ ▼ │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ SHARED POOL (SGA) │ │
│ │ │ │
│ │ ┌────────────────┐ ┌───────────────────────────┐ │ │
│ │ │ Library Cache │ │ Data Dictionary Cache │ │ │
│ │ │ (Parsed SQL / │ │ (Table/Column metadata) │ │ │
│ │ │ Exec Plans) │ │ │ │ │
│ │ └────────┬───────┘ └───────────────────────────┘ │ │
│ └───────────┼────────────────────────────────────────────-┘ │
│ │ │
│ SOFT PARSE ◄──── SQL found in cache? ────► HARD PARSE │
│ (reuse plan) NO (full parse) │
│ │ │ │
│ └──────────────┬─────────────────────┘ │
│ ▼ │
│ ┌──────────────┐ │
│ │ OPTIMISATION │ Cost-Based Optimizer │
│ └──────┬───────┘ │
│ ▼ │
│ ┌──────────────┐ │
│ │ ROW SOURCE │ Execution Plan Tree │
│ │ GENERATION │ │
│ └──────┬───────┘ │
│ ▼ │
│ ┌──────────────┐ │
│ │ EXECUTION │ → Buffer Cache → Data Files │
│ └──────┬───────┘ │
│ ▼ │
│ Result Set → Client │
└───────────────────────────────────────────────────────────────────┘

2. Processing Phases
2.1 Parsing
Parsing is the first phase. Oracle checks the Shared Pool's Library Cache for an identical SQL
statement. If not found, Oracle performs a full parse:

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

Parsing Step Description

Tokenisation SQL text is broken into lexical tokens
Syntax Analysis Token sequence validated against SQL grammar
Semantic Analysis Objects (tables, columns) validated; privileges
checked
View Merging Inline views and subqueries may be merged into
main query
Predicate Pushdown WHERE clauses pushed closer to data sources
Parse Tree Creation Internal representation (parse tree) created

-- Observe parsing activity using V$SQL

SELECT sql_id, parse_calls, executions, sql_text
FROM v$sql
WHERE sql_text LIKE '%employees%'
ORDER BY parse_calls DESC;

-- High parse_calls vs executions ratio = parsing problem

-- Ideal: parse_calls = 1, executions = many (soft parse reuse)

2.2 Optimisation
After parsing, the Oracle Cost-Based Optimizer (CBO) evaluates possible execution strategies. It
considers:
• Access paths: Full Table Scan vs. Index Scan vs. Index-Only Scan.
• Join methods: Nested Loops, Sort-Merge Join, Hash Join.
• Join order: Which table to access first (inner vs. outer).
• Transformation rules: Subquery unnesting, view merging, predicate pushing.

-- Force specific optimizer hints (use carefully in production)

-- Full Table Scan
SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e WHERE salary > 5000;

-- Index Scan
SELECT /*+ INDEX(e emp_salary_idx) */ employee_id, last_name
FROM employees e WHERE salary > 5000;

-- First Rows optimisation (OLTP — get first rows fast)

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name
FROM employees ORDER BY hire_date;

-- All Rows optimisation (batch / reporting)

SELECT /*+ ALL_ROWS */ department_id, SUM(salary)
FROM employees GROUP BY department_id;

2.3 Row Source Generation

The Row Source Generator converts the optimizer's execution plan into a tree of row source objects.
Each row source is an iterator that:
• Accepts calls from its parent row source.
Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL
Oracle Database — Advanced Query Processing Fundamentals | Page

• Returns rows one at a time (or in batches).

• May call child row sources to get its input data.

ROW SOURCE TREE

Row Source Tree Example:

┌──────────────────────────┐
│ SORT (ORDER BY) │ ← Root: returns sorted rows
└────────────┬─────────────┘
│
┌────────────┴─────────────┐
│ HASH JOIN │ ← Joins employees & departments
└──────────┬──────┬────────┘
│ │
┌───────────────┘ └───────────────────┐
│ │
┌───┴────────────────┐ ┌──────────────┴──────────┐
│ TABLE ACCESS FULL │ │ INDEX RANGE SCAN + │
│ DEPARTMENTS │ │ TABLE ACCESS BY ROWID │
│ (27 rows) │ │ EMPLOYEES (salary>5000) │
└────────────────────┘ └─────────────────────────┘

2.4 Execution
During execution, Oracle's execution engine navigates the row source tree from the root, pulling rows
from child row sources as needed. The engine interacts with:
• Buffer Cache: First checks if needed data blocks are already in RAM.
• Data Files: If blocks are not cached (cache miss), reads from disk.
• Redo/Undo: For DML statements, redo and undo entries are written.

-- Monitor active SQL execution

SELECT s.sql_id, [Link], s.elapsed_time, s.cpu_time,
s.disk_reads, s.buffer_gets, s.rows_processed
FROM v$sql_monitor s
WHERE [Link] = 'EXECUTING'
ORDER BY s.elapsed_time DESC;

-- Check buffer gets vs disk reads (high disk_reads = I/O problem)

SELECT sql_text, buffer_gets, disk_reads,
ROUND(disk_reads/buffer_gets * 100, 2) AS disk_pct
FROM v$sql
WHERE buffer_gets > 1000
ORDER BY disk_reads DESC;

3. Hard Parsing vs Soft Parsing

Parse efficiency is one of the most critical performance factors in Oracle OLTP systems.

Aspect Hard Parse Soft Parse Soft-Soft Parse

Definition Full parse: SQL not in Library SQL found in Library Cache; SQL found in session cache;
Cache plan reused no latch needed

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

Aspect Hard Parse Soft Parse Soft-Soft Parse

Library Cache Not found — full processing Found — plan retrieved Found in session cursor
Search required cache
Parse Steps Tokenise, Syntax, Semantic, Semantic check + plan Minimal — direct reuse
Optimise, Plan Gen retrieval only
Resource Cost High (CPU, memory, Low (small latch acquisition) Minimal
latching)
Trigger First execution OR after plan Same SQL text in shared Same SQL text, same
eviction pool session
Oracle Latch Library Cache Latch Library Cache Latch (shared) No latch
(exclusive)
Solution Use bind variables; keep SQL Good — aim for this Best — use session cursor
identical cache

4. Shared SQL Areas and Library Cache

The Library Cache is a component of the Shared Pool within Oracle's System Global Area (SGA). It
stores:
• Parsed representations of SQL and PL/SQL statements.
• Execution plans generated by the optimizer.
• Dependencies on database objects.

SGA & LIBRARY CACHE STRUCTURE

┌────────────────────────────────────────────────────┐
│ SYSTEM GLOBAL AREA (SGA) │
│ │
│ ┌─────────────────────────────────────────────┐ │
│ │ SHARED POOL │ │
│ │ │ │
│ │ ┌────────────────────┐ ┌───────────────┐ │ │
│ │ │ LIBRARY CACHE │ │ DATA DICT │ │ │
│ │ │ │ │ CACHE │ │ │
│ │ │ SQL Area: │ │ │ │ │
│ │ │ • Parsed SQL │ │ • Table info │ │ │
│ │ │ • Execution Plans │ │ • Column defs │ │ │
│ │ │ • PL/SQL code │ │ • User privs │ │ │
│ │ │ • Cursor info │ │ • Constraints │ │ │
│ │ └────────────────────┘ └───────────────┘ │ │
│ └─────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────┐ ┌──────────────────────┐ │
│ │ BUFFER CACHE │ │ REDO LOG BUFFER │ │
│ │ (Data Blocks) │ │ (Change Records) │ │
│ └──────────────────┘ └──────────────────────┘ │
└────────────────────────────────────────────────────┘

-- Check Library Cache hit ratio (should be > 95%)

SELECT ROUND((1 - (SUM(reloads) / SUM(pins))) * 100, 2) AS lib_cache_hit_pct
FROM v$librarycache;

-- View Shared Pool sizing

SELECT name, bytes / 1024 / 1024 AS size_mb

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

FROM v$sgainfo
WHERE name IN ('Shared Pool Size', 'Library Cache Size');

-- Find high-parse SQL statements

SELECT sql_id, parse_calls, executions,
ROUND(parse_calls / executions * 100, 1) AS parse_pct,
SUBSTR(sql_text, 1, 80) AS sql_snippet
FROM v$sql
WHERE executions > 100
AND parse_calls / executions > 0.5
ORDER BY parse_calls DESC;

5. Importance of SQL Caching in Oracle

SQL Caching (via the Shared SQL Area in the Library Cache) is fundamental to Oracle's scalability in
OLTP systems. Without it:
• Every user executing the same query would trigger a full hard parse.
• CPU and memory would be consumed generating identical execution plans repeatedly.
• Library Cache latch contention would emerge under concurrent load.

-- BAD: Non-cacheable SQL (unique literals prevent sharing)

SELECT * FROM employees WHERE employee_id = 101;
SELECT * FROM employees WHERE employee_id = 102;
SELECT * FROM employees WHERE employee_id = 103;
-- Each statement is treated as DIFFERENT SQL → 3 hard parses

-- GOOD: Cacheable SQL with bind variables

SELECT * FROM employees WHERE employee_id = :emp_id;
-- ALL executions share ONE cursor → 1 hard parse, N soft parses

-- Check if cursor sharing is helping (CURSOR_SHARING parameter)

SELECT name, value FROM v$parameter WHERE name = 'cursor_sharing';
-- Values: EXACT (default, strictest), FORCE, SIMILAR

6. Bind Variables and Their Importance

Bind variables are placeholders in SQL statements that are replaced with actual values at execution
time. They are the single most important technique for scalable Oracle applications.

Why Bind Variables Matter

Oracle identifies 'identical' SQL by performing a character-by-character comparison of the SQL text.
Two statements that differ only in a literal value are treated as completely different SQL and each
triggers a hard parse.

-- ============================================
-- WITHOUT Bind Variables (POOR practice)
-- ============================================
-- Application generates 1000 logins per minute:
SELECT * FROM users WHERE user_id = 12345; -- Hard Parse
SELECT * FROM users WHERE user_id = 12346; -- Hard Parse again!

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

SELECT * FROM users WHERE user_id = 12347; -- Hard Parse again!

-- Result: 1000 hard parses/min, high CPU, library cache thrashing

-- ============================================
-- WITH Bind Variables (CORRECT practice)
-- ============================================
-- PL/SQL Example
DECLARE
v_emp_id employees.employee_id%TYPE := 100;
v_emp_rec employees%ROWTYPE;
BEGIN
-- :emp_id is a bind variable
SELECT * INTO v_emp_rec
FROM employees
WHERE employee_id = :emp_id; -- bind variable
DBMS_OUTPUT.PUT_LINE('Name: ' || v_emp_rec.last_name);
END;
/

-- ============================================
-- Using EXECUTE IMMEDIATE with Bind Variables
-- ============================================
DECLARE
v_dept_id NUMBER := 60;
v_count NUMBER;
v_sql VARCHAR2(200);
BEGIN
v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept';
EXECUTE IMMEDIATE v_sql INTO v_count USING v_dept_id;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

-- ============================================
-- Using DBMS_SQL with Bind Variables
-- ============================================
DECLARE
v_cursor INTEGER;
v_salary NUMBER := 5000;
v_result NUMBER;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor,
'SELECT COUNT(*) FROM employees WHERE salary > :sal',
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':sal', v_salary);
v_result := DBMS_SQL.EXECUTE_AND_FETCH(v_cursor);
DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

Bind Variable Peeking

Oracle 9i+ introduced Bind Variable Peeking: on first hard parse, Oracle peeks at the actual bind value
to choose an optimal plan. However, the plan is then cached and reused regardless of future bind
values — which can cause issues with skewed data distributions. Oracle 11g+ introduced Adaptive
Cursor Sharing to address this.

-- Adaptive Cursor Sharing (Oracle 11g+)

-- Oracle can maintain MULTIPLE execution plans for one SQL

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

-- if bind values lead to significantly different selectivities

-- Check if a cursor is bind-aware

SELECT sql_id, child_number, is_bind_aware, is_shareable
FROM v$sql
WHERE sql_text LIKE '%employees%'
AND is_bind_aware = 'Y';

Case Study 1: E-Commerce Login Service

Problem: A high-traffic e-commerce platform had 1,200 logins/second. Each login generated a unique
SQL with a literal user_id, causing 72,000 hard parses/minute and 85% CPU usage on the database
server.
Fix: Modified application code to use bind variables (:user_id). Result: hard parses dropped to near-
zero, CPU dropped to 22%, response time improved 4x.

Case Study 2: Reporting Application Tuning

Problem: A BI application generated hundreds of variations of the same report query with literal date
ranges embedded. The Shared Pool was constantly evicting plans.
Fix: Parameterised all date ranges as bind variables (:start_date, :end_date). Library Cache hit rate
improved from 62% to 97%. Report generation time halved.

📋 SUMMARY
Oracle processes SQL in 4 phases: Parsing → Optimisation → Row Source Generation → Execution.
Hard Parse: SQL not in Library Cache — full cost, lexical + semantic analysis + optimisation.
Soft Parse: SQL found in Library Cache — plan reused, low cost.
The Library Cache (part of Shared Pool in SGA) stores parsed SQL and execution plans.
SQL Caching enables plan reuse, reducing CPU and memory overhead significantly.
Bind variables are the #1 technique for OLTP scalability — they prevent redundant hard parses.
Adaptive Cursor Sharing (11g+) handles skewed data by maintaining multiple plans per SQL.

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

PART C: Algorithms for Executing Query Operations

1. Definition of Query Execution Algorithms

Query execution algorithms are the specific computational methods used by the database engine to
implement relational operations (selection, projection, join, sort, aggregation, etc.) on physical data.
They define HOW each operation retrieves, filters, combines, or transforms data.

💡 KEY POINT
The Oracle Query Optimizer selects which algorithm to use for each operation in the execution plan
based on cost estimates (I/O + CPU + memory).
Understanding these algorithms allows DBAs and developers to write queries that Oracle can
execute optimally.

2. Algorithms for Selection Operations

Selection (σ) filters rows based on a predicate (WHERE clause). Oracle uses different algorithms
depending on the presence and type of indexes:

Algorithm Description Best Used When

Full Table Scan (FTS) Reads every block in the table No index, or >5-15% rows
sequentially via multi-block reads returned
Index Unique Scan Traverses B-Tree to single leaf Equality on primary/unique key
entry; returns 0 or 1 ROWID
Index Range Scan Traverses B-Tree; returns range Range predicates (<, >,
of ROWIDs in order BETWEEN)
Index Full Scan Reads entire index in order; ORDER BY on indexed column
avoids sort
Index Fast Full Scan Reads all index blocks using multi- COUNT(*) or indexed columns
block I/O; no order only
Index Skip Scan Skips leading column of Predicate on non-leading column
composite index
Bitmap Index Scan ANDs/ORs bitmap vectors; Low cardinality columns, DW
efficient multi-predicate filtering queries

-- 1. Full Table Scan (hint to force)

SELECT /*+ FULL(e) */ * FROM employees e WHERE salary > 50000;

-- 2. Index Unique Scan (automatic on primary key)

SELECT * FROM employees WHERE employee_id = 107;
-- Execution Plan: INDEX UNIQUE SCAN on EMP_EMP_ID_PK

-- 3. Index Range Scan

CREATE INDEX emp_salary_idx ON employees(salary);
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

-- Plan: INDEX RANGE SCAN on EMP_SALARY_IDX

-- 4. Bitmap Index Scan (data warehouse scenario)

CREATE BITMAP INDEX sales_region_bix ON sales(region_code);
CREATE BITMAP INDEX sales_status_bix ON sales(status);

-- Oracle combines bitmaps efficiently

SELECT COUNT(*) FROM sales
WHERE region_code = 'EAST'
AND status = 'COMPLETED';
-- Plan: BITMAP AND → BITMAP INDEX SINGLE VALUE (two indexes)

3. Algorithms for Projection Operations

Projection (π) selects specific columns from a result set. Oracle implements projection at the row
source level — only specified columns are carried through the execution plan.

Key Optimisations for Projection:

• Index-Only Scan: If all required columns are in an index, Oracle never accesses the table.
• Column-Level Storage: Oracle only reads required column blocks in columnar storage (Exadata
Hybrid Columnar Compression).
• Late Materialisation: Projection is deferred until necessary to avoid carrying unused columns
through joins.

-- Standard Projection
SELECT employee_id, last_name, salary -- Only 3 columns projected
FROM employees;

-- Index-Only Scan (covering index — no table access needed)

CREATE INDEX emp_cover_idx ON employees(department_id, salary);

-- This query only needs department_id and salary → index-only!

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;

-- Plan shows: INDEX FAST FULL SCAN (no TABLE ACCESS BY ROWID)
-- This is significantly faster — avoids random row reads

-- Verify with EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

4. Algorithms for Sorting

Sorting is required for ORDER BY, GROUP BY, DISTINCT, UNION, and many join operations. Oracle
uses an optimised two-pass external sort:

ORACLE SORT ALGORITHM

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

ORACLE SORT ALGORITHM

Input Data (unsorted rows)

│
▼
┌───────────────────────┐
│ PASS 1: Sort Runs │
│ • Read data into PGA │
│ • Sort in memory │
│ • Write sorted runs │
│ to temp tablespace │
└──────────┬────────────┘
│ Sorted Runs
▼
┌───────────────────────┐
│ PASS 2: Merge Runs │
│ • Merge sorted runs │
│ • If all fits in PGA │
│ → in-memory sort │
│ • Otherwise → disk │
└──────────┬────────────┘
│
▼
Sorted Output

Key: If sort fits in PGA → OPTIMAL (in-memory)

If exceeds PGA → ONEPASS (one disk write)
If very large → MULTIPASS (multiple disk writes)

-- Check sort operations in V$SQL_WORKAREA

SELECT operation_type, policy, estimated_optimal_size/1024 AS optimal_kb,
last_memory_used/1024 AS used_kb, last_execution
FROM v$sql_workarea
WHERE operation_type = 'SORT'
ORDER BY last_memory_used DESC;

-- last_execution values:
-- 'OPTIMAL' → Sort fit entirely in PGA (best)
-- 'ONEPASS' → One write to temp tablespace
-- 'MULTIPASS'→ Multiple writes (worst — tune PGA)

-- Adjust PGA for better sort performance

ALTER SYSTEM SET pga_aggregate_target = 512M;
-- Or let Oracle manage it:
ALTER SYSTEM SET pga_aggregate_limit = 2G;

-- Avoid unnecessary sorts

-- BAD: Forces sort
SELECT DISTINCT department_id FROM employees ORDER BY department_id;

-- BETTER: If index exists, Oracle can avoid the sort

CREATE INDEX emp_dept_idx ON employees(department_id);
SELECT department_id FROM employees ORDER BY department_id;
-- Plan: INDEX FULL SCAN (already ordered — no sort step!)

5. Algorithms for Duplicate Elimination

Duplicate elimination is triggered by DISTINCT, UNION (as opposed to UNION ALL), INTERSECT, and
MINUS. Oracle uses two primary approaches:

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

Method Algorithm When Oracle Chooses It

Sort-Based Sort all rows, then scan linearly to Smaller datasets, or when sort is
remove adjacent duplicates already needed
Hash-Based Hash each row into buckets; Larger datasets, sufficient PGA
within each bucket, compare for memory available
duplicates
Index-Based Use a unique or covering index to When index covers all projected
avoid duplicates at retrieval columns

-- DISTINCT triggers duplicate elimination

SELECT DISTINCT department_id FROM employees;

-- UNION removes duplicates (= UNION + DISTINCT)

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL avoids sort/hash dedup (much faster!)

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- INTERSECT (rows in both sets)

SELECT employee_id FROM employees
INTERSECT
SELECT employee_id FROM managers;

-- MINUS (rows in first set not in second)

SELECT employee_id FROM employees
MINUS
SELECT employee_id FROM terminated_employees;

-- Performance tip: Use UNION ALL + GROUP BY instead of UNION

-- when you control the data and know there are no duplicates

6. Join Algorithms
Joins are the most complex and performance-critical operations in relational query processing. Oracle
implements three primary join algorithms. The optimizer selects among them based on cost.

6.1 Nested Loop Join (NLJ)

The Nested Loop Join is the conceptually simplest join. For each row in the outer (driving) table, Oracle
scans the inner table to find matching rows.

NESTED LOOP JOIN

NESTED LOOP JOIN

OUTER TABLE (Driving) INNER TABLE (Probed)

┌──────────────────┐ ┌──────────────────┐

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

│ dept_id | name │ │ emp_id | dept_id │

├──────────────────┤ ├──────────────────┤
│ 10 | Sales │ ──────► │ 101 | 10 │ ✓ Match
│ | │ ──────► │ 102 | 20 │ ✗
│ | │ ──────► │ 103 | 10 │ ✓ Match
├──────────────────┤ └──────────────────┘
│ 20 | IT │ ──────► Scan inner again...
└──────────────────┘

Pseudo-code:
FOR each row R1 in OUTER table:
FOR each row R2 in INNER table:
IF R1.dept_id = R2.dept_id THEN
output (R1, R2)

Cost: O(|Outer| × |Inner|) without index

With index: O(|Outer| × log|Inner|) ← much better

-- Nested Loop Join example

-- Best: small outer table + index on inner table join column
SELECT d.department_name, e.last_name, [Link]
FROM departments d -- outer (small: 27 rows)
JOIN employees e ON d.department_id = e.department_id -- inner (index!)
WHERE d.location_id = 1700;

-- Force Nested Loop with hint

SELECT /*+ USE_NL(d e) */ d.department_name, e.last_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id;

-- Nested Loop is OPTIMAL when:

-- • Outer table is small (few driving rows)
-- • Inner table has an index on the join column
-- • Query uses FIRST_ROWS optimization goal
-- • OLTP workload (return first rows quickly)

6.2 Sort-Merge Join (SMJ)

The Sort-Merge Join sorts both input sets on the join key, then merges them by scanning both sorted
sets simultaneously. It does not require an index.

SORT-MERGE JOIN

SORT-MERGE JOIN

Step 1: Sort both inputs on join key

SORTED: departments SORTED: employees

┌──────────────────┐ ┌──────────────────┐
│ dept_id | name │ │ emp_id | dept_id │
├──────────────────┤ ├──────────────────┤
│ 10 | Acctg │ │ 104 | 10 │
│ 20 | IT │ │ 107 | 10 │
│ 30 | Sales │ │ 101 | 20 │
│ 40 | HR │ │ 102 | 30 │
└──────────────────┘ └──────────────────┘

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

Step 2: Merge with two pointers

Pointer A → dept_id=10 │ Pointer B → dept_id=10 → Match: output

Pointer A → dept_id=10 │ Pointer B → dept_id=10 → Match: output
Pointer A → dept_id=10 │ Pointer B → dept_id=20 → Advance A
Pointer A → dept_id=20 │ Pointer B → dept_id=20 → Match: output
...

Cost: O(|R| log|R| + |S| log|S|) (dominant cost = sorting)

Once sorted: merge is linear O(|R| + |S|)

-- Sort-Merge Join is chosen when:

-- • Both tables are large and neither has a suitable index
-- • The join result is large (many matching rows)
-- • Inequality join conditions (>, <, >=, <=)
-- • Input is already sorted (e.g., from an index or prior sort)

-- Force Sort-Merge Join with hint

SELECT /*+ USE_MERGE(e d) */ e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_id;

-- Sort-Merge advantage: handles non-equi joins

SELECT e1.last_name, [Link],
e2.last_name AS higher_paid
FROM employees e1
JOIN employees e2 ON [Link] < [Link] -- inequality join!
WHERE e1.department_id = 60;

-- Optimisation: if joining on an indexed column,

-- Oracle can skip the sort phase for that table

6.3 Hash Join

Hash Join is Oracle's most powerful join algorithm for large datasets in batch/analytical workloads. It
builds a hash table from the smaller input, then probes it with the larger input.

HASH JOIN

HASH JOIN

Phase 1: BUILD (smaller table → hash table in PGA)

departments (small - 27 rows) Hash Table (in memory PGA)

┌──────────────────┐ ┌─────────────────────────┐
│ dept_id | name │ hash(10)→ │ Bucket 3: [10, Acctg] │
│ 10 | Acctg │ hash(20)→ │ Bucket 7: [20, IT] │
│ 20 | IT │ hash(30)→ │ Bucket 1: [30, Sales] │
└──────────────────┘ └─────────────────────────┘

Phase 2: PROBE (larger table → look up in hash table)

employees (large - 107 rows) Probe hash table

┌──────────────────┐
│ emp_id | dept_id │ hash(10) → lookup Bucket 3 → MATCH!
│ 101 | 10 │ hash(20) → lookup Bucket 7 → MATCH!

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

│ 102 | 20 │ hash(50) → lookup → no match

└──────────────────┘

Cost: O(|R| + |S|) — linear scan of both tables

Requires: PGA memory for hash table
If hash table > PGA: spill to temp tablespace (slower)

-- Hash Join is automatically chosen by Oracle for:

-- • Large table joins (OLAP / batch)
-- • No suitable index on join columns
-- • Equi-joins only (=, IN)

-- Force Hash Join with hint

SELECT /*+ USE_HASH(e d) */ e.last_name, d.department_name,
SUM([Link]) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

-- Monitor Hash Join memory usage

SELECT operation_type, policy,
estimated_optimal_size/1024 AS optimal_kb,
last_memory_used/1024 AS used_kb,
last_execution -- OPTIMAL, ONEPASS, or MULTIPASS
FROM v$sql_workarea
WHERE operation_type = 'HASH JOIN'
ORDER BY last_memory_used DESC;

-- Parallel Hash Join (for very large tables)

SELECT /*+ PARALLEL(e,4) PARALLEL(d,4) USE_HASH(e d) */
d.department_name, COUNT(*), AVG([Link])
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

7. Join Algorithm Comparison

7.1 Detailed Comparison Table
Criterion Nested Loop Join Sort-Merge Join Hash Join
Basic Mechanism For each outer row, Sort both inputs, then merge Hash smaller table, probe
scan/probe inner table with larger
Join Type Equi-joins and Non-equi-joins Equi-joins and Non-equi-joins Equi-joins ONLY (=, IN)
Support (inequality)
Time Complexity O(|R| × |S|) no index; O(|R| O(|R| log|R| + |S| log|S|) O(|R| + |S|) build+probe
log|S|) with index
Memory Usage Very low (one row at a time) Medium (sort buffers for both High (build-side hash table in
tables) PGA)
I/O Pattern Random I/O (row-by-row Sequential I/O after initial sort Sequential I/O (scan both
inner probes) tables once)
Index Needed? Yes (on inner join column) for No (but speeds up sort No
performance phase)
Best Scenario Small outer + indexed large Pre-sorted data; inequality Large tables; data
inner; OLTP joins; medium tables warehouse; batch

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

Criterion Nested Loop Join Sort-Merge Join Hash Join

First Row Latency Very fast (returns first match Slow (must sort both inputs Slow (must build entire hash
quickly) first) table first)
Scalability Poor for large-large table Moderate — limited by sort Excellent — linear scaling
joins I/O
Parallel Execution Limited benefit Good Excellent
CPU Usage Low per row, high total for High (sorting is CPU- Moderate (hashing is CPU-
large sets intensive) efficient)
Temp Space None (usually) Yes (sort runs on temp Yes (if hash table > PGA)
tablespace)
Oracle Hint USE_NL(a b) USE_MERGE(a b) USE_HASH(a b)
Typical Use OLTP, primary key lookups Range joins, pre-sorted data OLAP, reporting, ETL, DW

7.2 Performance Decision Matrix

JOIN ALGORITHM SELECTION FLOWCHART

HOW ORACLE CHOOSES A JOIN ALGORITHM

Is it an equi-join (=)?
├─ NO → Sort-Merge Join (handles inequality)
└─ YES ↓

Is the outer table small (OLTP, few rows)?

├─ YES → Is there an index on inner join column?
│ ├─ YES → Nested Loop Join ✓ (fastest for OLTP)
│ └─ NO → Hash Join (or Sort-Merge if small enough)
└─ NO ↓

Are both tables large?

├─ YES → Is PGA memory sufficient for smaller table?
│ ├─ YES → Hash Join ✓ (best for large-large joins)
│ └─ NO → Sort-Merge Join (disk-friendly)
└─ Is data already sorted on join key?
├─ YES → Sort-Merge Join (skip sort phase)
└─ NO → Hash Join (Oracle's default for OLAP)

Additional factors:
• FIRST_ROWS goal → Prefer Nested Loop
• ALL_ROWS goal → Prefer Hash Join
• Parallel query → Prefer Hash Join

7.3 Memory and I/O Profile

Resource Nested Loop Sort-Merge Hash Join
RAM Required Minimal (1 row buffer) Sort buffer (PGA) × 2 Hash table = smaller
table size
Temp Tablespace Not used Used for sort runs Used if hash table
overflows PGA
Buffer Cache Hits High (random reads, Medium (sequential, Low per join (sequential
many cache misses) large scans) scans)
CPU per Row Low High (comparison during Medium (hash function

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

Resource Nested Loop Sort-Merge Hash Join

sort/merge) per row)
Disk I/O Total High if no index High (sort + merge Low (one full scan each
passes) table)
Best PGA Config Any Large sort_area_size Large hash_area_size /
pga_aggregate_target

8. Practical Scenarios
Scenario 1: Customer Order Lookup (OLTP — Nested Loop)
A customer service agent queries a single customer's recent orders. The CUSTOMERS table has 5
million rows, ORDERS has 50 million rows.
-- OLTP Query: Retrieve 1 customer's orders
-- Oracle will use Nested Loop: 1 customer row → index on ORDERS

-- Ensure index exists

CREATE INDEX orders_customer_idx ON orders(customer_id, order_date DESC);

-- Query
SELECT c.first_name, c.last_name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = :cust_id -- bind variable!
AND o.order_date >= SYSDATE - 90 -- last 90 days
ORDER BY o.order_date DESC;

-- Expected Plan:
-- INDEX UNIQUE SCAN on customers (PK)
-- NESTED LOOPS
-- TABLE ACCESS BY ROWID customers
-- INDEX RANGE SCAN on orders_customer_idx
-- TABLE ACCESS BY ROWID orders

-- This returns the first row in milliseconds

Scenario 2: Sales Performance Report (OLAP — Hash Join)

A business analyst runs a monthly sales report joining SALES (10M rows), PRODUCTS (500K rows),
and REGIONS (200 rows).
-- OLAP Query: Monthly sales summary
-- Oracle will use Hash Join for large-large table joins

SELECT r.region_name,
[Link],
TO_CHAR(s.sale_date, 'YYYY-MM') AS sale_month,
SUM([Link]) AS total_qty,
SUM([Link]) AS total_revenue,
COUNT(DISTINCT s.customer_id) AS unique_customers
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN regions r ON s.region_id = r.region_id
WHERE s.sale_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
GROUP BY r.region_name, [Link], TO_CHAR(s.sale_date, 'YYYY-MM')

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

ORDER BY r.region_name, sale_month;

-- Expected Plan:
-- SORT ORDER BY
-- HASH GROUP BY
-- HASH JOIN (sales + products result JOIN regions)
-- HASH JOIN (sales JOIN products)
-- PARTITION RANGE (sales — if partitioned by sale_date)
-- TABLE ACCESS FULL SALES
-- TABLE ACCESS FULL PRODUCTS
-- TABLE ACCESS FULL REGIONS

-- Enable parallel for large result sets

ALTER SESSION ENABLE PARALLEL DML;
SELECT /*+ PARALLEL(s,8) PARALLEL(p,8) */ ...

Scenario 3: Reconciliation Report (Sort-Merge Join)

A financial system reconciles bank transactions with internal records, joining on transaction amount
ranges (non-equi join).
-- Non-equi join → Sort-Merge Join is ideal
SELECT bt.transaction_id,
[Link] AS bank_amount,
ir.internal_id,
[Link] AS internal_amount
FROM bank_transactions bt
JOIN internal_records ir
ON [Link] BETWEEN [Link] * 0.99 AND [Link] * 1.01 -- range!
AND ir.value_date = bt.value_date
WHERE bt.value_date = DATE '2024-11-30'
ORDER BY [Link];

-- Sort-Merge is chosen because:

-- 1. Non-equi (range) join condition
-- 2. Both tables filtered to manageable sizes
-- 3. Result needs to be ordered (sort reused for ORDER BY)

📋 SUMMARY
Query execution algorithms define HOW each SQL operation is physically executed on data.
Selection: FTS, Index Unique Scan, Range Scan, Bitmap — Oracle chooses based on selectivity and
statistics.
Projection: Oracle projects only required columns; covering indexes enable index-only scans.
Sorting: Two-pass external sort; OPTIMAL (in-memory) is fastest; MULTIPASS (disk) is slowest.
Duplicate elimination: Sort-based or hash-based depending on data volume and PGA availability.
Nested Loop Join: Best for OLTP, small outer table + indexed inner table. Low memory, fast first row.
Sort-Merge Join: Best for inequality joins, pre-sorted data, medium-large tables.
Hash Join: Best for large-large equi-joins in OLAP/batch workloads. Linear complexity. High memory.
The CBO selects join algorithms by evaluating estimated cost (I/O + CPU + memory) using statistics.

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

QUICK REFERENCE: Oracle Performance Commands

Essential V$ Views for Query Tuning

View Purpose Key Columns
V$SQL All parsed SQL statements in sql_id, parse_calls, executions,
shared pool buffer_gets, disk_reads
V$SQL_PLAN Execution plans for cached SQL sql_id, operation, options, cost,
cardinality
V$SQL_MONITOR Real-time SQL execution sql_id, status, elapsed_time,
monitoring disk_reads
V$SQL_WORKAREA Memory usage for sort/hash operation_type, last_execution,
operations last_memory_used
V$SESSION Active sessions and current SQL sid, sql_id, status, wait_class
V$SGAINFO SGA component sizes name, bytes
V$LIBRARYCACHE Library cache hit statistics namespace, pins, reloads,
gethitratio
V$PARAMETER Database configuration name, value, description
parameters
DBA_INDEXES Index metadata index_name, table_name,
index_type, status
DBA_TAB_STATISTICS Table statistics for CBO num_rows, blocks, avg_row_len,
last_analyzed

Key Oracle Hints Reference

Hint Category Hint Syntax Effect
Join Method USE_NL(a b) Force Nested Loop Join between
tables a and b
Join Method USE_MERGE(a b) Force Sort-Merge Join
Join Method USE_HASH(a b) Force Hash Join
Access Path FULL(t) Force Full Table Scan on table t
Access Path INDEX(t idx_name) Force use of specific index
Access Path NO_INDEX(t) Prevent index usage
Join Order LEADING(a b c) Force join order: a first, then b,
then c
Parallelism PARALLEL(t, n) Execute with n parallel threads
Goal FIRST_ROWS(n) Optimise for returning first n rows
quickly
Goal ALL_ROWS Optimise for total throughput
(default)
Caching RESULT_CACHE Cache query result in Result

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL

Oracle Database — Advanced Query Processing Fundamentals | Page

Hint Category Hint Syntax Effect

Cache

— END OF STUDY NOTES —

Comprehensive Study Notes | Parts A, B & C | Oracle SQL & PL/SQL