Updating statistics in oracle

This topic discusses statistics concepts and provides guidelines for using query optimization statistics effectively.

Statistics for query optimization are binary large objects (BLOBs) that contain statistical information about the distribution of values in one or more columns of a table or indexed view.

These correlation statistics, or , are derived from the number of distinct rows of column values.

A histogram measures the frequency of occurrence for each distinct value in a data set.

Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view.

The Query Optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold.

In more detail, SQL Server creates the histogram from the sorted set of column values in three steps: Density is information about the number of duplicates in a given column or combination of columns and it is calculated as 1/(number of distinct values).

The query optimizer uses densities to enhance cardinality estimates for queries that return multiple columns from the same table or indexed view.

If the histogram is created from a sampled set of rows, the stored totals for number of rows and number of distinct values are estimates and do not need to be whole integers.

The Query Optimizer uses these statistics to estimate the enable the Query Optimizer to create a high-quality query plan.

For example, depending on your predicates, the Query Optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, and in doing so improve query performance.

Each statistics object is created on a list of one or more table columns and includes a displaying the distribution of values in the first column.

Statistics objects on multiple columns also store statistical information about the correlation of values among the columns.

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating statistics in oracle”