Dictionary
INDEX
In the cases where database accesses are necessary and appropriate, it is imperative to perform those accesses as efficiently as possible. The single most important method of optimizing a database access is by using an index.
An index is a set of fields from a table that is sorted and then stored in a location separate from the table itself. Each record in the index contains a pointer to matching record(s) in the actual database table.
THE TELEPHONE BOOK ANALOGY
This analogy is probably as old as computer science itself, but it still works well. Think of an index like a telephone book.
If you want to find someone’s phone number, the first thing you might do is decide where you want to look for that number. One thing you probably won’t do is start calling all possible telephone number combinations until you hit upon the right one! You might ask a friend, or call directory assistance, or look up the information in a phone book.
Let’s suppose you decide to use a phone book. The next thing you do is to begin searching for that person’s name in the book. Once you find the name, you then look across from the name to find the number.
A DBMS goes through a similar process when performing a query. It first must decide how it is going to look for the information that has been requested, just as you did when looking for a telephone number. One option is to look at each record in the table, one at a time, until all the matching records have been found. This would be analogous to trying all possible combinations of telephone numbers until you found the right one.
As ridiculous as that sounds, sometimes DBMS’s have to do just that. This is called performing a full table scan.
Another option the DBMS has is to perform an index scan. In this case, the DBMS looks in a separate file, searching for the information by a particular key field (or fields). This is like using the phone book, searching for the correct name.
Once the matching record(s) have been found in the index, the DBMS follows a pointer from the index to retrieve the actual record in the table. This pointer consists of a disk address that tells the system exactly where to find the desired record(s). In our telephone book example, the analogous process is pretty simple: you simply look across the line from the name to the telephone number.
THE INDEX RANGE SCAN
This process is analogous to an index range scan that might be performed by a DBMS. Sometimes an index doesn’t point to a single record. If you have an index of customers by their last names, this will probably be the case – you will probably have many customers with the same last name.
In this case, once the database finds matching record(s) in the index, its job isn’t over. It now must retrieve all the records that matched the selected index records, and examine each one to see if they meet the final selection criteria for the query.
THE UNIQUE INDEX SCAN
In contrast, if each index record matches exactly one record in the table, and if all the fields of the index are specified in the query, a unique index scan can be performed. Queries based on a table’s full primary key always fulfil this criterion – such as by client (implicit if using Open SQL) and customer number in table KNA1.
In this case, once the DBMS finds the matching record in the index, its work is almost done. All it must do is follow the pointer from the index to the solitary table record that it knows will satisfy the query.
EVALUATION THE VARIOUS TYPES OF DATA SCAN
SUMMARY