Indexed Sequential Access Method | Database Management System

IndexedВ Sequential Access Method | Database Management System

Indexed Sequential Access Method : ISAM is a method for indexing data for fast retrieval. SAM was originally developed by IBM for mainframe computers. In an ISAM system, data is organized into records which are composed of fixed length fields. Records are stored sequentially, originally to speed access on a tape system. A secondary set of hash tables known as indexes contain “pointers” into the tables, allowing individual records to be retrieved without having to search the entire data set. This is a departure from the contemporaneous navigational databases, in which the pointers to other data were stored inside the records themselves. The key improvement in SAM is that the indexes are small and can be searched quickly, thereby allowing the database to access only the records it needs. Additionally modifications to the data do not require changes to other data, only the table and indexes in question.

ISAM is very simple to understand and implement, as it primarily consists of direct, sequential access to a database file. It is also very inexpensive. The trade off is that each client machine must manage its own connection to each file it accesses. This, in turn, leads to the possibility of conflicting inserts into those files, leading to an inconsistent database state. This is typically solved with the addition of a client-server framework which marshals client requests and maintains ordering. This is the basic concept behind a database management System (DBMS), which is a client layer over the underlying data store.

Creating an ISAM File

An ISAM file must be loaded sequentially in Sorted order by record key. SAM win defect a record out of order. Any dummy records to be added to the file should be placed in the input data stream in sequence. These records are best added where record additions are expected to take place. For instance, a Credit card company may expect in the near future to add records whose keys range between 416-250-000 and 416-275-000 as a new district of credit cardholders is opened up. In this case, dummy records with these keys can be Created and added to the file during file creation. Another possibility is simply to scatter a certain percentage of dummy records throughout the file. This is not nearly as effective as always possible (there may be no unused keys in the file). Recall that a dummy record is only ignored if it is at the end of a track. It will stay on a track until it is replaced by a valid record with the same key or pushed off the end by a new insertion.

Once the file is in use, any record whose deletion is desired can be turned into a dummy record by writing HIGH-VALUES in its first character position. This is a useful feature, especially in a credit card situation or phone number list where inactive customers can be replaced.

Advantages of ISAM indexes

  1. Because the whole structure is ordered to a large extent, partial (LIKE y%) and range (BETWEEN 12 and 18) based retrievals can often benefit from the use of this type of index.
  2. SAM is good for Static tables because there are usually fewer index levels than B-tree.
  3. Because the index is never updated, there are never any locking Contention problems within the index itself—this can occur in B-tree indexes, especially when they get to the point of ‘splitting to Create another level.
  4. In general there are fewer disk I/OS required to access data, provided there is no overflow.
  5. Again if little overflow is evident, then data tends to be clustered. This means that single block retrieval often brings back rows with similar key values and of relevance to the initiating query.

Disadvantages of ISAM indexes

  1. SAM is still not as quick as Some (hash organization, dealt with later, is quicker).
  2. Overflow can be a real problem in highly volatile tables.
  3. The sparse index means that the lowest level of index has only the highest key for a specific data page, and therefore the block (or more usually a block header), must be searched to locate Specific rows in a block.

In a nutshell therefore, these are the two types of indexing generally available. Indexes can be either Created on One single, or several groups, of Columns within Single tables, and generally the ability to Create them should be a privilege under the control of the DBA. Indexes usually take up significant disk space, and although generally of significant benefit in the case of data retrieval, they can slow down insert/update and delete operation because of overhead in maintaining the index, and in SAM of ensuring the logical organization of the data rows. The presence of an index does not mean that the RDBMS will always use it. It is not generally possible to pick and choose which index will be used under which conditions. If follows, therefore, that the administration of indexes should be done centrally, with great case, and should be a major consideration in the physical design Stage of a project due to its application dependence.