A newer version of this documentation is available.

Delete Statistics

reference

Enterprise Edition

February 23, 2025

+ 12

You can use the UPDATE STATISTICS statement to delete statistics.

Purpose

The UPDATE STATISTICS statement provides a syntax which enables you to delete statistics for a set of index expressions, or for an entire keyspace.

Since the cost-based optimizer uses statistics for cost calculations, deleting statistics for a set of index expressions effectively turns off the cost-based optimizer for queries which utilize predicates on those expressions. Deleting all statistics for a keyspace turns off the cost-based optimizer for all queries referencing that keyspace.

Syntax

ebnf
ViewCopy
update-statistics-delete ::= ( 'UPDATE' 'STATISTICS' 'FOR'? |
                               'ANALYZE' ( 'KEYSPACE' | 'COLLECTION')? )
                               keyspace-ref delete-clause

For this syntax, UPDATE STATISTICS and ANALYZE are synonyms. The statement must begin with one of these alternatives.

When using the UPDATE STATISTICS keywords, the FOR keyword is optional. Including this keyword makes no difference to the operation of the statement.

When using the ANALYZE keyword, the COLLECTION or KEYSPACE keywords are optional. Including either of these keywords makes no difference to the operation of the statement.

keyspace-ref	Keyspace Reference
delete-clause	DELETE Clause

Keyspace Reference

ebnf
ViewCopy
keyspace-ref ::= keyspace-path | keyspace-partial

ebnf
ViewCopy
keyspace-path ::= ( namespace ':' )? bucket ( '.' scope '.' collection )?

ebnf
ViewCopy
keyspace-partial ::= collection

The simple name or fully-qualified name of the keyspace for which you want to delete statistics. Refer to the CREATE INDEX statement for details of the syntax.

DELETE Clause

ebnf
ViewCopy
delete-clause ::= 'DELETE' ( delete-expr | delete-all )

The DELETE clause enables you to provide a comma-separated list of index expressions for which you want to delete statistics, or to specify that you want to delete all statistics for the keyspace.

delete-expr	Delete Expressions
delete-all	Delete All Statistics

Delete Expressions

ebnf
ViewCopy
delete-expr ::= 'STATISTICS'? '(' index-key ( ',' index-key )* ')'

Constraint: if you used the UPDATE STATISTICS keywords at the beginning of the statement, you may not use the STATISTICS keyword in this clause.

Conversely, if you used the ANALYZE keyword at the beginning of the statement, you must include the STATISTICS keyword in this clause.

index-key

[Required] The expression for which you want to delete statistics. This may be any expression that is supported as an index key, including, but not limited to:

A SQL++ expression over any fields in the document, as used in a secondary index.
An array expression, as used when creating an array index.
An expression with the META() function, as used in a metadata index.

Delete All Statistics

ebnf
ViewCopy
delete-all ::= 'ALL' | 'STATISTICS'

Constraint: If you used the UPDATE STATISTICS keywords at the beginning of the statement, you must use the ALL keyword in this clause.

Conversely, if you used the ANALYZE keyword at the beginning of the statement, you must use the STATISTICS keyword in this clause.

Result

The statement returns an empty array.

Examples

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

Example 1. Delete statistics with UPDATE STATISTICS

sql++
ViewCopy
UPDATE STATISTICS FOR hotel
DELETE (city, country, free_breakfast);

Example 2. Delete statistics with ANALYZE

sql++
ViewCopy
ANALYZE KEYSPACE hotel
DELETE STATISTICS (city, country, free_breakfast);

This query is equivalent to the query in Example 1.

Example 3. Delete all statistics with UPDATE STATISTICS

sql++
ViewCopy
UPDATE STATISTICS FOR airport DELETE ALL;

Example 4. Delete all statistics with ANALYZE

sql++
ViewCopy
ANALYZE KEYSPACE airport DELETE STATISTICS;

This query is equivalent to the query in Example 3.