What does the index contain?

Posted on 2011-05-01
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
    LVL 4

    Accepted Solution

    LVL 42

    Assisted Solution


    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

    - 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:

    LVL 3

    Assisted Solution

    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 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    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…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now