10. Choosing Storage Structures and Secondary Indexes : Heap Storage Structure : Structure of a Heap Table
 
Share this page                  
Structure of a Heap Table
A heap table consists of a chain of pages. The layout of the sample heap table, employee, is shown below:
       empno  name        age  salary     comment
       +-------------------------------------------
Page 0 |  17| Shigio     | 29| 28000.000|
       |   9| Blumberg   | 33| 32000.000|
       |  26| Stover     | 38| 35000.000|
       |   1| Mandic     | 46| 43000.000|
       |-------------------------------------------
Page 1 |  18| Giller     | 47| 46000.000|
       |  10| Ming       | 23| 22000.000|
       |  27| Curry      | 34| 32000.000|
       |   2| Ross       | 50| 55000.000|
       |-------------------------------------------
Page 2 |  19| McTigue    | 44| 41000.000|
       |  11| Robinson   | 64| 80000.000|
       |  28| Kay        | 41| 38000.000|
       |   3| Stein      | 44| 40000.000|
       |-------------------------------------------
Page 3 |  20| Cameron    | 37| 35000.000|
       |  12| Saxena     | 24| 22000.000|
       |  29| Ramos      | 31| 30000.000|
       |   4| Stannich   | 36| 33000.000|
       |-------------------------------------------
Page 4 |  21| Huber      | 35| 32000.000|
       |  13| Clark      | 43| 40000.000|
       |  30| Brodie     | 42| 40000.000|
       |   5| Verducci   | 55| 55000.000|
       |-------------------------------------------
Page 5 |  22| Zimmerman  | 26| 25000.000|
       |  14| Kreseski   | 25| 24000.000|
       |  31| Smith      | 20| 10000.000|
       |   6| Aitken     | 49| 50000.000|
       |-------------------------------------------
Page 6 |  23| Gordon     | 28| 27000.000|
       |  15| Green      | 27| 26000.000|
       |   7| Curan      | 30| 30000.000| Fire
       |  24| Sabel      | 21| 21000.000|
       |-------------------------------------------
Page 7 |  16| Gregori    | 32| 31000.000|
       |   8| McShane    | 22| 22000.000|
       |  25| Sullivan   | 38| 35000.000|
       |
       +-------------------------------------------
Because table scans are expensive, heap is not a good structure to use while querying large tables. A retrieval of this type must look at every page in the employee table:
Select * from employee
   where employee.name = 'Sullivan';
A retrieval like this also scans the entire table, even though Shigio’s record is the first row of the first page:
Select * from employee
   where employee.name = 'Shigio';
Because heap tables do not eliminate duplicate rows, the entire table must be scanned in case there is another employee named Shigio on another page in the table.