Provide some examples of the different types of indexes available in Oracle. Also, what are some of the best practices with indexes in Oracle?

Other than the normal indexing options that are available, Oracle has some other indexing options available that we will discuss here. Let’s go through those options with some examples here.

Bitmap index example in Oracle

Oracle has what are called bitmap indexes, which are meant to be used on lowcardinality columns. A low cardinality column just means that the column has relatively few unique values. For example, a column called Sex which has only “Male” and “Female” as the two possible values is considered low cardinality because there are only two unique values in the column.




A bitmap index will create separate structures for each unique value of the column(s) – so in our example, there will be one structure for “Male” and another for “Female”. And each structure will contain the same number of rows as the table. For each row inside each of those structures there will be a binary bit of 0 or 1. A 0 means that in that row corresponding to the table the structure value is not present, but a 1 means that it is present. That is why it’s called a bitmap index.

When dealing with bitmap indexes, the RDBMS will actually use matrix algebra to find the rows that are being looked up.

Syntax for creating a bit map index in Oracle

Here’s an example of what the syntax would look like to create a bit map index in Oracle – note the use of the keyword BITMAP in the syntax below:

CREATE BITMAP INDEX IX_PEOPLE_NAME
ON PEOPLE (NAME);

Function based indexes in Oracle

Function based indexes are another indexing option available in Oracle.

Suppose we include a function in a SQL predicate. For our example, let’s use the LOWER function, which converts text to lower case. So, let’s say we have some SQL that looks like this – where NAME is a column in a table that is called PEOPLE:

WHERE LOWER(NAME) = 'joe smith';

Usually having a predicate that uses a function would mean that an index can not be used on that query. But, Oracle actually lets you create an index on a function. This means that you can actually create an index on the “LOWER(NAME)” function call, and then you can use that index for any query that uses the call to the function “LOWER(NAME)”.

Before you create a function based index, you should know that there are some prerequisites – so be sure to consult the Oracle documentation on this matter.

Syntax for creating a function based index in Oracle

When creating a function based index, you just specify the function call where you would normally specify the column name. So, here’s an example of what creating a function based index would look like in Oracle – assuming that the table name is PEOPLE, and the function is LOWER(NAME):

CREATE INDEX IX_NAME_LOWER
ON PEOPLE (LOWER(NAME));

Index Organized Tables in Oracle




Creating an index on the primary key of a table is generally considered a good idea. But some tables have very few columns, and if that is true, then what typically happens is that almost all of the data that’s in the table is duplicated in the index. Remember that an index will also store the column data for any columns on which the index is defined. So, for tables with very few columns, creating a normal index could be redundant.

So, Oracle actually has a solution to this problem, and let’s you store the entire table inside the index. This solution is known as an Index Organized Table, or IOT for short. So, an IOT is actually a table and a primary key index in one data structure.

Index Organized Tables versus Normal Tables

Normal, non-IOT tables, actually store the table rows in an unsorted order. But IOT’s on the other hand store table rows in a B-tree index structure, which is then sorted by the primary key of the table. Because an IOT is still a table, you can actually create more indexes on the IOT, but doing that may not make sense depending on your data situation.

Syntax for creating an index organized table in Oracle

Here’s what the syntax to create an IOT would look like in Oracle – assuming we are creating a table called People :

CREATE TABLE PEOPLE (
NAME VARCHAR(50),
ADDRESS VARCHAR(70), 
CONSTRAINT PK_NAME
  PRIMARY KEY(NAME))
ORGANIZATION INDEX;

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.

  • kshitija joshi

    Thanks a lot,very well explained.

  • rahul

    Oracle stores the data of the indexed column separately along with its row id for quick looking. If less number of columns like in the example above 2 or 3 then whole table is stored separately in the index along with indexed column.
    Now this is redundancy, same data stored in Table and index both places.

  • kshitija joshi

    Nice article for any beginner for Oracle db engine, Can you please brief more on “So, for tables with very few columns, creating a normal index could be redundant.” I did not understand how an index would be redundant?

  • Ash

    Super.. thanks. well written