Contents

About the Authors

About the Technical Reviewer

Acknowledgments

Chapter 1: Core SQL

The SQL Language

Interfacing to the Database

Review of SQL*Plus

Connect to a Database

Configuring the SQL*Plus Environment

Executing Commands

The Five Core SQL Statements

The SELECT Statement

The FROM Clause

The WHERE Clause

The GROUP BY Clause

The HAVING Clause

The SELECT List

The ORDER BY Clause

The INSERT Statement

Single-Table Inserts

Multitable Inserts

The UPDATE Statement

The DELETE Statement

The MERGE Statement

Summary

Chapter 2: SQL Execution

Oracle Architecture Basics

SGA: The Shared Pool

The Library Cache

Identical Statements

SGA: The Buffer Cache

Query Transformation

Query Blocks

View Merging

Subquery Unnesting

Join Elimination

ORDER BY Elimination

Predicate Pushing

Query Rewrite with Materialized Views

Determining the Execution Plan

Executing the Plan and Fetching Rows

SQL Execution: Putting It All Together

Summary

Chapter 3: Access and Join Methods

Full Scan Access Methods

How Full Scan Operations Are Chosen

Full Scans and Throwaway

Full Scans and Multiblock Reads

Full Scans and the High-Water Mark

Index Scan Access Methods

Index Structure

Index Scan Types

Index Unique Scan

Index Range Scan

Index Full Scan

Index Skip Scan

Index Fast Full Scan

Join Methods

Nested Loops Joins

Sort–Merge Joins

Hash Joins

Cartesian Joins

Outer Joins

Summary

Chapter 4: SQL Is about Sets

Thinking in Sets

Moving from Procedural to Set-Based Thinking

Procedural vs. Set-Based Thinking: An Example

Set Operations

UNION and UNION ALL

MINUS

INTERSECT

Sets and Nulls

NULLs and Unintuitive Results

NULL Behavior in Set Operations

NULLs and GROUP BY and ORDER BY

NULLs and Aggregate Functions

Summary

Chapter 5: It’s about the Question

Asking Good Questions

The Purpose of Questions

Categories of Questions

Questions about the Question

Questions about Data

Building Logical Expressions

Summary

Chapter 6: SQL Execution Plans

Explain Plan

Using Explain Plan

The Plan Table

Breaking Down the Plan

Understanding How EXPLAIN PLAN Can Miss the Mark

Reading the Plan

Access and Filter Predicates

Reading the Plan as a Narrative

Execution Plans

Viewing Recently Generated SQL

Viewing the Associated Execution Plan

Collecting the Plan Statistics

Identifying SQL Statements for Later Plan Retrieval

Understanding DBMS_XPLAN in Detail

Using SQL Monitor Reports

Using Plan Information for Solving Problems

Summary

Chapter 7: Advanced Grouping

Basic GROUP BY Usage

HAVING Clause

“New” GROUP BY Functionality

CUBE Extension to GROUP BY

Putting CUBE to Work

Eliminate NULLs with the GROUPING( ) Function

Extending Reports with GROUPING( )

Extending Reports with GROUPING_ID( )

GROUPING SETS ( ) and ROLLUP( )

GROUP BY Restrictions

Summary

Chapter 8: Analytic Functions

Overview

Example Data

Anatomy of Analytic Functions

List of Functions

Aggregation Functions

Aggregate Function over an Entire Partition

Granular Window Specifications

Default Window Specification

lead and lag

Syntax and Ordering

Example 1: Returning a Value from a Prior Row

Understanding That Offset Is in Rows

Example 2: Returning a Value from an Upcoming Row

first_value and last_value

Example: first_value to Calculate Maximum

Example: last_value to Calculate Minimum

Other Analytic Functions

nth_value

rank

dense_rank

row_number

ratio_to_report

percent_rank

percentile_cont

percentile_disc

NTILE

stddev

listagg

Performance Tuning

Execution Plans

Predicates

Indexes

Advanced Topics

Dynamic SQL

Nesting Analytic Functions

Parallelism

PGA Size

Organizational Behavior

Summary

Chapter 9: The MODEL Clause

Spreadsheets

Interrow Referencing Via the MODEL Clause

Example Data

Anatomy of a MODEL Clause

Rules

Positional and Symbolic References

Positional Notation

Symbolic Notation

FOR Loops

Returning Updated Rows

Evaluation Order

Row Evaluation Order

Rule Evaluation Order

Aggregation

Iteration

An Example

PRESENTV and NULLs

Lookup Tables

NULLs

