DROP INDEX (SQL)
DROP INDEX [IF EXISTS] [%NOJOURN] index-name [ON [TABLE] table-name] DROP INDEX [IF EXISTS] [%NOJOURN] table-name.index-name
|IF EXISTS||Optional — Suppresses the error if the command is executed on a nonexistent index. For further details, refer to the following section on nonexistent indices.|
|%NOJOURN||Optional — Suppresses journaling for the duration of the deletions. None of the changes made in any of the rows are journaled, including any triggers fired. If you perform a ROLLBACK after a statement with %NOJOURN, the changes made by the statement will not be rolled back. To use this option the user must have the corresponding %NOJOURN administrative privilege. Failing to set this privilege generates an SQLCODE -99 error.|
|index-name||The name of the index to be deleted. index-name is the SQL version of the name, which can include underscores and other punctuation. It is listed in the table’s Management Portal SQL Catalog Details as the SQL Map Name.|
orON TABLE table-name
|Optional — The name of the table associated with the index. You can specify the table-name using either syntax: The first syntax uses the ON clause; the TABLE keyword is optional. The second syntax uses the qualified name syntax schema-name.table-name.index-name. A table-name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name. If you omit the table-name entirely, InterSystems IRIS deletes the first index found that matches index-name, as described below.|
A DROP INDEX statement deletes an index from a table definition. You can use DROP INDEX to delete a standard index, bitmap index, or bitslice index. You can use DROP INDEX to delete a unique constraint or a primary key constraint by deleting the corresponding Unique index. You cannot use DROP INDEX to delete a Bitmap Extent index or a Master Map (Data/Master) IDKEY index.
You may wish to delete an index for any of the following reasons:
You intend to perform large numbers of INSERT, UPDATE, or DELETE operations on a table. Rather than accepting the performance overhead of having each of these operations write to the index, you can use the %NOINDEX option for the operation. Or, in certain cases, it may be preferable to delete the index, perform the bulk changes to the database, and then recreate the index and populate it.
An index exists for a field or combination of fields that are not used for query operations. In this case, the performance overhead of maintaining the index may not be worthwhile.
An index exists for a field or combination of fields that now contain large amounts of duplicate data. In this case, the minimal gain to query performance may not be worthwhile.
You cannot drop an IDKEY index when there is data in the table. Attempting to do so generates an SQLCODE -325 error.
Privileges and Locking
The DROP INDEX command is a privileged operation. The user must have %ALTER_TABLE administrative privilege to execute DROP INDEX. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %ALTER_TABLE privileges. You can use the GRANT command to assign %ALTER_TABLE privileges to a user or role, if you hold appropriate granting privileges. Administrative privileges are namespace-specific. For further details, refer to Privileges in Using InterSystems SQL.
The user must have %ALTER privilege on the specified table. If the user is the Owner (creator) of the table, the user is automatically granted %ALTER privilege for that table. Otherwise, the user must be granted %ALTER privilege for the table. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have required %ALTER privilege needed to change the table definition for 'Schema.TableName'. You can determine if the current user has %ALTER privilege by invoking the %CHECKPRIV command. You can use the GRANT command to assign %ALTER privilege to a specified table. For further details, refer to Privileges in Using InterSystems SQL.
DROP INDEX cannot be used on a table projected from a persistent class, unless the table class definition includes [DdlAllowed]. Otherwise, the operation fails with an SQLCODE -300 error with the %msg DDL not enabled for class 'Schema.tablename'.
DROP INDEX cannot be used on a table projected from a deployed persistent class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.
The DROP INDEX statement acquires a table-level lock on table-name. This prevents other processes from modifying the table’s data. This lock is automatically released at the conclusion of the DROP INDEX operation.
When specify an index-name to create an index, the system generates a corresponding class index name by stripping out any punctuation characters; it retains the index-name you specified in the class as the SqlName value for the index (the SQL map name). When you specify an index-name to DROP INDEX, you specify the name including the punctuation, which is listed in the table’s Management Portal SQL Catalog Details as the SQL Map Name. For example, you specify the generated SQL Map Name for a Unique constraint (MYTABLE_UNIQUE2), not the Index Name (MYTABLEUNIQUE2). This index-name is not case-sensitive.
You can specify the table associated with the index using either DROP INDEX syntax form:
index-name ON TABLE syntax: specifying the table name is optional. If omitted, InterSystems IRIS searches all of the classes in the namespace for the corresponding index.
table-name.index-name syntax: specifying the table name is required.
In either syntax, the table name can be unqualified (table), or qualified (schema.table). If the schema name is omitted, the default schema name is used.
If DROP INDEX does not specify a table name, InterSystems IRIS searches through all indices for an index SqlName matching index-name, or an index name matching index-name for indices where an SqlName is not specified for the index. If InterSystems IRIS finds no matching indices in any class, an SQLCODE -333 error is generated, indicating no such index exists. If InterSystems IRIS finds more than one matching index, DROP INDEX cannot determine which index to drop; it issues an SQLCODE -334 error: “Index name is ambiguous. Index found in multiple tables.” Index names in InterSystems IRIS are not unique per namespace.
By default, if you try to delete a nonexistent index, DROP INDEX issues an SQLCODE -333 error. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL DROP of non-existent index setting. The default is 0 (“No”). This is the recommended setting. If set to 1 (“Yes”) DROP INDEX for a nonexistent index performs no operation and issues no error message. For further details, refer to the SQL and Object Settings Pages listed in System Administration Guide.
From the Management Portal, System Administration, Configuration, SQL and Object Settings, SQL you can set this option (and other similar create, alter, and drop options) system-wide by selecting the Ignore redundant DDL statements check box.
The behavior of the predicate IF EXISTS takes priority over settings in the Management Portal and the configuration parameter file (CPF) which also govern DDL statementsOpens in a new tab. These settings return SQLCODE 0 and suppress the error silently. When IF EXISTS is specified, the command returns SQLCODE 1 along with a message.
If you specify the %NOJOURN keyword, then DROP INDEX suppresses journaling and disables transactions for the duration of the operation. To specify %NOJOURN, you must have %NOJOURN SQL administrative privileges, which you can set by using the GRANT command.
If you specify the optional table-name, it must correspond to an existing table.
If the specified table-name does not exist, InterSystems IRIS issues an SQLCODE -30 error and sets %msg to Table 'SQLUser.tname' does not exist.
If the specified table-name exists but does not have an index named index-name, InterSystems IRIS issues an SQLCODE -333 error and sets %msg to Attempt to DROP INDEX 'MyIndex' on table SQLUSER.TNAME failed - index not found.
If the specified table-name is a view, InterSystems IRIS issues an SQLCODE -333 error and sets %msg to Attempt to DROP INDEX 'EmpSalaryIndex' on view SQLUSER.VNAME failed. Indices only supported for tables, not views.
The first example creates a table named Employee, which is used in all of the examples in this section.
The following embedded SQL example creates an index named "EmpSalaryIndex" and later removes it. Note that here DROP INDEX does not specify the table associated with the index; it assumes that "EmpSalaryIndex" is a unique index name in this namespace.
&sql(CREATE TABLE Employee ( EMPNUM INT NOT NULL, NAMELAST CHAR(30) NOT NULL, NAMEFIRST CHAR(30) NOT NULL, STARTDATE TIMESTAMP, SALARY MONEY, ACCRUEDVACATION INT, ACCRUEDSICKLEAVE INT, CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM)) ) WRITE !,"SQLCODE=",SQLCODE," Created a table" &sql(CREATE INDEX EmpSalaryIndex ON TABLE Employee (Namelast,Salary)) WRITE !,"SQLCODE=",SQLCODE," Created an index" /* use the index */ NEW SQLCODE,%msg &sql(DROP INDEX EmpSalaryIndex) WRITE !,"SQLCODE=",SQLCODE," Deleted an index" WRITE !,"message",%msg
The following embedded SQL example specifies the table associated with the index to be dropped using an ON TABLE clause:
&sql(CREATE INDEX EmpVacaIndex ON TABLE Employee (NameLast,AccruedVacation)) WRITE !,"SQLCODE=",SQLCODE," Created an index" /* use the index */ &sql(DROP INDEX EmpVacaIndex ON TABLE Employee) WRITE !,"SQLCODE=",SQLCODE," Deleted an index"
The following embedded SQL example specifies the table associated with the index to be dropped using qualified name syntax:
&sql(CREATE INDEX EmpSickIndex ON TABLE Employee (NameLast,AccruedSickLeave)) WRITE !,"SQLCODE=",SQLCODE," Created an index" /* use the index */ &sql(DROP INDEX Employee.EmpSickIndex) WRITE !,"SQLCODE=",SQLCODE," Deleted an index"
The following command attempts to drop a nonexistent index. It generates an SQLCODE -333 error:
DROP INDEX PeopleIndex ON TABLE Employee
“Defining and Building Indices” chapter in SQL Optimization Guide
SQLCODE error messages listed in the InterSystems IRIS Error Reference