Speaking At DOAG 2018 Conference And IT Tage 2018 - I will be speaking at the yearly DOAG conference in December as well as at the IT Tage in November. My talk will be "Oracle Optimizer System Statistics Upd...
Acum o zi
|How To Quickly Add/Remove Column Statistics (Histograms) For A Column [ID 390249.1]|
Applies to:Oracle Server - Enterprise Edition - Version: 184.108.40.206 to 220.127.116.11 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.
In some situations it is necessary to quickly add or remove histograms from a particular column (also known as column statistics.)
This article shows how to accomplish this with one command.
The commands given in this article will not affect column statistics currently gathered for other columns, they will only touch the specified column.
Column statistics can be:
- so called minimal consisting of just 1 bucket (2 endpoints) with min/max value information required by the Optimizer
- histograms with between 2 and 254 buckets (3 and 255 endpoints respectively)
- absent with no column information recorded in the data dictionary (not normally recommended)
Assuming you are connected as the owner of the table.
To add column statistics use one of the following varieties:
To delete histograms from a column leaving behind base column statistics i.e. minimal information required for the Optimizer (this is for pre-Oracle11g versions:)
To completely erase all types of column statistics/histograms for a column including minimal statistics:This is generally not recommended as the Optimizer needs at least the minimal (size 1) information.
In Oracle11g there is new syntax to delete the histogram for a column while still leaving behind the base column statistics:
Additionally there is functionality to delete histograms for a partition:
and to delete column histograms for the table and all its partitions: