# Costing and Selectivity

According to the **CPU costing model**:

Cost = ( #SRds * sreadtim + #MRds * mreadtim + #CPUCycles / cpuspeed ) / sreadtim where #SRDs - number of single block reads #MRDs - number of multi block reads #CPUCycles - number of CPU Cycles sreadtim - single block read time mreadtim - multi block read time cpuspeed - CPU cycles per second cardinality = num_rows * density.

Further Reading : Cost Based Optimizer (CBO) Overview (Doc ID 10626.1)

Commonly used terminology :

predicate =

A WHERE condition in SQL.

selectivity =

The first measure, selectivity, represents a fraction of rows from a row set. The selectivity of a predicate in a SQL statement is used to estimate the cost of a particular access path.

OR

Selectivity is a measure ranging between 0 and 1, simplistically defined as 1/NDV where NDV stands for Number of Distinct Values.

cardinality =

Cardinality represents the number of rows in a row set.

OR

Cardinality is defined as the number of rows expected to be fetched by a predicate or execution step.

OR

Cardinality is calculated as the number of rows in the table divided by the number of distinct values in the column.

OR

The cardinality of a predicate can be defined as the selectivity times the number of rows in the table.

cost =

The cost represents (and has always represented) the optimizer’s best estimate of the time it will take to execute the statement.

aggregate views / aggregation =

transformation =

Sometimes, the optimizer (Query Transformer) transforms one such statement into another that achieves the same goal if the second statement can be executed more efficiently.

instantiate =

histograms =

A histogram partitions the values in the column into bands, so that all column values in a band fall within the same range.

index fast fulll scan =

parallel execution =

extent boundaries blocks =

bind peeking =

stale statistics =

parse =

skew =