Index

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

Index in Oracle

Index:-
Index is a schema object. Index is a performance-tuning method. Using Index retrieval of data from database is fast.

Syntax:-
CREATE INDEX index_name
ON table_name(column_name1,column_name2,column_name3,…);

Rename an INDEX:-
Alter INDEX old_index_name
RENAME TO new_index_name;

Drop an INDEX:-
DROP INDEX index_name;

Type of Index:-

1. Normal Index(B-Tree Index)
2. Bitmap Index
3. Function-based Index

Normal Index:-
It is also called B-TREE Index, By Default B-TREE index is created. It manage data in tree form.

Bitmap Index:-
Bitmap index put on that column which contain low cardinality. Low Cardinality means more duplicate data and less unique data.

Function-Based Index:-
The index expression can be an arithmetic expression or an expression that contains a SQL function, PL/SQL function, package function.

Example of Normal Index:-

CREATE INDEX xxc05_vendor_id
ON xxc05_ap_supplier(vendor_id);

Example of Bitmap Index:-

CREATE BITMAP INDEX xxc05_emp_gender
ON xxc05_employees(gender);

Example of Functional Index:-

CREATE INDEX xxc05_vendor_id
ON xxc05_ap_supplier(UPPER(vendor_name));