Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Chapter 33 SQL DROP INDEX

Delete an index.

The outline

DROP INDEX index-name [ON [TABLE] table-name]

DROP INDEX table-name.index-name
Copy the code

parameter

  • index-name– The name of the index to be dropped.Index-nameIs the SQL version of the name, which can include underscores and other punctuation. It is listed in the table as the SQL map nameManagement Portal SQL Catalog DetailsIn the.
  • ON table-name 或 ON TABLE table-name– Optional – Name of the table associated with the index. Table names can be specified using either syntax: the first syntax is usedONClause;TABLEKeywords are optional. The second syntax uses the qualified name syntaxschema-name.table-name.index-name. Table names can be qualified (schema.table), can also be unqualified (Table). Unqualified table names take the default schema name. If you omit the table name entirely, IRIS will delete the first and foundindex-nameMatching indexes, as described below.

describe

The DROP INDEX statement drops the INDEX from the table definition. You can use DROP INDEX to DROP standard, bitmap, or bitslice indexes. You can use DROP INDEX to DROP unique or primary key constraints by dropping the corresponding unique INDEX. You cannot DROP a bitmap range INDEX or a primary map (data/primary)IDKEY INDEX using DROP INDEX.

You may want to drop an index for any of the following reasons:

  • Intended to perform a large number ofINSERT,UPDATEorDELETEOperation. Can be used for operations%noindexOption rather than accept the performance overhead of having each of these operations write to the index. Or, in some cases, it may be preferable to drop the index, make bulk changes to the database, and then recreate the index and populate it.
  • Indexes exist for fields or combinations of fields that are not used for query operations. In this case, the performance overhead of maintaining an index may not be worth it.
  • Fields or combinations of fields that contain a lot of duplicate data now have indexes. In this case, the minimal benefit of query performance may not be worth it.

If there is data in the table, the IDKEY index cannot be deleted. Attempting to do so generates a SQLCODE-325 error.

Permissions and lock

The DROP INDEX command is privileged. The user must have the %ALTER_TABLE administrative permission to execute DROP INDEX. % MSG user ‘name’ does not have %ALTER_TABLE permission SQLCODE-99 If you have the appropriate GRANT permissions, you can use the GRANT command to assign %ALTER_TABLE permissions to users or roles. Administrative permissions are namespace-specific.

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 permission on the table. Otherwise, the user must be granted the %ALTER privilege on the table. Otherwise it will result in a SQLCODE-99 error because % MSG user ‘name’ does not have the %ALTER privilege required to change the table definition of ‘schema.tablename’. You can determine whether the current user has %ALTER privileges by calling the %CHECKPRIV command. You can use the GRANT command to assign %ALTER permissions to specified tables.

  • Cannot be used on tables projected from persistent classesDROP INDEXUnless the table class definition includes[DdlAllowed]. Otherwise, the operation will fail and appearSQLCODE-300Error, and not a class'Schema. The tablename'To enable the%msg DDL.
  • DROP INDEXTables that cannot be used for projection from deployed persistent classes. This operation failed and occursSQLCODE-400Error and display%msg Unable to Execute DDLTo modify the deployed class:'the classname'.

The DROP INDEX statement obtains a table-level lock on table-name. This prevents other processes from modifying the table data. This lock is automatically released when the DROP INDEX operation ends.

The index name

When an index name is specified to create an index, the system removes all punctuation marks to generate the corresponding class index name. It reserves the index name specified in the class as the SqlName value (SQL mapping name) of the index. When specifying the index name to drop the index, you need to specify the name that contains punctuation marks, which are listed as SQL map names in the management portal SQL directory details of the table. For example, specify the name of the generated SQL map for the unique constraint (MyTable_UNIQUE2) instead of the index name (MYTABLEUNIQUE2). This index name is case insensitive.

The name of the table

You can use the DROP INDEX FORM to specify the table associated with the INDEX:

  • Index-name ON TABLE syntax: It is optional to specify the TABLE NAME. If omitted, IRIS will search all classes in the namespace for the corresponding index.

  • Table-name.index-name Syntax: The Table name needs to be specified.

