Index Tablespace Concept, Recovery, Create Index

Contents:- Concept of index tablespace, Index Tablespace Recovery, Creating Index

Concept

A tablespace that contains only indexes may be recovered without performing a RECOVER Task.

Recovery Steps if a Datafile belonging to an index tablespace is lost

 1.   Drop the data file.

 2.   Drop the tablespace.

 3.   Re-create the index tablespace.

 4.   Re-Create the indexes that were in the tablespace.

Creating Index

SQL> Create index demo_index on demo(salary);

Options

PARALLEL

(NOPARALLEL is the Default):- when you use parallel option at that time multiple server process simultaneously used to create an index. So by using parallel option you can create index more fastly.        

NOLOGGING

Using this option index can be created faster, Because it creates a very minimal amount of redo log entries as a result of the creation process.

SQL> Alter index demo_index nologging/ logging;

Notes:- NOLOGGING can be overwritten, if you are using Data Guard or Force Logging at the database or tablespace level.

You can use Data Pump Export with the CONTENT=METADATA_ONLY parameter to create a dump file containing the SQL commands to re-create the index.