ORACLE Indexes

What are Indexes in ORACLE, Type of indexes and Advantages/Disadvantages of Indexes?

• An index is a memory object basically used to speed up the performance of queries & allowing faster retrieval of records. Basically helps in improving the Execution Plan of the Query. To read more about Execution Plan - Click here.
• Index is a physical structure contains pointers to the data. To know how Indexes are stored in DB and improve Query Performance- Click here.
• An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Advantages of Indexes:

•   Avoid Full Table Scan and also used to avoid a table access altogether.
•   Indexes Speed up Select. Their use in queries usually results in much better performance.
•   They can be used for sorting. A post-fetch-sort operation can be eliminated.

Disadvantages of Indexes:

•  Indexes slow down INSERT, UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field. INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.
• Indexes take additional disk space. Space increases with the number of fields used and the length of the fields.

We have mainly two different Indexes

Implicit index and Explicit index.

IMPLICIT Index:

Whenever we create a column(s) with Primary key or Unique constraints, Oracle implicitly creates Normal index (Clustered Type Index)- This index is not Unique index. Why because those columns already having uniqueness because of Unique or Primary key constraints (NOT NULL).

Create a Table with Primary Key and Describe Table Structure-

SQL> CREATE TABLE IndexedTab
  2  (
  3  ID NUMBER PRIMARY KEY,
  4  NAME VARCHAR2(20),
  5  PhoneNo VARCHAR2(20),
  6  SALARY NUMBER,
  7  GENDER VARCHAR2(10),
  8  STATUS VARCHAR2(10)
  9  );

Table created.

SQL> DESC IndexedTab;
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                      NOT NULL NUMBER
 NAME                             VARCHAR2(20)
 PHONENO                          VARCHAR2(20)
 SALARY                           NUMBER
 GENDER                           VARCHAR2(10)
 STATUS                           VARCHAR2(10)

EXPLICIT Index:

Explicit Indexes are user defined. Explicit index created by "Create Index Command'

Unique Index

Composite Index  

B-TREE INDEX

BITMAP INDEX

FUNCTION-BASED INDEX

Unique Index

Unique Index is create using “Crete Unique Index” Statement.

NOTE- A constraint has different meaning to an index. It gives the optimizer more information and allows you to have foreign keys on the column, whereas a unique index doesn't.

SQL> CREATE UNIQUE INDEX INDX_PH ON IndexedTab(PHONENO);

Index created.

Composite Index

Two or more columns combined to form a composite index with higher selectivity. If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.

Composite indexes should be avoided as they are large in size and can be have a performance overhead.

SQL> CREATE INDEX INDX_FLNM ON IndexedTab(ID,NAME);

Index created.

Consider creating a composite index on columns that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either column individually. Consider indexing columns that are used frequently to join tables in SQL statements.

B-Tree and Bitmap Index

B-Tree Indexes Vs. Bitmap Indexes

B-tree Index	Bitmap Index
Good for high-cardinality data	Good for low-cardinality data
Good for OLTP databases (lots of updating)	Good for data warehousing applications
Use a large amount of space	Use relatively little space
Easy to update	Difficult to update

Internally, a bitmap and a B-Tree indexes are very different, but functionally they are identical in that they serve to assist Oracle in retrieving rows faster than a full-table scan.  The basic differences between b-tree and bitmap indexes include:

1:  Syntax differences:  The bitmap index includes the "bitmap" keyword.  The btree index does not say "bitmap".

2: Cardinality differences:  The bitmap index is generally for columns with lots of duplicate values (low cardinality), while b-tree indexes are best for high cardinality columns.

3: Internal structure differences:  The internal structures are quite different.  A b-tree index has index nodes (based on data block size), it a tree form:

B*Tree Indexes

In general B*Tree index would be placed on columns that were frequently used in the predicate of a query and expect some small fraction of the data from the table to be returned.

Index Creation

Create Index INDX_NM on TableNM(ColumnNM) Tablespace TB01;

Bitmap Indexes

Bitmap indexes are structures that store pointers to many rows with a single index key entry. In a bitmap index there will be a very small number of index entries, each of which point to many rows. Bitmap indexes are best used on low cardinality data, this is where the number of distinct items in the set of rows divided by the number of rows is a small number.

For example, a gender column may have M, F and NULL. If you had a table with 20000 rows you would find that 3/20000 = 0.00015, this would be an ideal candidate for a bitmap index. Other examples are Marital Status, Region and Level.

SQL> CREATE BITMAP INDEX INDX_GN ON IndexedTab(GENDER);

Index created.

An Oracle bitmap index would look like

Value/Row	1	2	3	4	5	6	7	8	9	10	11	12	13	14
Analyst	0	0	0	0	0	0	0	1	1	1	0	0	1	0
Clerk	1	0	0	0	0	0	0	0	0	0	1	1	0	1
Manager	0	0	0	1	0	1	1	0	0	0	0	0	0	0
President	0	0	0	0	0	0	0	0	1	0	0	0	0	0
Saleman	0	1	1	0	1	0	0	0	0	0	0	0	0	0

