Updating statistics in oracle

Posted by / 11-Sep-2017 19:18

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.There are three options that you can set that affect when and how statistics are created and updated. When the automatic create statistics option, AUTO_CREATE_STATISTICS is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan.These single-column statistics are created on columns that do not already have a histogram in an existing statistics object.The AUTO_CREATE_STATISTICS option does not determine whether statistics get created for indexes.

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.

The query optimizer computes a histogram on the column values in the first key column of the statistics object, selecting the column values by statistically sampling the rows or by performing a full scan of all rows in the table or 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 range includes all possible column values between boundary values, excluding the boundary values themselves.

The lowest of the sorted column values is the upper boundary value for the first histogram step.

updating statistics in oracle-28updating statistics in oracle-85updating statistics in oracle-37