|
|
|
ISAM is a keyed storage structure in which data is sorted by the value in the key column, and the index is static.
ISAM is a more versatile storage structure than hash. It supports pattern matching, range scans, and partial key specification, as well as exact match retrievals.
ISAM tables use a static index that points to a static number of main pages. The index contains key ranges and pointers either to other index pages or to the data page where rows with that key range are found.
Using the Modify Table Structure dialog or the modify statement, you can change any table to the ISAM storage structure. When you modify a table to ISAM, you must specify a key; otherwise, the first column is used as a key.
|
|
|
Here is a simple example that illustrates how the ISAM structure works. The employee table, which has 31 rows with a byte-width of 500, is modified to ISAM on employee number. The results are shown in the following table:
empno name age salary
Index Pages +----------------------------------------------
|1 |Mandic |46| 43000.000|
<=4 |2 |Ross |50| 55000.000|Data Page 1
=Page 1 |3 |Stein |44| 40000.000|
<=4 |4 |Stannich |36| 33000.000|
? |----------------------------------------------
>=5 >4 and |5 |Verducci |55| 55000.000|
<=8 |6 |Aitken |49| 50000.000|Data Page 2
<=8 =Page 2 |7 |Curan |30| 30000.000|
? |8 |McShane |22| 22000.000|
>8 |----------------------------------------------
>8 and |9 |Blumberg |33| 32000.000|
<=12 |10 |Ming |23| 22000.000|Data Page 3
=Page 3 |11 |Robinson |64| 80000.000|
<=12 |12 |Saxena |24| 22000.000|
? |----------------------------------------------
>=13 >12 and |13 |Clark |43| 40000.000|
<=16 |14 |Kreseski |25| 24000.000|Data Page 4
<=16 =Page 4 |15 |Green |27| 26000.000|
? |16 |Gregori |32| 31000.000|
>16 |----------------------------------------------
|17 |Shigio |35| 32000.000|
<=20 >16 and |18 |Giller |47| 46000.000|Data Page 5
? <=20 |19 |McTigue |44| 41000.000|
>20 =Page 5 |20 |Cameron |37| 35000.000|
|----------------------------------------------
|21 |Huber |35| 32000.000|
<=24 >20 and |22 |Zimmerman |26| 25000.000|Data Page 6
? <=24 |23 |Gordon |28| 27000.000|
>24 =Page 6 |24 |Sabel |21| 21000.000|
|----------------------------------------------
|25 |Sullivan |38| 35000.000|
>24and |26 |Stover |38| 35000.000|Data Page7
<=28 <=28 |27 |Curry |34| 32000.000|
? =Page7 |28 |Kay |41| 38000.000|
>28 |----------------------------------------------
|29 |Ramos |31| 30000.000|
>28 |30 |Brodie |42| 40000.000|Data Page8
=Page8 |31 |Smith |20| 10000.000|
|
+----------------------------------------------
Suppose you want to retrieve the employee data about employee number 11. Starting at the beginning of the index (at the left in the example), follow the index over to data Page 3, which contains rows of employees with employee numbers greater than 8 and less than or equal to 12. Scanning this page, you find employee number 11's row.
If you want to find all employees with employee numbers greater than 24, use the index, which directs you to Page 7, where you begin scanning the remainder of the table looking for qualifying rows.
To retrieve the row where the employee's name is Shigio, empno key does not help, because the index was constructed on empno and not on name. You must scan the entire table, from Page 0 through Page 9.
To append a new employee with an empno of 32, the search scans through the index to the largest key value less than 32. On the page with that key (Page 8), the new row is placed on the first available space on that page. If no room is left on that page, the row is placed on an overflow page.
|
|
|
ISAM can limit a scan if you specify at least the leftmost part of the key for the desired rows. ISAM also limits the pages scanned if you are looking for ranges of the key.
For instance, assume you modified the employee table to ISAM on name and age using the Structure of Table dialog. Alternatively, you can use the following modify statement:
modify employee to ISAM on name, age;
The following retrievals make use of the ISAM key:
select * from employee
where employee.name like 'S%';
select * from employee
where employee.name = 'Shigio'
and employee.age > 30;
In contrast, the following retrievals do not make use of the ISAM key, because the leftmost part of the key (name) is not restricted:
select * from employee
where employee.age = 32;
select * from employee
where employee.name like '%S'
and employee.age = 32;
select * from employee
where employee.name like '%higio%';
|
|
|
ISAM is a versatile storage structure because it supports both exact match and range retrievals. ISAM indexes and main pages are static—if you are appending many rows, remodify to avoid overflow pages. For tables that are mostly static, ISAM can be preferable to Btree.
Because ISAM indexes are static, no locking needs to be done on the ISAM index. In a heavily concurrent update environment, this feature makes ISAM more appealing than B-tree, where pages of the index must be locked when splitting or updating occurs.
ISAM is a good storage structure to use when the table is relatively static, and retrievals tend to use any of the following:
ISAM is a poor storage structure to use in any of these cases, which causes overflow pages:
|
|
|
The following are problems encountered with the ISAM storage structure, and their solutions:
Problem |
Solution |
|---|---|
You try to use pattern matching, but do not specify the leftmost character. |
*F* does not use the ISAM index, whereas F* does. If you cannot modify the search condition, the entire table must be scanned. |
You try to use just part of a multi-column key, but do not specify the leftmost column. |
If you cannot modify the search condition, create a secondary index with only the columns on which you are searching. |
The table is growing quickly and new rows are added to overflow pages. |
Use B-tree instead. |