In versions of Adaptive Server earlier than 11.9, dropping an index removed the distribution page for the index. As of version 11.9.2, maintaining column-level statistics is under explicit user control, and the optimizer can use column-level statistics even when an index does not exist. The delete statistics command allows you to drop statistics for specific columns.
If you create an index and then decide to drop it because it is not useful for data access, or because of the cost of index maintenance during data modifications, you must determine whether the:
Statistics on the index are useful to the optimizer.
Distribution of key values in the columns for this index are subject to change over time as rows are inserted and deleted.
If the distribution of key values changes, run update statistics periodically to maintain useful statistics.
This example deletes the statistics for the price column in the titles table:
delete statistics titles(price)
delete statistics removes rows only
from sysstatistics; it does not remove rows
from systabstats. You cannot delete the rows
in systabstats that described partition row
counts, cluster ratios, page counts, and so on. However, if you
use optdiag simulate statistics to add any simulated systabstats rows
to sysstatistics, then those rows are deleted.
