The statistics object is the major source of information about data distribution for the predicate. Without knowing the exact data distribution, the query optimizer cannot have cardinality estimation, which is the process of calculating number of rows to return by applying the predicate.
After creating the statistics for the column, the column becomes out-of-date after executing DML commands, such as INSERT, UPDATE, and DELETE, because these commands change data, thereby affecting data distribution. In this scenario, a statistics update is needed.
In highly active tables, statistics become outdated in maybe a few hours; for static tables, statistics become outdated maybe in a few weeks. The decision about out-of-date statistics totally depends on the DML statements executed on the table
Before we move back to the core subject and start writing down the script, it is mandatory to draw attention to some facts and also to look at flashbacks.
Till...