Skip to main content

FREEZE PLANS (SQL)

Freezes one or more query plans.

Synopsis

FREEZE PLANS BY ID statement-hash

FREEZE PLANS BY TABLE table-name

FREEZE PLANS BY SCHEMA schema-name

FREEZE PLANS

Arguments

statement-hash The internal hash representation of the SQL Statement definition for a query plan, enclosed in quotation marks. Occasionally, what appear to be identical SQL statements may have different statement hash entries. Any difference in settings/options that require different code generation of the SQL statement result in a different statement hash. This may occur with different client versions or different platforms that support different internal optimizations. Refer to SQL Statement Details.
table-name The name of an existing table or view. A table-name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name.
schema-name The name of an existing schema. This command freezes all query plans for all tables in the specified schema.

Description

The FREEZE PLANS command freezes query plans. To unfreeze frozen query plans use the UNFREEZE PLANS command.

FREEZE PLANS can freeze query plans with the Plan State Unfrozen. It cannot freeze query plans with the Plan State Unfrozen/Parallel.

FREEZE PLANS provides four syntax forms for freezing query plans:

  • A specified query plan: FREEZE PLANS BY ID statement-hash. The statement-hash value must be delimited by double quotation marks.

  • All query plans for a table: FREEZE PLANS BY TABLE table-name. You can specify a table name or a view name. If a query plan references multiple tables and/or views, specifying any of these tables or views freezes the query plan.

  • All query plans for all tables in a schema: FREEZE PLANS BY SCHEMA schema-name.

  • All query plans for all tables in the current namespace: FREEZE PLANS.

This command issues SQLCODE 0 if one or more query plans are frozen; it issues SQLCODE 100 if no query plans are frozen. The Rows Affected (%ROWCOUNT) indicates the number of query plans frozen.

Other Interfaces

You can use the following $SYSTEM.SQL.Statement methods to freeze a single query plan or multiple query plans: FreezeStatement() for a single plan; FreezeRelation() for all plans for a relation (a table or view referenced in the query plan); FreezeSchema() for all plans for a schema; FreezeAll() for all plans in the current namespace. There are corresponding Unfreeze methods.

You can use the Management Portal, to freeze a query plan as described in the Frozen Plans Interface section of the Frozen Plans chapter of the InterSystems SQL Optimization Guide.

See Also

Feedback