Indexes in Oracle

Hi guys, in this blog I am going to explain you about Indexes in Oracle.

Indexes in Oracle-

In Oracle Database 19c, as in previous versions, indexes play a crucial role in enhancing query performance by providing a faster access path to data. Here are some key aspects of indexes in Oracle 19c:

 

B-Tree Indexes-

B-Tree (Balanced Tree) indexes are the most common type of index in Oracle. They provide efficient access to data based on equality and range predicates.

B-Tree indexes are suitable for columns that are frequently used in WHERE clauses with equality conditions.

 

Bitmap Indexes-

Bitmap indexes are suitable for low-cardinality columns, where there are a small number of distinct values.

They are efficient for read-only databases or data warehouses with infrequent updates.

 

Function-Based Indexes-

Function-based indexes allow indexing on the result of a function or expression rather than a simple column.

Useful for indexing computed values or applying functions to indexed columns.

 

Domain Indexes-

Domain indexes are used for indexing structured data within user-defined data types, such as XMLType or spatial data types.

 

Reverse Key Indexes-

Reverse key indexes are designed to reduce block contention in certain situations by reversing the bytes of each indexed column.

They can be beneficial in scenarios with high insert rates.

 

Clustered Indexes-

Oracle Database does not support clustered indexes in the same way as some other database systems. However, you can use index-organized tables (IOTs) to achieve similar functionality.

 

Index-Organized Tables (IOT)-

An IOT is a table stored in a B-Tree index structure. The data is stored in the index itself, which can provide better performance for certain types of queries.

 

Invisible Indexes-

Indexes can be marked as "invisible," making them temporarily unavailable for use by the optimizer without dropping them. This can be useful for testing the impact of removing an index without actually dropping it.

 

Online Index Rebuild-

Oracle 19c supports online index rebuild operations, allowing you to rebuild an index without blocking concurrent DML operations on the table.

 

Automatic Indexing-

Oracle 19c introduced automatic indexing, a feature that automatically identifies and creates indexes on tables to improve query performance. It relies on machine learning algorithms to make index creation recommendations.

 

How to create an Index in Oracle-

You can first build the table in a specific database in Oracle.

In Oracle Database 19c, you can create an index using the CREATE INDEX statement. Here's a basic syntax for creating an index:

 

Syntax-

CREATE INDEX index_name

ON table_name (column1, column2, ...);

 

Here's an example of creating a simple index on a single column-

CREATE INDEX idx_employee_salary ON Abhishek1.employees (salary);

You can also create composite indexes on multiple columns-

CREATE UNIQUE INDEX idx_employee_id ON Abhishek1.employee(employee_id);

You can also create composite indexes on multiple columns-

Example-

CREATE INDEX idx_employee_name ON employees (last_name, first_name);

Additionally, Oracle 19c introduces automatic indexing, a feature that can automatically create and manage indexes based on the usage patterns of the database. If you prefer to let Oracle manage indexes automatically, you can explore this feature. Automatic indexing is typically enabled by default, but you can check the current configuration and status using the following queries:

 

-- Check if automatic indexing is enabled

SELECT parameter_value FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection' AND status = 'ENABLED';

 

-- Check the status of automatic indexing

SELECT task_name, status FROM dba_autotask_task WHERE task_name = 'auto_index';