In both syntax, Table names 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, IRIS searches all indexes for indexes that do not specify SqlName for the INDEX name, or SqlName for the INDEX name. If IRIS does not find a matching index in any of the classes, it generates a SQLcode-333 error indicating that no such index exists. If IRIS finds multiple matching indexes, DROP INDEX cannot determine which INDEX to DROP; It issues SQLCODE -334 error: “Index name is not clear. Find indexes in multiple tables. Index names in IRIS are not unique in each namespace.

A nonexistent index

By default, DROP INDEX emits a SQLCODE-333 error by default if you attempt to DROP an INDEX that does not exist. To determine the current Settings, please call $SYSTEM. SQL. CurrentSettings (), it shows a allows DDL discarding the index set does not exist. The default value is 0(no). This is the recommended setting. If set to 1(” Yes “), DROP INDEX that does not have an INDEX does nothing and does not issue an error message.

This option (and other similar create, change, and delete options) can be set system-wide by selecting the ignore redundant DDL statement check box in the administrative portal, system administration, configuration, SQL, and object Settings.

The name of the table

If an optional table-name is specified, it must correspond to an existing table.

  • If the specified table name does not exist, IRIS will issueSQLCODE-30Wrong, and will%msgSet to the table'SQLUser. Tname'Does not exist.
  • If the specified table name exists but has no nameindex-nameThe index of IRIS will be issuedSQLCODE-333Wrong, and will%msgSet to attempt to drop the tableSQLUSER.TNAMEIndexes on the'MyIndex'– Index not found.
  • If the table name specified is a view, IRIS will be emittedSQLCODE-333Wrong, and will%msgSet to attempt to delete the viewSQLUSER.VNAMEIndexes on the'EmpSalaryIndex'Failure. Indexes support tables only, not views.

The sample

The first example creates a table named Employee, which is used by all the examples in this section.

The following embedded SQL example creates an index named “EmpSalaryIndex” and then deletes it. Note that DROP INDEX does not specify the table associated with the INDEX; It assumes that “EmpSalaryIndex” is the unique index name in this namespace.

ClassMethod DropIndex(a)
{
	&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)) ) w ! ,"SQLCODE= ",SQLCODE,"Create a table" &sql( CREATE INDEX EmpSalaryIndex ON TABLE Employee ( Namelast,Salary ) ) w ! ,"SQLCODE=",SQLCODE,"Create indexes" n SQLCODE ,%msg &sql( DROP INDEX EmpSalaryIndex ) w ! ,"SQLCODE=",SQLCODE,"Remove the index" if (SQLCODE '=0 ) { w ! ,"message",%msg } }Copy the code

The following embedded SQL example uses the ON TABLE clause to specify the table associated with the index to be dropped:

/// d ##class(PHA.TEST.SQLCommand).DropIndex1()
ClassMethod DropIndex1(a)
{
	&sql(CREATE INDEX EmpVacaIndex
		ON TABLE Employee
		( NameLast,AccruedVacation )) w ! ,"SQLCODE=",SQLCODE,"Create indexes" &sql(DROP INDEX EmpVacaIndex ON TABLE Employee) w ! ,"SQLCODE=",SQLCODE,"Remove the index"}Copy the code

The following embedded SQL example uses qualified name syntax to specify the table associated with the index to be dropped:

ClassMethod DropIndex2(a)
{
	&sql(CREATE INDEX EmpSickIndex
		ON TABLE Employee
		( NameLast,AccruedSickLeave )) w ! ,"SQLCODE=",SQLCODE,"Create indexes" &sql(DROP INDEX Employee.EmpSickIndex) w ! ,"SQLCODE=",SQLCODE,"Remove the index"}Copy the code

The following command deletes a nonexistent index. It generates SQLCODE -333 error:

DROP INDEX PeopleIndex ON TABLE Employee
Copy the code