Statistics Cache Management

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 {{ocient}} 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 sytem 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 invalidate stats example clear the entire optimizer statistics cache on all sql nodes 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 invalidate stats files example invalidate all statistics 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 alter table table name invalidate stats parameter data type description table name string the name of the table example force the system to recalculate the statistics for the table named test table alter table test table invalidate stats; related links database, tables, views, and indexes docid\ a4jvhkvg31tapexr9zpcq plan optimization docid\ tfuzyuqm7pkebt8zw4lnn query performance optimizations docid 8budnmvko7ll rtran50y