Skip to main content
Previous sectionNext section

UNFREEZE PLANS

Unfreezes one or more frozen query plans.

Synopsis

UNFREEZE PLANS [[FROM] UPGRADE] BY ID statement-hash

UNFREEZE PLANS [[FROM] UPGRADE] BY TABLE table-name

UNFREEZE PLANS [[FROM] UPGRADE] BY SCHEMA schema-name

UNFREEZE PLANS [[FROM] UPGRADE]

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 unfreezes all frozen query plans for all tables in the specified schema.

Description

The UNFREEZE PLANS command unfreezes frozen query plans. To freeze query plans use the FREEZE PLANS command.

UNFREEZE PLANS without the FROM UPGRADE clause unfreezes all query plans with the Plan State Frozen/Explicit. UNFREEZE PLANS with the FROM UPGRADE clause unfreezes all query plans with the Plan State Frozen/Upgrade. The FROM keyword in this clause is optional.

UNFREEZE PLANS provides four syntax forms for unfreezing query plans:

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

  • All query plans for a table: UNFREEZE 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 unfreezes the query plan.

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

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

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

Other Interfaces

You can use the following $SYSTEM.SQL.Statement methods to unfreeze a single query plan or multiple query plans: UnfreezeStatement() for a single plan; UnfreezeRelation() for all plans for a relation (a table or view referenced in the query plan); UnfreezeSchema() for all plans for a schema; UnfreezeAll() for all plans in the current namespace. There are corresponding Freeze methods.

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

See Also

FeedbackOpens in a new window