UNFREEZE PLANS (SQL)

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]

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()Opens in a new tab for a single plan; UnfreezeRelation()Opens in a new tab for all plans for a relation (a table or view referenced in the query plan); UnfreezeSchema()Opens in a new tab for all plans for a schema; UnfreezeAll()Opens in a new tab 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.

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.

Security and Privileges

The UNFREEZE PLANS command is a privileged operation that required the user to have %Development:USE permission. Such permissions can be granted through the Management Portal. Executing a UNFREEZE PLANS command without this privileges will result in a SQLCODE -99 error and the command will fail. There are two exceptions:

The command is executed via Embedded SQL, which does not perform privilege checks.
The user explicitly specifies not privilege checking by, for example, calling either %Prepare() with the checkPriv argument set to 0 or %ExecDirectNoPriv() on a %SQL.StatementOpens in a new tab.