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.
Last modified date: 11/28/2023