5.1 Simple Indexes

5.1 Simple Indexes

The simplest thing you can do to search a table is to just use the CREATE TABLE command and nothing else. In most SQL implementations, the rows will be stored in a file structure with a sequential access method of some kind. This saves the storage that would have been used for another access method, but will cost access time. SQL is not assumed to order the rows in a table, so every query to the table will have to do a complete linear search to be sure that it checked every row. While this is not really an index at all, it is leading up to the idea of an index.

What if instead of reading the table sequentially, I put the search results in another, smaller data structure that had the search value and the location of the matching row in it? This will cost me some extra storage, but I will get better search times because my little list will be so much smaller than the original table. That is the idea of an index!

The most basic form of index consists of a file made up of the key column value(s) from all the base table?s rows and a pointer to the physical address where each row is located in the table. If you need a physical analogy for this method, look at the doorbell board in a large apartment building. The index is the list of names and apartment numbers (usually...

< Previous Excerpt Next Excerpt >

Purchase This Book

Joe Celko's Data and Databases: Concepts in Practice

TABLE OF CONTENTS

5.1 Simple Indexes

Contact Preferences

This is embarrasing...

Customize Your GlobalSpec Experience

Select Your Free Newsletters

Industry Newsletters

Select Your Free Product Alerts

This is embarrasing...