Oracle Database - Selectivity

Card Puncher Data Processing

About

The first measure of the plan_estimator, selectivity, represents a fraction of rows from a row set. The row set can be a base table, a view, or the result of a join or a GROUP BY operator.

<MATH> \text{Selectivity} = \frac{\text{Numbers of rows satisfying a predicate}}{\text{Total number of rows}} </MATH>

The selectivity is tied to a query predicate. The selectivity of a predicate indicates how many rows from a row set will pass the predicate test.

Selectivity lies in a value range from 0.0 to 1.0 :

  • 0.0 means that no rows will be selected from a row set,
  • 1.0 means that all rows will be selected.

If no statistics are available then the optimizer either uses:

  • dynamic sampling (if enabled)
  • or an internal default value, Different internal defaults are used, depending on the predicate type. For example, the internal default for an equality predicate (last_name = 'Smith') is lower than the internal default for a range predicate (last_name > 'Smith'). The estimator makes this assumption because an equality predicate is expected to return a smaller fraction of rows than a range predicate.

When statistics are available, the estimator uses them to estimate selectivity.

For example:

  • for an equality predicate (last_name = 'Smith'), selectivity is set to the reciprocal of the number n of distinct values of last_name, because the query selects rows that all contain one out of n distinct values. If a histogram is available on the last_name column, then the estimator uses it instead of the number of distinct values. The histogram captures the distribution of different values in a column, so it yields better selectivity estimates. Having histograms on columns that contain skewed data greatly helps the query optimizer generate good selectivity estimates.





Discover More
Card Puncher Data Processing
Oracle Database - Index Scans

The Index Scans is a access path used by the query optimizer to produce the best . In this method, a row is retrieved by traversing the index, using the indexed column values specified by the statement....
Card Puncher Data Processing
Oracle Database - Selectivity

The first measure of the plan_estimator, selectivity, represents a fraction of rows from a row set. The row set can be a base table, a view, or the result of a join or a GROUP BY operator. The selectivity...
Oracle Database Star Transformation
Oracle Database - Star Transformation

This article talk the application of the star query in Oracle which involve a star schema. Star transformation executes the query in two phases: retrieves the necessary rows from the fact table (row...
Card Puncher Data Processing
Oracle Database - Statistics - Density (Column Selectivity)

Density is a column statistics that store a decimal number between 0 and 1 that measures the selectivity of a column. Values close to 1 indicate that the column is Density providesselectivity estimates...
Histogram Height Balanced Uniform Distribution
Oracle Database - Statistics - Histogram (Column statistics)

Data Dictionary Column statistics may be stored as histograms. These histograms provide accurate estimates of the distribution of column data. Histograms provide improved selectivity estimates in the presence...
Query Optimizer Process
Oracle Database - The Query Plan Estimator

The estimator is involved in the query optimizer process. Its main task is to measure the plans that give the plan generator. It generates three different types of measures : These measures...
Scale Counter Graph
Performance

has two dimensions (two metrics): either the Time to do the task from start to finish (execution time, response time, latency) or the tasks per unit time (throughput, bandwidth) is a feature....
Data System Architecture
Relation - Bitmap Indexes

Normally in a btree, there is a one-to-one relationship between an index entry and a row: an entry points to a row. Indexes should be selective in general. With Bitmap Indexes, a single entry uses a bitmap...
Oracle Database Sql Processing
SQL Engine - (Access|Scan) (Paths|Method)

Access paths are relational operator (ways, techniques) that retrieves data from the database. scan In general, index access paths should be used for statements that retrieve a small subset of table...



Share this page:
Follow us:
Task Runner