Results 1 to 11 of 11

Thread: How does indexes function?

  1. #1
    Moderator
    Join Date
    Oct 2005
    Answers
    305

    How does indexes function?

    How does indexes function? ie. How do they retrieve records? I know they use Rowid's but want to know how exactly they process?

    Note : [This question was asked by sravan1]


  2. #2
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: How does indexes function?

    When an index is created, it records the location of values in a table that are associated with the column that is indexed. Entries are added to the index when new data is added to the table. When a query is executed against the database and a condition is specified on a column in the WHERE clause that is indexed, the index is first searched for the values specified in the WHERE clause. If the value is found in the index, the index returns the exact location of the searched data in the table
    For further refference,
    Sams Teach Yourself SQL in 24 Hours > How Do Indexes Work?

    Database indexes for database speed & optimization

    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  3. #3
    Moderator
    Join Date
    Oct 2005
    Answers
    305

    Re: How does indexes function?

    Well kalayama

    Its quiet simple and neat
    Should help sravan1 in getting a clear idea on how indexes actually function.

    Thanks


  4. #4
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: How does indexes function?

    Its simple,

    imagine u have details of 100 members in a group with 5 different backgrounds Commerce, Physics, Mathematics, Computers and Law.

    If I ask you the names and addresses of all members in the group with Physics background. Then, you will have to search all ROWIDs of 100 members’ details and compare each background with ‘Physics’ to give the result. And immediately, I ask you the names and addresses of all members in the group with Computers background, u will have to search all ROWIDs of 100 members’ details again . This would be tedious and time consuming as the average ROWIDs searched each time is 100%.

    Now imagine, u have 5 lists of ROWIDs in each of the background, Commerce, Physics, Mathematics, Computers and Law and I ask you the same questions. This time, you have to search only those ROWIDs that are in the Physics / Computers background. It is easy as the average ROWIDs searched each time is just 20% (5 backgrounds).

    Any further clarifications? Feel free to ask


  5. #5
    Junior Member
    Join Date
    Sep 2006
    Answers
    1

    Re: How does indexes function?

    Thanks for the explaination.


  6. #6
    Junior Member
    Join Date
    Jan 2007
    Answers
    23
    ican you explain with one example?

    Quote Originally Posted by sutnarcha View Post
    Its simple,

    imagine u have details of 100 members in a group with 5 different backgrounds Commerce, Physics, Mathematics, Computers and Law.

    If I ask you the names and addresses of all members in the group with Physics background. Then, you will have to search all ROWIDs of 100 members’ details and compare each background with ‘Physics’ to give the result. And immediately, I ask you the names and addresses of all members in the group with Computers background, u will have to search all ROWIDs of 100 members’ details again . This would be tedious and time consuming as the average ROWIDs searched each time is 100%.

    Now imagine, u have 5 lists of ROWIDs in each of the background, Commerce, Physics, Mathematics, Computers and Law and I ask you the same questions. This time, you have to search only those ROWIDs that are in the Physics / Computers background. It is easy as the average ROWIDs searched each time is just 20% (5 backgrounds).

    Any further clarifications? Feel free to ask
    --------------------------------------

    can you explain with ine example b'cse i am a new for data base .i got the idea about that but i don't know how to implement that?

    if time premits please.....................?

    i am waiting for your valuable reply..................!

    Last edited by kalayama; 02-15-2007 at 05:53 AM.

  7. #7
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: How does indexes function?

    Hi aarusa,

    Check the link si paste in one of my previous posts in this thread. They are very good. Once you are done with reading those, post again with your queries or if you get any doubts while reading the articles

    I am pasting one of the links here. This book teaches you everything that you need to know in SQL. Enjoy!

    Sam's SQL Book

    Cheers!
    Kalayama

    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  8. #8
    Junior Member
    Join Date
    Feb 2007
    Answers
    2

    Re: How does indexes function?

    Hi lokesh, index functionality is similar to the index that u see for a book. The index contains the primary key fields in a table and is present in the database. When u retrive the data from database using sql statements, then index helps u to retrive the data.as u mention the condition where .... The fields which are not primary and u use the fields more often , then u create secondary index which stores the info of the fields. Index is used to improve the performance .


  9. #9
    Junior Member
    Join Date
    Jul 2007
    Answers
    15

    Re: How does indexes function?

    Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book. Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.

    An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.

    Last edited by mahesh9920; 09-27-2007 at 05:58 AM. Reason: Same Answer was Already Given

  10. #10
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: How does indexes function?

    Indexes stores the address of data not the data itself. When a query is executed and the indexed column is used system retrives the records veryfast byusing the address of data stored in indexes.


  11. #11
    Junior Member
    Join Date
    Apr 2007
    Answers
    1

    Re: How does indexes function?

    [QUOTE=sutnarcha;3803]Its simple,
    sutnarcha ur explanation is very gud...i understood the concept..thanks...bye


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact