Preface — Why is indexing a development task?
Anatomy of an Index — What does an index look like?
The Leaf Nodes — A doubly linked list
The B-Tree — It’s a balanced tree
Slow Indexes, Part I — Two ingredients make the index slow
The Where Clause — Indexing to improve search performance
The Equals Operator — Exact key lookup
Primary Keys — Verifying index usage
Concatenated Keys — Multi-column indexes
Slow Indexes, Part II — The first ingredient, revisited
Functions — Using functions in the
where
clauseCase-Insensitive Search —
UPPER
andLOWER
User-Defined Functions — Limitations of function-based indexes
Over-Indexing — Avoid redundancy
Bind Variables — For security and performance
Searching for Ranges — Beyond equality
Greater, Less and
BETWEEN
— The column order revisitedIndexing SQL
LIKE
Filters —LIKE
is not for full-text searchIndex Combine — Why not using one index for every column?
Partial Indexes — Indexing selected rows
NULL
in the Oracle Database — An important curiosityNULL
in Indexes — Every index is a partial indexNOT NULL
Constraints — affect index usageEmulating Partial Indexes — using function-based indexing
Obfuscated Conditions — Common anti-patterns
Dates — Pay special attention to
DATE
typesNumeric Strings — Don’t mix types
Combining Columns — use redundant
where
clausesSmart Logic — The smartest way to make SQL slow
Math — Databases don’t solve equations
Testing and Scalability — About hardware
Data Volume — Sloppy indexing bites back
System Load — Production load affects response time
Response Time and Throughput — Horizontal scalability
The Join Operation — Not slow, if done right
Nested Loops — About the N+1 selects problem in ORM
Hash Join — Requires an entirely different indexing approach
Sort-Merge Join — Like a zipper on two sorted sets
Clustering Data — To reduce IO
Index Filter Predicates Intentionally Used — to tune
LIKE
Index-Only Scan — Avoiding table access
Index-Organized Table — Clustered indexes without tables
Sorting and Grouping — Pipelined
order by
: the third powerIndexed Order By —
where
clause interactionsASC
/DESC
andNULL FIRST
/LAST
— changing index orderIndexed Group By — Pipelining
group by
Partial Results — Paging efficiently
Selecting Top-N Rows — if you need the first few rows only
Fetching The Next Page — The offset and seek methods compared
Window-Functions — Pagination using analytic queries
Insert, Delete and Update — Indexing impacts on DML statements
On my Own Behalf
I make my living from SQL training, SQL tuning and consulting and my book “SQL Performance Explained”. Learn more at https://winand.at/.
Execution plans — getting and reading database execution plans
Myth Directory — Common myth and misbeliefs
Example Schema —
CREATE
andINSERT
scriptsGlossary — Important terms briefly explained