Oracle index management

Basic knowledge: Create Table test01 (Enno number (3), enname varchar (20), sex varchar (2), age Number (3) 1. index creation Syntax: create Index name on table name (column name 1, column name 2 ,.......) tablespace name; example: Create index ind_enno on test01 (Enno) tablespace system; create unique index: create unique index name on table name (column name 1, column name 2 ,......) example of table space name: create unique index ind_age on test01 (AGE) tablespace system Create Function Index: Create Index name on table name (function) Example: create index ind_sex on test01 (upper (sex) 2. query index select index_name, table_name from dba_indexes where table_name = 'test01'; 3. Delete index Syntax: drop index name example: drop index ind_enno; 4. Rename index Syntax: Alter index old index name Rename to new index name example: Alter index ind_enno Rename to ind_enno1 5. enable monitoring for monitoring indexes: alter Index name monitoring usage query usage: Select * from V $ object_usage; disable monitoring: Alter Index name nomonitoring usage example: Alter index ind_enno monitoring usage; query test01 table: select * From test01 where Enno = '1'; Alter index ind_enno nomonitoring usage; select * from V $ object_usage; index_name table_name mon use start_monitoring
End_monitoring syntax --- --------------------- ----------------------- ind_enno test01 Yes 02/27/2013 05:42:41 remarks: use indicates usage, Yes indicates use, no indicates not use 6, re-index Syntax: alter Index name rebuild example: Alter index ind_enno rebuild; Syntax: Alter Index name rebuild online example: Alter index ind_enno rebuild online; ============================ the following content is to reference others' articles ======================== ======================

Source: http://blog.csdn.net/hailang99/article/details/1827129

Objective: To better understand the content written by the author

Alter index... rebuild online mechanism

When we rebuild an index without the online option, Oracle directly reads the data of the original index. When we add the online option, Oracle directly scans the data in the table, so how to maintain the consistency of the index segment data? It is the data change from the start of creation to the completion of index creation...

From the moment the index starts to rebuild online, Oracle will first create a system temporary log table sys_journal_xxx. The structure is similar to the mlog $ _ table, through the internal trigger, records the changes in the table when the rebuild index starts. After the index is created, new data is directly written into the index, you only need to maintain the changes in the sys_journal_xxx log table to the index.

1. Drop & create, the current table cannot use this index, it may seriously affect the application, it can only be used when the application does not need this index, and alter index .. rebuild online does not have this restriction, but it only consumes dB resources and does not seriously affect applications.

2. Drop & create only occupies the space of one index, unlike alter index .. rebuild online will occupy the space of the New and Old indexes during the creation process. In environments with insufficient free space, you may only use drop & create

3. As to whether alter index... rebuild, or alter index... rebuild online uses index scan instead of table scan, it seems that different environments have different results. (In RBO status)
In my environment, alter index .. rebuild => index fast full scan
Alter index .. rebuild online ==> table full scan
The other is that alter index... rebuild/alter index... rebuild online uses table full scan.