Index in Oracle

Application developer or DBA will consider (create) Index whenever they are facing performance issue. To improve performance, they create indexes on proactively. Practically, we will add index only after a performance issue has been faced. Oracle DB offers several types of index.

???B-Tree Index?- Common and Default

???Cluster Index - For cluster

???Hash Cluster Index - Hash Cluster

???Reverse Key Index - RAC applications

???????Bitmap Index - small set

???????Function based index - Pre-computed value

???? Domain Indexes - Specific to an application

Columns with these characteristics are suitable for indexing

???Unique column

???Wide range values

???Small range ( Bitmap Index)

In 19c - autonomous database -, Oracle provides automatic index. This has been managed by DBMS_AUTO_INDEX package. This management task will be taken care by database itself. It creates, rebuilds and drops indexes automatically.