Chapter 1. SQL Processing and I/O
1.1 SQL Parsing and Optimization
1.1.1 Structured, collective, and declarative query languages
1.1.2 SQL Optimization
1.1.3 SQL Optimizer
1.1.4 Implementation Plan and Costs
1.1.5 Optimizer Hints

1.2 SQL Sharing and Reuse
1.2.1 Soft parsing vs.
hard parsing
1.2.2 The Importance of Bind Variables

1.3 Data storage structure and I/O mechanism
1.3.1 Why SQL is Slow
1.3.2 Database storage structure
1.3.3 Block Unit I/O
1.3.4 Sequential Access vs.
random access
1.3.5 Logical I/O vs.
Physical I/O
1.3.6 Single Block I/O vs.
Multiblock I/O
1.3.7 Table Full Scan vs.
Index Range Scan
1.3.8 Cache Lookup Mechanism


Chapter 2.
Index Basics
2.1 Index Structure and Navigation
2.1.1 Preview Index Tuning
2.1.2 Index Structure
2.1.3 Index Vertical Search
2.1.4 Index Horizontal Search
2.1.5 Combined Index Structure and Search

2.2 Basic Index Usage
2.2.1 Using an index
2.2.2 Why Indexes Cannot Be Range Scanned
2.2.3 More important index usage conditions
2.2.4 Omitting sort operations using indexes
2.2.5 Column processing in the ORDER BY clause
2.2.6 Column processing in SELECT-LIST
2.2.7 Automatic type conversion

2.3 How to use the index extension function
2.3.1 Index Range Scan
2.3.2 Index Full Scan
2.3.3 Index Unique Scan
2.3.4 Index Skip Scan
2.3.5 Index Fast Full Scan
2.3.6 Index Range Scan Descending


Chapter 3.
Index Tuning
3.1 Minimizing table access
3.1.1 Table Random Access
3.1.2 Index Clustering Factor
3.1.3 Index Break-Even Point
3.1.4 Adding an index column
3.1.5 Read and process only the index
3.1.6 Index Structure Table
3.1.7 Cluster Table

3.2 Using partial range processing
3.2.1 Partial range processing
3.2.2 Implementing partial range processing
3.2.3 Principle of Performance Improvement by Partial Range Processing in OLTP Environments

3.3 Improving index scan efficiency
3.3.1 Index Search
3.3.2 Index Scan Efficiency
3.3.3 Access Conditions and Filter Conditions
3.3.4 Clustering by comparison operator type and column order
3.3.5 Inefficiency that occurs when the index leading column is not an equality (=) condition
3.3.6 Converting BETWEEN to IN-List
3.3.7 Using Index Skip Scan
3.3.8 Is the IN condition '='?
3.3.9 Comparison of BETWEEN and LIKE scan ranges
3.3.10 Inefficiency caused by overusing range search conditions
3.3.11 Comparison of the pros and cons of various option condition processing methods
3.3.12 Index configuration to reduce function call load

3.4 Index Design
3.4.1 Why index design is difficult
3.4.2 The two most important selection criteria
3.4.3 Criteria other than scan efficiency
3.4.4 Strategic Design Beyond Formulas
3.4.5 Adding a column to omit sort operations
3.4.6 Combined Index Selectivity
3.4.7 Removing duplicate indexes
3.4.8 Creating an index design diagram


Chapter 4.
Join Tuning
4.1 NL Join
4.1.1 Basic Mechanism
4.1.2 NL Join Execution Plan Control
4.1.3 Analysis of the NL Join Execution Process
4.1.4 NL Join Tuning Points
4.1.5 Summary of NL Join Features
4.1.6 NL Join Tuning Practice
4.1.7 NL Join Expansion Mechanism

4.2 Sort Merge Join
4.2.1 SGA vs. PGA
4.2.2 Basic Mechanism
4.2.3 Why Sort Merge Join is Fast
4.2.4 Main Uses of Sort Merge Join
4.2.5 Controlling Sort Merge Joins
4.2.6 Summary of Sort Merge Join Features

