Working with Indexes in an Oracle database

Of course we all know what an Index is and how it is used. We are already familiar with its usage while reading books of large page volumes. We also know how ridiculous it would look to index for example, a 5 page booklet.

Well indexing also applies to tables in Oracle database and very much every other databases pout there.

Indexes are objects in the database that provides a mapping of all the values in a table column, along with the ROWIDs for all rows in the table that contains the value for the column.

A ROWID is a unique identifier for a row in an oracle database table. An example of a rowid is the matriculation number of a student in a particular school.

Hence Indexes however are used to make searches on tables faster. It is mostly not needed on a small table as its effect will not be seen.

There are 2 types of indexes:

·         B*Tree

·         Bitmap

Let’s look at them one by one

Please note that this tutorial will be done using SQLPLUS command line tool

Click here to view tutorial using LiveSQL

B*Tree Index

This is in form of a binary tree and it’s the default index type. It is mostly used on tables with high cardinality.

High cardinality refers to columns with unique values. Examples of columns with high cardinality are:

·         User names

·         Email Addresses

·         account number

·         Staff id

In other words all values that can never have duplication in their sequences

The diagram above is constructed to make searches faster.

Let me now show you how it’s used.

Exercise

Use B*Tree to search for TAYO

Solution

This is how B*tree would do the search

·         For T it would drill down like this  

A-Z, N-Z, T-Z, T-V

·         For A it would drill down like this

A-Z, A-M, A-F, A-C

·         For Y it would drill down like this 

A-Z, N-Z, T-Z, W-Z

·         For O it would drill down like this 

A-Z, N-Z, N-S, N-P

Observe that we drilled down from 26 characters to a minimum of 3 characters.

Now it would search through just 3 characters thus speeding things up.

Bitmap Index

This type of index has a bitmap for each distinct value indexed and each bit position represents a row that may or may not contain index value.

I see the bitmap index as complex lookup table, having rows that correspond to all unique data values in the columns being indexed.

This is best for columns with low cardinality. Examples are

·         Male or Female,

·         Single or Married,

·         Graduate or Undergraduate

·         Staff or Non-Staff etc.

Let’s have a closer look at how the bitmap index really works.

Consider the following table

We have created a bitmap index on the sex column. Let’s see how it works:

A bitmap index table will automatically be created for the table as thus:

Each row in the table being indexed adds only a bit to the size of the bitmap pattern column for the bitmap index, so growth of the table will affect the size of the bitmap table. However each distinct value adds another column to the bitmap index, which adds another entire bitmap pattern with one bit for each row in the table so please be careful how you add new distinct values.

This is why it’s is said that the bitmap index works better with distinct values of low cardinality.

It should be noted that B*tree indexes are created by default.

Creating an index on a table.

Now let us create an index on a table with an exercise

Exercise

1.    Log on to the database as user HR

2.    Create a table called PAYDAY

3.    Populate the table using the data in the PAYDAY table above. Save your inserts

4.    Create a B*tree index on the name column of the PAYDAY table

5.    Create a Bitmap index on the sex column of the PAYDAY table

Solution

1.    Write the following query to connect to HR

SQLPLUS HR/HR

2.    Write the following query to create the PAYDAY table:

Create table PAYDAY

(id number(3), name varchar2(20), sex varchar2(6), salary number(9));

3.    Write the following query to insert into the PAYDAY table:

Insert into PAYDAY

values (001,'TAYO','male',400000);

Insert into PAYDAY

values (002,'DEOLA','female',250000);

Insert into PAYDAY

values (003,'TUNDE','female',300000);

Insert into PAYDAY

values (004,'EMMANUEL','male',300000);

Insert into PAYDAY

values (005,'AFI','female',310000);

Insert into PAYDAY

values (006,'NIKE','female',250000);

Insert into PAYDAY

values (007,'RUTH','female',200000);

4.    To create a B*tree index, write the following query:

CREATE INDEX NAME_IDX

ON PAYDAY (NAME);

5.    To create a Bitmap index, write the following query:

CREATE BITMAP INDEX SEX_IDX 

ON PAYDAY (SEX);

And that’s it about basic management of Indexes. I hope this post has been interesting as much as it was informative.

Stay tuned for more.