• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

What does the index contain?

After we create an index, how it makes the query to run faster, and also what does the index contain?
0
sakthikumar
Asked:
sakthikumar
5 Solutions
 
qasim_mdCommented:
0
 
dqmqCommented:

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.  
0
 
OP_ZaharinCommented:
- 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:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optplanmgmt.htm
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/manage/spm/spm.htm

0
 
htonkovCommented:
Hi!
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...

Regards,
Hrvoje
0
 
slightwv (䄆 Netminder) Commented:
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'"?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now