What is the Index in Database ?

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.

If a heap-organized table has no indexes, then the database must perform a full table scan to find a value. For example, without an index, a query of location2700 in the hr.departments table requires the database to search every row in every table block for this value. This approach does not scale well as data volumes increase.

 

http://docs.oracle.com/cd/E11882_01/server.112/e10713/indexiot.htm

An index: Is a schema object Is used by the Oracle server to speed up the retrieval of rows by using a pointer Can reduce disk I/O by using a rapid path access method to locate data quickly Is independent of the table that it indexes Is used and maintained automatically by the Oracle server

An Index is a method of faster retrieval and search of records as regarding to oracle so oracle creates B-tree indexes by default. If go deeply into Indexes so as we now about the books they have some index pages at the end of texture pages (topic pages) and some is going to search any information regarding any specific word or topic and the book contain more then500 hundred pages it means that this book has lot of records in it so it difficult to search for this specific word or topic on one by one page the solution for this search or the indexes on the end of that you can easily search it from there they or sorted alphabetically, so in the databases indexes work same.

A Database object that helps quickly find specific rows of data in the database by avoiding a Full Table Scan

suppose you have a record in a table for an employee id555

and index on employee id field of the table would contain

e.g

-

 

where first part would be the binary equivalent of the data value . in this case555.

The next chain is the location on the hard disk cylinder where this record is stored.

 

So when i say select * from table where empid=555;

oracle will look in the index first part , get to555 then read the next part and get location of data on hard disk and fetch data from there.

 

Thats why when we move tables around in a database the indexes are invalidated because the hard disk location part is no longer pointing to the right data.

 

 

• Create an index if you frequently want to retrieve less than15% of the rows in a large table. The percentage varies greatly according to the relative speed of a table scan and how the distribution of the row data in relation to the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.

• To improve performance on joins of multiple tables, index columns used for joins.

• Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. You can create or drop an index without affecting the base tables, database applications, or other indexes. Oracle automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.