What does the index contain?

Posted on 2011-05-01
Medium Priority
Last Modified: 2012-05-11
After we create an index, how it makes the query to run faster, and also what does the index contain?
Question by:sakthikumar

Accepted Solution

qasim_md earned 400 total points
ID: 35503268
LVL 42

Assisted Solution

dqmq earned 400 total points
ID: 35503271

In general, they work just like an index in a book.  Without an index, finding a specific record would involve reading page-by-page until you find the right one.  On average, you would read half the table to find a single record.  With an index, you can "lookup" the right page and then read only the one you want.  Usually, that's so much faster.

In reality, it's way more complicated than that.  First, there are different kinds of indexes and they don't all work the same.  Second, when queries are optimized, indexes are used to reduce the amount are not only used for faster access, they can also be used to reduce the number of records that it is necessary to access.

Indexes are all about reducing I/O:  for a little bit more IO to read the index, there can be a great, great reduction in IO to fetch the result set.  
LVL 23

Assisted Solution

OP_Zaharin earned 400 total points
ID: 35503718
- as we all know, indexing is one of the criteria to improve data retrieval. but how to know if the index that we've create improved the sql execution? in Oracle we can use the execution plan and SQL tuning advisor feature to analyze and optimize sql statements and index usage. in 11g, the execution plan has been enhance further and known as SQL Plan Management (SPM).

- i would suggest you to analyze your index using the SPM as explain further in the following Oracle documents:


Assisted Solution

htonkov earned 400 total points
ID: 35508631
simplest explanation how index helps quicky find a record in table is:

You have a table T with columns A, B and C; and you need to search table T by column A where that column has a specific value (e.g. 7)
Without index Oracle would have to look in each row of table to see value of column A and if is equal to 7.
However, if you create an index on column A, it would contain an **ORDERED** list of unique values of column A, with pointers to locations of records in table T where column A has a respective value.
Ordered sets of data can be quickly searched, to if you use index and search for value 7, you can quickly locate a list of rows in table where A=7, and retreive data of other columns, i.e. B and C

This explanation is simple and not completely accurate, but that is the core idea of index...

LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 35509719
Not sure what you are asking here but in addition to the above posts:

>>how it makes the query to run faster

It allows the Oracle Cost Based Optimizer (and other database engines that use them) to determine the rows to be returned by reading the minimal information from disk.

So, it can pull the rows from the index by reading more information from disk than it could if it had to read them all (Full Table Scan).

>>and also what does the index contain?

In the links above, learn what a BTree index is.  There are a few other index types but BTree is the most common.

In a nutshell in Oracle: Index values and the unique rowid of the Oracle row.  It's slightly more complicated than that but in tree fashion.

If you index 'lastname', think of a table with list name and a unique row identifier.  Is it quikcer to read every row in the table or quickly scan a much smaller version with just the lastname to get the rows when you ask "where lastname='SMITH'"?

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question