Skip to main content

Oracle Indexes

Indexes are optional data structures associated with Database tables. Indexes are logically & physically independent of the data in the associated table. They are independent database objects created to reduce disk I/O & speed up execution of SQL statements on a table. We can create many indexes on a table given that each index points to different column (in case of a single column) or unique combination of columns from a table.

All Indexes offer complementary performance functionalities based on their structure:

B-Tree indexes : B-Tree Indexes are stored as a conventional binary data structure in the database. Each branch node contains a key prefix to make a direction between two keys. Each Leaf Node is at same depth from the branch node and contain complete key value for each row & ROWID.

Features :

  • All leaf blocks of the tree are at same depth, resulting same performance for retrieval of any record from the indexed table.
  • B-Trees are stay balanced automatically.
  • Excellent performance is achieved for wide range of queries and are generally suitable for both small & large sized tables without performance degradation.

Syntax :

CREATE INDEX last_name_idx ON students (last_name);

Bitmap indexes : Bitmap Indexes are space efficient indexes that represent trade-off between Disk I/O and CPU usage. A bitmap value is stored for each key value instead of a list of ROWIDs. Each bitmap corresponds to a possible ROWID. A mapping function returns actual ROWID at runtime in the form of a Row-ID list, and these ROWID values are directly used to access the data row. So, there is a trade-off between space (using compressed indexes) and CPU usage (for processing decompression).

Features :

  • Bitmap indexes are generally used in data warehouses for executing bulk data operations.
  • They are space efficient, but need higher CPU usage in comparison to B-Tree Indexes.
  • Bitmap indexes are not suitable for OLTP applications with large number of concurrent operations modifying individual rows of data. They are efficient in querying large data.
  • Bitmap indexes are not useful when performing comparison operations, instead use them for logical operations on data (viz. AND, OR, NOT) or equality queries.

Syntax :

CREATE BITMAP INDEX last_name_idx ON students (last_name);

Bitmap Join indexes : As the name suggests, Bitmap join indexes are bitmap indexes defined using an equi-join condition between two or more tables. In bitmap-join index, indexed values comes from one table (Fact table) but bitmaps point to another table (Dimension table). Usually implemented in a data warehousing environment, they have similar features to bitmap indexes. Additionally, following considerations must be taken into account :

  • Bitmap-join indexes take much more time to build than a conventional bitmap index for a join is performed and bitmap value is generated.
  • Bitmap-join indexes should be applied on tables frequently used in tandem (using joins) with each other.

Syntax :

CREATE BITMAP INDEX last_name_idx ON students (math.marks) WHERE students.pk = math.fk;


Index-Organized Tables : An index organized table has a storage organization that is different from a conventional (heap-organized) table whose data is stored as an unordered collection. Data for an index organized table is stored in a B-Tree index structure in a primary key sorted manner. Along with the primary key column data, it stores the values for the non-key columns as well.

Features :

  • Access to rows is defined using logical ROWID.
  • Full-Index returns all rows.
  • Avoid additional block fetch for accessing non-key columns’ data.

Syntax :

CREATE TABLE students (
rollno number,
first_name varchar2(20),
last_name varchar2(20),
CONSTRAINT pk_stud_iot_index PRIMARY KEY (rollno) )
ORGANIZATION index
TABLESPACE spc_example_ts_01
PCTHRESHOLD 20 INCLUDING last_name;


Function based indexes : Function based indexes are based on functions, expressions that involve one or more columns in the table being indexed. A value of the function/expression is calculated and stored as the value for the index. They can be stored either as a B-Tree index or a bitmap index.

Features :

  • Function based indexes are beneficial for evaluating queries that involve functions in WHERE clauses. Example :

    • SELECT * FROM students WHERE substr(stud_name, 1, 5) = "PETER";

  • It’s easy & provides immediate values for expressions.
  • It provides additional functionality with little cost, if expensive functions/expressions need to be evaluated regularly from the indexed table.

Syntax :

CREATE INDEX fb_last_name_idx on students (UPPER(last_name));

Popular posts from this blog

iPod Nano 5G synchronization issues in Ubuntu

I was able to manage my iPod Nano using Ubuntu until now. When I recently upgraded to Ubuntu 11.04 Natty which was a clean install, a weird problem started. I could not synchronize my iPod using any application like Banshee, Rhythmbox, gtkpod, Amarok etc. On closer look, I realized that when I transferred any file, it was copied to the iPod but the database on iPod was not updated. None of the applications seemed to work and it was same error in each case. Since the iPod database was not updated, iPod could not see the file and was unable to play it. It was an iPod Nano generation 5, which comes with a video camera on the back side. To debug the issue, I executed Banshee in debug mode (banshee -debug on the terminal), and I noticed the following error when trying to transfer any file on iPod. ‘Failed to save iPod database – GLib.GException: Failed to generate sqlite database (in `libgpod-sharp)’ I searched for solutions on Internet but was confused at various solutions suggest...
Read more

Sydney Trip

I went to Sydney on a business trip for 2-3 weeks. Since, it was a business trip I was going to travel alone and miss my family. Therefore, I appreciated the short duration of the trip but it did not feel like a short one. Most of it was work only and some time for me during the two weekends. I had decided to make the most of available time instead of idling and feel more homesick. Also, I was going to stay in the CBD area so it was a big advantageous factor since not much effort was to be spent for sightseeing. The journey began with the longest flight I’ve ever been on (17 hours) with a stopover at Bangkok for 2 hours. I had a terrible time in the flight from Delhi to Bangkok; I was struggling a lot in the airplane seats and couldn’t sleep at all. However, the flight from Bangkok to Sydney was much better. On my flight from Bangkok to Sydney, an Australian lady was seated at the window seat on my right hand side. She asked me if I wanted to swap my seat with hers as I had starte...
Read more

Busy or Lazy?

Either of the two. But that’s been the case after my wedding. I’ve not done a blog post, neither I did anything on Facebook, Twitter, Google+ for that matter. Whatever! Better late than never. So, in order of priority, I wanted to write down a few important events that happened since my last post - We were blessed with a baby daughter – Naisha. Literal meaning of Naisha is ‘Special’ and she justifies this meaning completely. She’s beautiful, cranky and a fighter. She’s awesome! Naisha is one creature in the house. She just celebrated her first birthday dressed up as a Barbie, which was cute. All the day she’s running around the house and interpreting various things and objects in her unique style. I cannot understand much of it, but most of it appears very logical somehow. I switched from X to Y organization. It was a long pending decision for me and thankfully it worked out this time. It took its own time and I was recruited after long six months of interviewing process. A...
Read more