SQL Tuning

Glossary

Proper words in proper places, make the true definition of a style.

—Jonathan Swift, Letter to a Young Clergyman

Aggregation

The summarization of details into a smaller number of summary datapoints (sums, counts, and averages, usually), usually using GROUP BY.

Anti-join

A correlation join applied to a NOT EXISTS-type subquery.

Apples-and-oranges tables

Tables that hold somewhat different, but related entity types within the same physical table.

B-tree index

A balanced, branched, sorted structure that allows the database to rapidly locate a row or set of rows that match conditions of the indexed column or columns.

Block

The smallest unit of physical storage or cached storage in a database. Blocks are usually just a few kilobytes in size and most often contain from less than one hundred rows to at most a few hundred rows.

Block buffer cache

The cache that stores recently used table and index blocks in shared memory for logical I/O, avoiding the need for physical I/O of the cached blocks. Any user’s SQL can read blocks into the block buffer cache, and any other user’s SQL automatically takes advantage of these cached blocks. See LRU caching.

Branch block

An index block that the database reaches from a root block or a higher branch block. The branch block, in turn, points to leaf blocks or lower branch blocks that contain entries in the desired range. See root block and leaf block.

Cache-hit ratio

The fraction of logical I/Os that avoid physical I/Os.

Cartesian join

A join between two ...