Index and its Type in Oracle with Syntax

Index:-

CREATE INDEX XXC05_INDEX
ON
XXC05_TEST(ID);

Composite Index:- it is also called concatenated Index, is an index on multiple columns in a table.

CREATE INDEX XXC05_INDEX
ON
XXC05_TEST(ID,NAME);

Unique Index:-  Unique indexes guarantee that no two rows of a table have duplicate values in the key column or column.

NonUnique Index:-  NonUnique indexes permit duplicate value in a column of table.

Types of Index:

B-Tree Index:-

These indexes are the standard index type. They are excellent for primary key and highly-selective indexes

By default B-tree Index created when we create index.

Syntax:-    create index XXC05_INDEX on XXC05_TEST(id,name)

Bitmap Index:- The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.

Syntax:-    create bitmap index XXC05_INDEX on XXC05_TEST(id,name)

Unique Index:-

create unique index XXC05_INDEX on XXC05_TEST(id);

Function Based Index:-

create unique index XXC05_INDEX on XXC05_TEST (UPPER(id));

** Maxinum no of column use in Index:-    32

Rename Index:-

Alter index XXC05_INDEX
rename to XXC05_INDEX1;

Drop Index:- DROP INDEX INDEX_NAME;

Drop index XXC05_INDEX;

Advertisements