Using the above table, you can see that rows 1, 4, 6, 7, 11, 12 and 14 would represent a manager and clerk.

Index Creation

Create Bitmap Index INDX_JOB on Employee(Job_Desc) Tablespace TB01;

Let’s check below Example-

How many of our married customers live in the central or west regions?" This corresponds to the following SQL query:

SELECT COUNT(*) FROM customer  

WHERE MARITAL_STATUS = 'Married' AND REGION IN ('Central','West'); 

Bitmap indexes can process this query with great efficiency by merely counting the number of ones in the resulting bitmap. To identify the specific customers who satisfy the criteria, the resulting bitmap would be used to access the table.

Oracle stores ranges of rows for each bitmap as well, which is why bitmaps don’t do well when you update the bitmap-indexed column (as you can lock an entire range of rows).

Bitmap Indexes and Nulls

Bitmap indexes include rows that have NULL values, unlike most other types of indexes. Indexing of nulls can be useful for some types of SQL statements, such as queries with the aggregate function COUNT.

Example

SELECT COUNT(*) FROM emp; 

Any bitmap index can be used for this query because all table rows are indexed, including those that have NULL data. If nulls were not indexed, the optimizer would only be able to use indexes on columns with NOT NULL constraints.

FUNCTION-BASED INDEX

Function-Based indexes give the ability to index computed columns and use theses indexes in a query, it allows you to have case insensitive searches or sorts, search complex equations and to extend the SQL language by implementing your own functions and operators and then searching on them. The main reason to use them are:

• They are easy to implement and provide immediate value
• They can be used to speed up existing applications without changing the application code or queries.

Index Creation

Create Index INDX_NM on TableNM(FUNCTION(ColumnNM));

SQL> CREATE INDEX INDX_ST ON IndexedTab(UPPER(STATUS));

Index created.

What is Clustered and Non-Clustered Index?

Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. In case of non-clustered index, the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

Clustered indexes
Physically stored in order (ascending or descending)
Only one per table
When a primary key is created a clustered index is automatically created as well.
If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended.
Columns with values that will not change at all or very seldom, are the best choices.

Non-clustered indexes
Up to 249 nonclustered indexes are possible for each table or indexed view.
The clustered index keys are used for searching therefore clustered index keys should be chosen with a minimal length.
Covered queries (all the columns used for joining, sorting or filtering are indexed) should be non-clustered.
Foreign keys should be non-clustered.
If the table is under heavy data retrieval from fields other than the primary key, one clustered index and/or one or more non-clustered indexes should be created for the column(s) used to retrieve the data.

Use below Query to check the Table Indexes.                  

Select 

 IDX.INDEX_NAME,

 Substr(Col.Column_Name,1,20) Column_Name,

 IDX.INDEX_TYPE,

 IDX.TABLE_OWNER,

 IDX.TABLE_NAME,

 IDX.STATUS

      From User_Ind_Columns Col,

           User_Indexes Idx

      Where Idx.Table_name='INDEXEDTAB'

      And   Col.Table_name=Idx.Table_Name

      And   Col.Index_name = Idx.Index_Name

      Order By Idx.Index_Name,Col.Column_Position;

SQL> Select
  2   IDX.INDEX_NAME,
  3   Substr(Col.Column_Name,1,20) Column_Name,
  4   IDX.INDEX_TYPE,
  5   IDX.TABLE_OWNER,
  6   IDX.TABLE_NAME,
  7   IDX.STATUS
  8        From User_Ind_Columns Col,
  9             User_Indexes Idx
 10        Where Idx.Table_name='INDEXEDTAB'
 11        And   Col.Table_name=Idx.Table_Name
 12        And   Col.Index_name = Idx.Index_Name
 13        Order By Idx.Index_Name,Col.Column_Position;

INDEX_NAME                     COLUMN_NAME          INDEX_TYPE                  TABLE_OWNER                 TABLE_NAME                STATUS
------------------------------ -------------------- --------------------------- ------------------------------ ------------------------------ --------
INDX_FLNM                      ID                   NORMAL                      HR                          INDEXEDTAB                VALID
INDX_FLNM                      NAME                 NORMAL                      HR                          INDEXEDTAB                VALID
INDX_GN                        GENDER               BITMAP                      HR                          INDEXEDTAB                VALID
INDX_PH                        PHONENO              NORMAL                      HR                          INDEXEDTAB                VALID
INDX_ST                        SYS_NC00007$         FUNCTION-BASED NORMAL       HR                          INDEXEDTAB                VALID
SYS_C0013026                   ID                   NORMAL                      HR                          INDEXEDTAB                VALID

6 rows selected.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.