How to modify index in oracle

How to modify the index in oracle: 1. Use the "alter index old index name rename to new index name;" statement to rename the index; 2. Use the "drop index" statement to delete the original index, and then use "CREATE" INDEX" statement creates an index with the same name as needed.

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

Explanation

1) Index is one of the database objects, used to speed up data retrieval, similar to the index of a book. Indexing in the database can reduce the amount of data that needs to be read when the database program queries the results, similar to how in books we can use indexes to find the information we want without having to read the entire book.

　2) Index is an optional object built on the table; the key to the index is to replace the default full table scan retrieval method with a set of sorted index keys, thereby improving retrieval efficiency

　3) The index is logically and physically independent of the related tables and data. When creating or deleting an index, it will not affect the basic table;

　4) Once the index is created, it will be performed on the table During DML operations (such as when performing insertion, modification or deletion related operations), Oracle will automatically manage the index, and index deletion will not have an impact on the table

　5) The index is transparent to the user, no matter what is on the table Whether there is an index, the usage of the sql statement remains unchanged

　6) Oracle will automatically create an index on the column when creating the primary key

Index principle

1. If there is no index, when searching for a certain record (for example, searching for name='wish'), you need to search all records, because there is no guarantee that there is only one wish, and you must search them all

2 . If an index is created on name, Oracle will perform a search on the entire table, arrange the name value of each record in ascending order, then build the index entry (name and rowid), and store it in the index segment. When the query name is wish You can directly find the corresponding place

3. Creating an index does not necessarily mean it will be used. Oracle automatically counts the information of the table and decides whether to use the index. When there is very little data in the table, the speed of full table scan is already very high. Fast, there is no need to use index

Index usage (create, modify, delete, view)

1. Create index syntax

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express]) [TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

2. Modify the index

1) Rename the index

alter index index_sno rename to bitmap_index;

2) Merge the index (after the table is used for a period of time, fragmentation will occur in the index. At this time The index efficiency will be reduced. You can choose to rebuild the index or merge the index. The merged index method is better, does not require additional storage space, and the cost is lower)

alter index index_sno coalesce;

3) Rebuild the index

Method 1: Delete the original index, and then create an index with the same name as needed.

Method 2:

alter index index_sno rebuild;

3. Delete the index

drop index index_sno;

4. View the index

select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename'; -- eg: 
create index index_sno on student('name'); select * from all_indexes where table_name='student';

Recommended tutorial: "OracleTutorial》

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