Performance Tuning with the MODEL Clause

Execution Plans

Predicate Pushing

Materialized Views

Parallelism

Partitioning in MODEL Clause Execution

Indexes

Subquery Factoring

Summary

Chapter 10: Subquery Factoring

Standard Usage

WITH Using a PL/SQL Function

Optimizing SQL

Testing Execution Plans

Testing the Effects of Query Changes

Seizing Other Optimization Opportunities

Applying Subquery Factoring to PL/SQL

Recursive Subqueries

A CONNECT BY Example

The Example Using an RSF

Restrictions on RSF

Differences from CONNECT BY

Duplicating CONNECT BY Functionality

The level Pseudocolumn

The SYS_CONNECT_BY_PATH Function

The CONNECT_BY_ROOT Operator

The connect_by_iscycle Pseudocolumn and NOCYCLE Parameter

The connect_by_isleaf Pseudocolumn

Summary

Chapter 11: Semijoins and Antijoins

Semijoins

Semijoin Plans

Controlling Semijoin Plans

Controlling Semijoin Plans Using Hints

Controlling Semijoin Plans at the Instance Level

Semijoin Restrictions

Semijoin Requirements

Antijoins

Antijoin Plans

Controlling Antijoin Plans

Controlling Antijoin Plans Using Hints

Controlling Antijoin Plans at the Instance Level

Antijoin Restrictions

Antijoin Requirements

Summary

Chapter 12: Indexes

Understanding Indexes

When to Use Indexes

Choice of Columns

The Null Issue

Index Structural Types

B-tree indexes

Bitmap Indexes

Index-Organized Tables

Partitioned Indexes

Local Indexes

Global Indexes

Hash Partitioning vs. Range Partitioning

Solutions to Match Application Characteristics

Compressed Indexes

Function-Based Indexes

Reverse Key Indexes

Descending Indexes

Solutions to Management Problems

Invisible Indexes

Virtual Indexes

Bitmap Join Indexes

Summary

Chapter 13: Beyond the SELECT

INSERT

Direct Path Inserts

Multitable Inserts

Conditional Insert

DML Error Logging

UPDATE

Using CTAS vs. UPDATE

Using INSERT APPEND vs. UPDATE

DELETE

MERGE

Syntax and Usage

Performance Comparison

Summary

Chapter 14: Transaction Processing

What Is a Transaction?

ACID Properties of a Transaction

Transaction Isolation Levels

Multiversion Read Consistency

Transaction Control Statements

Commit

Savepoint

Rollback

Set Transaction

Set Constraints

Grouping Operations into Transactions

The Order Entry Schema

The Active Transaction

Using Savepoints

Serializing Transactions

Isolating Transactions

Autonomous Transactions

Summary

Chapter 15: Testing and Quality Assurance

Test Cases

Testing Methods

Unit Tests

Regression Tests

Schema Changes

Repeating the Unit Tests

Execution Plan Comparison

Instrumentation

Adding Instrumentation to Code

Testing for Performance

Testing to Destruction

Troubleshooting through Instrumentation

Summary

Chapter 16: Plan Stability

Plan Instability: Understanding the Problem

Changes to Statistics

Changes to the Environment

Changes to the SQL

Bind Variable Peeking

Adaptive Cursor Sharing

Statistics Feedback

About SQL Plan Directives

Identifying Plan Instability

Capturing Data on Currently Running Queries

Reviewing the History of a Statement’s Performance

Aggregating Statistics by Plan

Looking for Statistical Variance by Plan

Checking for Variations around a Point in Time

Summary

Chapter 17: Plan Control

Plan Control: Solving the Problem

Modifying Query Structure

Making Appropriate Use of Literals

Giving the Optimizer Some Hints

Plan Control: Without Access to the Code

Option 1: Change the Statistics

Option 2: Change Database Parameters

Option 3: Add or Remove Access Paths

Option 4: Apply Hint-Based Plan Control Mechanisms

Outlines

SQL Profiles

SQL Plan Baselines

SQL Patches

Hint-Based Plan Control Mechanisms Wrap-up

Summary

Chapter 18: Miscellaneous SQL Constructs

Conditional Logic Constructs

Using DECODE

Using CASE

Using NVL, NVL2, and COALESCE

Using NULLIF

PIVOT/UNPIVOT Queries

Using PIVOT

Using UNPIVOT

SQL to Generate Test Data

What to Watch out For

Using CONNECT BY

Using the MODEL Clause

Using the Recursive WITH Clause

Data Generator Wrap-up

Summary

Index