What happens exactly when I create an index on the empid column of this employee table

  empid number(5),
  ename varchar2(30),
  sal   number(10,2)

Let's assume the table contains the below data

empid   ename      sal
1       hema       10000
3       suma       10000
2       bheema     15000
4       bhama      20000
5       panama     25000

what exactly my doubt is what will be created and stored internally in index..... to improve the performance , whether sorted content of empid or some other information related to rows....?

  • Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! Then you don't need any of those messy <br> and &nbsp; tags, either! – marc_s Jun 16 '13 at 7:58
  • 1
    The Oracle documentation explains how indexes work and are stored. – Alex Poole Jun 16 '13 at 9:45

Indexes, their various types and internal structures, are described in the documentation.


In the 'Anatomy of an SQL Index' section at http://use-the-index-luke.com/sql/anatomy you should found the answer.


Indices can be implemented using a variety of data structures. Popular indices include balanced trees, B+ trees and hashes.

most likely a balanced tree will be created to improve select queries performance each node in the tree will contain the index columns and a rowid for a quick access to the tables data

  • do you meant to say that rowids will be stored in b*tree format – Vamsi Krishna Jun 16 '13 at 8:41
  • @VamsiKrishna yes i did – makc Jun 16 '13 at 8:52

Your table content will remain unchanged. Creation of index will register its structure visible in views USER_INDEXES, user_tab_columns, user_objects etc. Also segment is created in default tablespace (for simplification, we will forget case when no data in table and delayed segment creation is set) and visible in user_segments and user_extents. Inside segment is stored representation of tree structure containing empid values but also rowid. Rowid can be understand as pointer to table line. Tree consists of branch and leaf nodes. Only leaf nodes contain values, branch nodes serve to find leaf nodes.
By default, order is ascending.


I once needed to wrap my head around indexes and understand how they work, and found an article "Understanding Indexes" by rleishman to be very useful and intuitive. There are also other nice posts by this author.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy