SQL - Drop index

Drop Index Statement

DROP> 表格用于删除或删除现有的数据库物体,如表格、索引、观点或程序。 每当我们使用DROP声明与数据库的任何物体一起时,它将永久清除这些物体及其相关数据。

因此,从数据库的表格中删除现有索引DROP INDEX。 使用了KQ指挥系统。 为了使用DROP INDEX指令,你需要具体说明你想要删除的特定指数的名称以及该指数与表格名称有关。 一旦你执行指挥,该指数就会从表上删除。

It is important to understand that dropping an index can have a significant impact on the performance of your database queries. Therefore, only try to remove an index if you are sure that it is no longer required.

我们不能删除由当代的基调或UNI Request制约因素造成的指数。

Syntax

The syntax of the DROP INDEX 指挥——Q

DROP INDEX index_name
ON table_name;

在这方面,

index_name is the name of the index that you want to drop.

table_name is the name of the table that the index is associated with.

Example

首先,让我们努力利用以下询问建立一个名为CUSTOMERS的表格:

SQL> CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 4),
   PRIMARY KEY(ID));
);

让我们利用以下询问,将一些价值观列入上述表格:

SQL> INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1,  Ramesh ,  32 ,  Ahmedabad , 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (2,  Khilan ,  25 ,  Delhi , 1500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (3,  kaushik ,  23 ,  Kota , 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (4,  Chaitap ,  25 ,  Mumbai , 6500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (5,  Hardik , 27 ,  Bhopal , 8500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (6,  Komal ,  22 ,  MP , 9000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (7,  Muffy ,  24 ,  Indore , 5500);

表格一经编制,让我们利用以下问询,在CUSTOMERS表中为“NAME”一栏编制索引。

SQL> CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

现在,让我们采用以下办法,列出在科罗塞罗群岛表格上建立的所有指数:

SQL> EXEC sys.sp_helpindex @objname = N CUSTOMERS ;

在执行上述询问时,产出如下:

+----------------------------------+---------------------+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| INDEX_NAME                       | nonclustered        | NAME             |
|                                  | located on PRIMARY  |                  |
| PK__CUSTOMER__3214EC27CB063BB7   | clustered, unique,  | ID               |
|                                  | primary key located |                  |
|                                  | on PRIMARY          |                  |
+----------------------------------+---------------------+------------------+

让我们在使用DROP INDEX的消费物价指数表中删除“NAME”栏。 指挥

SQL> DROP INDEX INDEX_NAME ON CUSTOMERS;

Output

如果我们收集和处理上述问题,结果如下:

Commands completed successfully.

Verification

Let’s verify whether the index for the column named “NAME” is dropped or not using the following query −

SQL> EXEC sys.sp_helpindex @objname = N CUSTOMERS ;

在你观察上述询问的结果时,你可以发现,从指数清单中删除“NAME”栏。

+----------------------------------+---------------------+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| PK__CUSTOMER__3214EC27CB063BB7   | clustered, unique,  | ID               |
|                                  | primary key located |                  |
|                                  | on PRIMARY          |                  |
+----------------------------------+---------------------+------------------+

DROP INDEX with IF EXISTS

DROP INDEX FI EXISTS > 说明中只有在表格中存在指数时才会采用。 当你想要放弃指数时,这一说法特别有用,但你无法确定指数是否存在。

IF EXISTS 条款确保,如果存在指数,则该表只能删除。 如果该指数不存在,则该指数只是终止该表。

Syntax

The syntax of the DROP INDEX FI EXISTS :

DROP INDEX IF EXISTS index_name
ON table_name;

在这方面,

index_name is the name of the index that you want to drop.

table_name is the name of the table that the index is associated with.

Example

采用先前编制的表格,让我们在表格中使用以下表格编制“NAME”栏索引:

SQL> CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

让我们利用以下问询,列出在消费物价指数表中制定的所有指数:

SQL> EXEC sys.sp_helpindex @objname = N CUSTOMERS ;

在执行上述询问时,产出如下:

+----------------------------------+---------------------+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| INDEX_NAME                       | nonclustered        | NAME             |
|                                  | located on PRIMARY  |                  |
| PK__CUSTOMER__3214EC27CB063BB7   | clustered, unique,  | ID               |
|                                  | primary key located |                  |
|                                  | on PRIMARY          |                  |
+----------------------------------+---------------------+------------------+

在建立指数之后,让我们利用以下询问,在消费物价指数表中降低的指数:

SQL> DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS;

Commands completed successfully.

SQL> EXEC sys.sp_helpindex @objname = N CUSTOMERS ;

+----------------------------------+---------------------+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| PK__CUSTOMER__3214EC27CB063BB7   | clustered, unique,  | ID               |
|                                  | primary key located |                  |
|                                  | on PRIMARY          |                  |
+----------------------------------+---------------------+------------------+

SQL> DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS;

Commands completed successfully.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

SQL> EXEC sys.sp_helpindex @objname = N CUSTOMERS ;

+----------------------------------+------------------- -+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| PK__CUSTOMER__3214EC27CB063BB7   | nonclustered        | ID               |
|                                  | located on PRIMARY  |                  |
+----------------------------------+---------------------+------------------+

SQL> ALTER TABLE customers
DROP CONSTRAINT PK__CUSTOMER__3214EC27CB063BB7;

Commands completed successfully.

SQL> EXEC sys.sp_helpindex @objname = N CUSTOMERS ;

The object  CUSTOMERS  does not have any indexes, or you do not have permissions.