Skip to main content
System administrators can invalidate internal statistics caches used by the SQL optimizer. The optimizer then immediately sends new requests to Foundation Nodes to repopulate statistics for the underlying data in tables. The System calculates two primary types of statistics. The system calculates Cardinality Distinct Estimates (CDEs) by performing a union operation on the information stored in the table on Foundation Nodes. Also, the system calculates Probability Density Functions (PDFs) by sampling the table data local to the Foundation Node. Then, the system performs a union operation on the CDEs and PDFs on SQL Nodes when stored in the statistics cache. Notably, the union operation for the PDF is not associative, and its implementation is not deterministic. The union operation for the CDE is associative, commutative, and deterministic. The use of these SQL statements in system administration settings is rare. Do not use these statements for day-to-day maintenance of Ocient clusters. Mostly, you can use these commands when large amounts of data have been loaded or removed from the Ocient System quickly, and the system administrator wants to recalculate statistics immediately rather than wait for the configured statistics cache refresh timer to reflect the changes. These syntaxes help to stabilize the optimizer for new data distributions quickly.

​
Invalidate Optimizer Statistics Cache

INVALIDATE STATS clears the entire optimizer statistics cache on all SQL Nodes. If a table has not undergone any row delta after the last statistics cache refresh, the Ocient System does not recalculate statistics by sampling the underlying table. PDF statistics might still change slightly due to non-deterministic union operations. If the table has undergone a row change, the Ocient System recalculates statistics from scratch. Query performance might be affected until the statistics cache repopulates, specifically for optimization time. For tables that have undergone a row delta, the optimizer temporarily uses the old statistics file again until the recalculated file is available. Syntax
SQL
INVALIDATE STATS
Example Clear the entire optimizer statistics cache on all SQL Nodes.
SQL
INVALIDATE STATS;

​
Invalidate All Base Statistics

The INVALIDATE STATS FILES SQL statement forcibly invalidates the CDE and PDF files of the Foundation Node for all tables regardless of changes in the row delta, which triggers the system to recalculate the statistics. The optimizer statistics cache continues to use its current value until the Foundation Nodes have finished recalculating the new statistics, after which the cache replaces the table entry with the new statistics. This operation should have minimal impact on query optimization times. Depending on the size of the system, the system might take minutes to hours to recalculate all statistics on the system fully, and for new statistics to populate in the optimizer cache. Syntax
SQL
INVALIDATE STATS FILES
Example Invalidate all statistics
SQL
INVALIDATE STATS FILES;

​
Invalidate All Base Statistics For a Specific Table

The ALTER table_name INVALIDATE STATS SQL statement forcibly invalidates the CDE and PDF files of the Foundation Node for the specified table, regardless of changes in the row delta, which triggers the system to recalculate the statistics. The optimizer statistics cache continues to use its current value until the Foundation Nodes have finished recalculating the new statistics, after which the cache replaces the table entry with the new statistics. This operation should have minimal impact on query optimization times. Syntax
SQL
ALTER TABLE table_name INVALIDATE STATS
ParameterData TypeDescription
table_namestringThe name of the table.
Example Force the system to recalculate the statistics for the table named test_table.
SQL
ALTER TABLE test_table INVALIDATE STATS;
Plan Optimization Query Performance Optimizations
Last modified on May 27, 2026