An index is a simple concept. It is typically a listing of keywords accompanied by the location of information on a subject. To find information on indexes, for instance, you look up the word “indexes” in the index at the back of this book. It will give the number of the page you are reading now. The word “indexes” is the key, and the page numbers given point you to the location of discussions about indexes in this book.
Although you could find the information on indexes simply by reading through this book until you encountered the page with the information on it, this method would be slow and time-consuming. Because the index at the back of the book is in alphabetical order, you can quickly go to the appropriate spot in the index (without reading every entry) where “index” is found. If the word is not common, this is quicker than reading through the book from front to back. These same principles apply to Oracle indexes. For example, if you are searching for a particular book, you can query with a limiting condition on the TITLE column:
If BOOKSHELF does not have an index on the PUBLISHER column, Oracle has to read every row in the table until it finds all publishers that match the WHERE clause of your query. If the table is small, that may not cause a performance problem. As the table grows in size, the time required to return all the matching rows to the user may impact the application’s performance and the business process it supports.
To speed data retrieval, you can create an index on the PUBLISHER column. Then, when you execute the same query, Oracle first looks in the index, which is sorted, thus finding the publisher named “Scholastic” very quickly (Oracle doesn’t read every entry, but jumps directly within close vicinity of the name, much as you would in looking through the index of a book). The index entry then gives Oracle the exact location in the table (and on disk) of the row(s) for that publisher. The standard type of index in Oracle is called a B*-tree index, matching column values to their related ROWIDs.
Indexing an important column (one that’s likely to appear in a WHERE clause) generally speeds up Oracle’s response to a query. Indexing likewise speeds up queries where two tables are joined, if the columns that are related (by the WHERE clause) are indexed.
Simple and helpful article