How to query all indexes in oracle

Method: 1. Use the "select*from user_indexes where table_name=table name" statement to query the indexes in the table; 2. Use the "select*from all_indexes where table_name=table name" statement to query all indexes.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query all indexes in oracle

View which indexes are in the table

The syntax is:

select * from user_indexes where table_name = '表名'

or

select * from all_indexes where table_name = '表名'

View the table Which columns the index corresponds to

select * from user_ind_columns where table_name='表名'

Extended knowledge:

The index information of the table in oracle exists in two tables, user_indexes and user_ind_columns,

among them

The user_indexes system view stores information such as the name of the index and whether the index is the only index.

The user_ind_columns summary view stores the index name, corresponding tables and columns, etc.

sql Example:

select* from all_indexes where table_name='ACM_NETWORK_OPERATION';
select * from user_ind_columns where table_name='ACM_NETWORK_OPERATION';

Create a simple index

SQL CREATE INDEX Syntax

Create a simple index on the table. Duplicate values ​​are allowed:

CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Create a unique index on the table. Duplicate values ​​are not allowed: a unique index means that two rows cannot have the same index value. Creates a unique index on a table. Duplicate values ​​are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Note: The syntax used to create an index is different in different databases. Therefore, check the syntax for creating indexes in your database.

CREATE INDEX Example

The following SQL statement creates an index named "PIndex" on the "LastName" column of the "Persons" table:

CREATE INDEX PIndex
ON Persons (LastName)

If you want To index more than one column, you can list the column names in parentheses, separated by commas:

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

Oracle's DROP INDEX syntax:

DROP INDEX index_name

Recommended tutorial: "Oracle Video Tutorial》

The above is the detailed content of How to query all indexes in oracle. For more information, please follow other related articles on the PHP Chinese website!