Updating statistics in oracle
To create the histogram, the query optimizer sorts the column values, computes the number of values that match each distinct column value and then aggregates the column values into a maximum of 200 contiguous histogram steps.
Each histogram step includes a range of column values followed by an upper bound column value.
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.
Note Frequency is information about the occurrence of each distinct value in the first key column of the statistics object, and is calculated as row count * density.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.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.For more information about the filter predicate, see CREATE STATISTICS (Transact-SQL).For more information about when to create filtered statistics, see the When to Create Statistics section in this topic.It applies strictly to single-column statistics for the full table.When the Query Optimizer creates statistics as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with SELECT OBJECT_NAME(s.object_id) AS object_name, COL_NAME(sc.object_id, sc.column_id) AS column_name, AS statistics_name FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id WHERE like '_WA%' ORDER BY s.name; When the automatic update statistics option, AUTO_UPDATE_STATISTICS is ON, the Query Optimizer determines when statistics might be out-of-date and then updates them when they are used by a query.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.