4.3 Hash Join
4.3.1 Basic Mechanism
4.3.2 Why Hash Joins Are Fast
4.3.3 Handling Large Build Inputs
4.3.4 Hash Join Execution Plan Control
4.3.5 Join Method Selection Criteria

4.4 Subquery Join
4.4.1 Why Subquery Transformation is Necessary
4.4.2 Subqueries and Joins
4.4.3 Views and Joins
4.4.4 Scalar Subquery Joins


Chapter 5.
Sort tuning
5.1 Understanding the sort operation
5.1.1 Sorting Process
5.1.2 Sort Operation

5.2 Writing SQL to prevent sorting
5.2.1 Union vs.
Union All
5.2.2 Using Exists

5.3 Omitting sort operations using indexes
5.3.1 Omit Sort Order By
5.3.2 Top N Queries
5.3.3 Finding minimum/maximum values
5.3.4 History Inquiry
5.3.5 Omit Sort Group By

5.4 Writing SQL to Use Small Sort Areas
5.4.1 Reducing sort data
5.4.2 Principle of reducing sort load for Top N queries
5.4.3 Sorting overhead when not a Top N query
5.4.4 Top N sort in analytic functions


Chapter 6. DML Tuning
6.1 Basic DML Tuning
6.1.1 Factors Affecting DML Performance
6.1.2 Database Calls and Performance
6.1.3 Using Array Processing
6.1.4 Bulk DML Tuning with Indexes and Constraint Release
6.1.5 Modifiable Join Views
6.1.6 Using the MERGE Statement

6.2 Utilizing Direct Path I/O
6.2.1 Direct Path I/O
6.2.2 Direct Path Insert
6.2.3 Parallel DML

6.3 DML Tuning Using Partitions
6.3.1 Table Partitions
6.3.2 Index Partitions
6.3.3 Tuning Bulk UPDATEs Using Partitions
6.3.4 Tuning Bulk DELETEs Using Partitions
6.3.5 Tuning Bulk INSERTs Using Partitions

6.4 Locks and Transaction Concurrency Control
6.4.1 Oracle Lock
6.4.2 Transaction Concurrency Control
6.4.3 Comparison of INSERT performance by numbering method


Chapter 7. SQL Optimizer
7.1 Statistical Information and Cost Calculation Principles
7.1.1 Selectivity and Cardinality
7.1.2 Statistical Information
7.1.3 Cost Calculation Principle

7.2 Understanding the Optimizer
7.2.1 Optimizer Types
7.2.2 Optimizer Mode
7.2.3 Factors Affecting the Optimizer
7.2.4 Optimizer Limitations
7.2.5 Developer Role
7.2.6 How to Study to Become a Tuning Expert

Appendix. SQL Analysis Tools
1.
Check the execution plan
2.
AutoTrace
3. SQL Trace
4. DBMS_XPLAN package
5.
Real-time SQL monitoring
6.
V$SQL

SQL tuning is not a technical field that can be mastered by memorizing certain formulas or secrets. It is most important to accurately understand the internal execution principles of SQL.
The author of the first and second volumes of 'Oracle Performance Enhancement Principles and Solutions', the best-selling books in the field of database performance, has recently published 'Friendly SQL Tuning' and emphasizes that in order to properly tune SQL, you must have at least a thorough understanding of the contents covered in this book.


This book is an introductory book on tuning that experts with firsthand experience in the world of SQL tuning can confidently recommend to their fellow developers.
If you understand the overall flow and context of SQL tuning through this book, you can easily learn about 'Oracle Performance Enhancement Principles and Solutions'.
The specific target audience is as follows, and it will be a must-read, especially for those preparing for the SQLP certification exam.


- Applicants for the nationally recognized SQLP qualification exam
- Developers and DBAs who want to get started with SQL tuning
- Advanced Database Programmer
- Database designer

If you have any questions about the book, please contact the author's internet cafe below.


DBian Forum http://www.dbian.net