In Chapter 2, Understanding Query Processing, we discussed how the Query Optimizer is a fundamental piece of the overall query processor. In this chapter, we will dig deeper into the core component of cost-based query optimization: the Cardinality Estimator (CE).
As the name suggests, the role of the CE is to provide fundamental estimation input to the query optimization process. For example, the cardinality of a table that contains the name of every living human on Earth today is about 7,600,000,000. But if a predicate is applied on this table to find only inhabitants of the United States of America, the cardinality after the predicate is applied is only 327,000,000. Reading through 7,600,000,000 or 327,000,000 records may result in different data-access operations, such as a full scan or a range scan in this case. As such, early knowledge...