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 : [I][This question was asked by sravan1][/I]

[QUOTE]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[/QUOTE]

For further refference,
[url=http://www.samspublishing.com/library/content.asp?b=STY_Sql_24hours&seqNum=125&rl=1]Sams Teach Yourself SQL in 24 Hours > How Do Indexes Work?[/url]

[url=http://www.websitedatabases.com/database-index.html]Database indexes for database speed & optimization[/url]

Well kalayama

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

Thanks

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 :mad:. 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). :cool:

Any further clarifications? Feel free to ask:)

Thanks for the explaination.

ican you explain with one example?

[QUOTE=sutnarcha;3803]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 :mad:. 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). :cool:

Any further clarifications? Feel free to ask:)[/QUOTE]
--------------------------------------

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..................!

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

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

[URL="http://www.samspublishing.com/library/content.asp?b=STY_Sql_24hours&seqNum=125&rl=1"]Sam's SQL Book[/URL]

Cheers!
Kalayama

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 .

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.

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.

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