10. Choosing Storage Structures and Secondary Indexes : ISAM Storage Structure : Structure of an ISAM Table
 
Share this page                  
Structure of an ISAM Table
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.