Index Enhancements   «Prev 



ALTER INDEX Required keywords.
index_name Unique name for the index.
REBUILD/COALESCE Keyword for indicating desired maintenance operation.
ONLINE Required keyword to build the index while leaving the underlying table available.

Creating Oracle Index

You create an index via the create index command. When you designate a primary key or a unique column during table creation or maintenance, Oracle will automatically create a unique index to support that constraint. The full command syntax for create index uses the following format as follows:

create [bitmap | unique] index index
on table(column [,column]. . .) [reverse];

index must be a unique name and follow the naming conventions of Oracle columns. table is simply the name of the table on which the index will be established, and column is the name of the column being indexed. Bitmap indexes allow you to create useful indexes on columns with very few distinct values. The reverse keyword tells Oracle to reverse the bytes of the indexed value, which may improve the I/O distribution during the insert of many sequential data values. You can establish a single index on multiple columns by listing the columns one after the other, separated by commas. For the following examples, the BOOKSHELF_AUTHOR table was created without a primary key:
(Title VARCHAR2(100),
AuthorName VARCHAR2(50),
constraint TitleFK Foreign key (Title)
references BOOKSHELF(Title),
constraint AuthorNameFK Foreign key (AuthorName)
references AUTHOR(AuthorName));