|
Written by Administrator
|
|
Tuesday, 24 March 2009 15:06 |
Tables store data.
Indexes are used to speed up retrieval of data.
Imagine a table containing 1 million records. Lets say a program has requested the database to fetch records for all employees whose name is SCOTT. The database would have to start reading the table from row 1 to the last row until it finds all the records where name is SCOTT. This is called a full table scan.
Full table scan slows down the performance as it is time consuming.
Indexes are created to avoid the full table scan.
In this example you would create an index on column NAME of EMPLOYEE table.
Imagine index as a table that only contains one or two columns. The actual EMP table could have more than 10 column. The index on NAME column could be imagined as a table with one column NAME.
Now if you ask the database to retrieve all rows where NAME is SCOTT, first the database would visit this index. Index could be scans in a matter of seconds since it is a small object as oppose to the table.
Index has pointers pointing to the actual rows of the table.
First the database finds all desired values within the index.
Pointers could be used to retrieve other attributes of the entity residing in the actual table e.g. SALARY etc.
|
