Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

CREATE TABLE employee
(
  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....?

share|improve this question

migrated from stackoverflow.com Jun 16 '13 at 11:24

This question came from our site for professional and enthusiast programmers.

    
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

5 Answers 5

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

share|improve this answer

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

share|improve this 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

share|improve this answer
    
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

Let say you do CREATE INDEX I01 ON EMPLOYEE ( EMPID ).
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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.