Oracle Index

1. index is a database object that provides a fast and efficient way to look up and retrieve data from a table.
2. It allows you to quickly fetch the information without having to scan the entire table.
3. Indexes are crucial for improving the performance of SELECT queries but can slightly impact the performance of INSERT, UPDATE, and DELETE operations.
4. Oracle we can maintain an unlimited number of index on a table and 12c version we can maintain multiple indexes in a single column.

Benefits:

• Faster Data Retrieval
• Improved Query Performance
• Enhanced Joins
• Constraint Enforcement
• Reduced I/O Operations

types of Indexes:

• B-Tree Index
• Unique Index
• Composite index
• Reverse Key Index
• Descending Index
• Bitmap Index
• Function-Based Index

B-Tree Index:

B-Tree means Balanced Tree. It is the Default index on Oracle.
It organizes data in a tree structure where each node has multiple children.
It provides excellent retrieval performance for a wide range of queries.

Examples:

CREATE INDEX index_name ON table_name(column_name);
CREATE UNIQUE INDEX index_name ON table_name(column_name);
CREATE INDEX index_name ON table_name(column_name1,column2);
CREATE INDEX index_name ON table_name(column_name)Reverse;
CREATE INDEX index_name ON table_name(column_name DESC);

Bitmap Index:

1. Bitmap indexes are useful for columns with a small number of distinct values.
2. It stores bitmaps for each unique value in a column, indicating the true or false of that value in each row of a table.
3. This allows for fast and efficient filtering and aggregation operations on the data.
4. Bitmap indexes are commonly used in data warehousing environments.

Example:

CREATE BITMAP INDEX index_name ON table_name(column_name);

Function-Based Index:

1. function-based index computes the value of a function or expression involving one or more columns and stores it in the index.
2. The function used for building the index can be an arithmetic expression or an expression that contains a SQL function, user-defined PL/SQL function and package functions.
3. It’s very easy and provides immediate value.

Examples:

CREATE INDEX index_name  ON table_name (12 * salary * commission_pct, salary, commission_pct);
CREATE INDEX index_name ON table_name ( UPPER(column_name) );

create/alter index and allocate tablespace:

CREATE INDEX index_name ON table_name(column_name) tablespace tablespace_name;
alter index index_name tablespace tablespace_name;

How to rebulid the index:

alter index index_name rebuild;

rename the index:

alter index index_name rename to new_index_name;

analyze the index:

analyze index index_name validate structure;

drop the index:

drop index index_name;

Usability:

alter index index_name unusable;
alter index index_name rebuild;
select index_name,status from user_indexes where index_name='index_name';

Visibility:

alter index index_name invisible;
alter index index_name visible;
select index_name, index_type, visibility from user_indexes where index_name='index_name');

index Monitoring:

alter index index_name monitoring usage;
alter index index_name nomonitoring usage;

select * from v$object_usage;
select * from dba_indexes;
select * from dba_ind_columns;
select * from user_indexes;
select * from user_ind_columns;
select * from user_ind_partitions;
select * from all_indexes;
select * from v$object_usage;