The virtues and trade-offs of database indexing –

We typically think of a database as a store of digital data, but a database can be a store of written data as well. From this perspective of definition, a library is a database. Despite their differences, a library of books and a database have much in common. Neither the librarian nor the database administrator simply haphazardly stores the data in the next available slot. Both categorize their data. The library has a section for philosophy and psychology, for science and other non-fiction books, one for drama, another for English and so on.

Similarly, a digitized data base will store system data in one folder, programs in another, business data in yet another, and so on. With a simple list of categories and bin or disk locations, both the librarian and the database administrator can find and retrieve what is wanted. This master list serves as an index into some space. A file name is associated to an address. We do not consider this master list a part of database indexing, yet the concept is the same.

Books or digital data are more easily retrieved when they are indexed. Without an index, you have to go down the aisle containing books on, say, psychology, looking at each title, until you find the one you want. The same is true of databases in which database indexing is not applied. If the data is all thrown in to one database, say the customer database, without any regard to order, then conceivably the entire database might have to be searched, starting from the first record, comparing the customer name with the name you want, selecting it if it matches; otherwise, you move on to the next record and compare again, doing this record after record until you’ve found a match. Ordering books by title or ordering a customer database by name significantly improves your search time. Ordering your information, although a necessary step when database indexing, is also not indexing.

Both libraries and database designers have learned that you can improve your search speed by using indexes, keys associated to the location of the item sought. Libraries use card catalogs to index their books, and library maps to help the searcher find the book’s location. Database indexing uses files similar to card catalogs, files that contain a key value associated to a disk location, or that contains a value that points to a specific location. Rather than traverse each track of a disc containing database rows, which are similar to records, a retrieval may read an index file and find the index and the rows location, then go to the row location and read in the row. Database indexing gains you a substantial advantage by avoiding having to read in all records. One record length might be the length of five key-address pairs. The database system will still have to read each key/address sequentially, until it finds a match, but it will have less reading to do.

Use database indexing to minimize your retrieval time, but remember too, it takes longer to store a record because you must also store an index. Using indexes can make your adding and updating time unreasonably long.

Administering a database involves more than the practicalities. There’s an element of art to this task. If you’re considering optimizing your database, you’d be smart to investigate the finer points of database indexing to make your best choice. We at teach you all these under host of Oracle Fusion Applications Training courses.