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.