Overflow and ISAM and Hash Tables

Database Administrator Guide > Database Administrator Guide > Maintaining Storage Structures > Overflow Management > Overflow and ISAM and Hash Tables

Was this helpful?

In hash and ISAM tables that have had a large amount of data added and have not been remodified, overflow and the resulting performance degradation is easy to understand. A keyed retrieval that normally touches one page now has to look through not only the main data page, but also every overflow page associated with the main data page. For every retrieval, the amount of disk I/O increases as the number of overflow pages increases.

Overflow pages are particular to a main data page for ISAM and hash tables, not to the table itself. If a table has 100 main pages and 100 overflow pages, it is likely that the overflow pages are distributed over many main data pages (that is, each main data page has perhaps one overflow page). A keyed retrieval on such a table possibly causes only one additional I/O rather than 100 additional I/Os.

For more information on overflow in hash tables, see Alternate Fill Factors.

For ISAM tables, because the ISAM index is static, if you append a large number of rows, the table can begin to overflow. If there is no room on a page to append a row, an overflow page is attached to the data page. For example, if you wanted to insert empno #33, there is no more room on the data page, so an overflow page is allocated for the data page as shown in the following diagram:

Page 8 Overflow Page for Primary Page 8

|--------------------------- | |-------------------------------|

| 29 |Ramos | 31| 30000.000 | | 33 |Quinn | 33| 20000.000 |

| 30 |Brodie | 42| 40000.000 | ---> | |

| 31 |Smith | 20| 10000.000 | | |

| 32 |Horst | 26| 50000.000 | | |

|--------------------------- | |-------------------------------|

For hash and ISAM tables, one way of looking at overflow is by looking at the tids of rows and analyzing the way the tids grow in a sequential scan through the table. For more information, see Tids on page 30.

Example: Showing Overflow Distribution

The sample code shown here can be customized to show overflow distribution. Each time a primary page is encountered, the tid’s value grows by 512. If a primary page has associated overflow pages, the tid’s value jumps by more than 512. So if you run the embedded SQL/C program shown in Sample Code to Show Overflow, the output looks like that shown in Output from Sample Code.

Sample Code to Show Overflow

page_val = 0;
exec sql select key, tid
    into :key_val, :tid_val
    from tablename
exec sql begin;
  if (tid_val == page_val)
  {
    printf("Primary Page %d, tid = %d,",(page_val/512)+1, tid_val);
    printf(" Starting key value = %d0", key_val);
    page_val = page_val + 512;
    old_tid_val = tid_val;
    overflow_page = 0;
  }
  else
  {
    if (tid_val > old_tid_val + 1)
  {
      overflow_page++;
      printf("\n Overflow page %d,tid = %d0",over_page,tid_val);
    }
    old_tid_val = tid_val;
  }
exec sql end;

Output from Sample Code

Primary Page 1, tid = 0, Starting Key Value = 123
Overflow page 1,tid = 2048
Overflow page 2,tid = 2560
Overflow page 3,tid = 3072
Overflow page 4,tid = 3584
Primary Page 2, tid = 512, Starting Key Value = 456
Overflow page 1,tid = 4096
Overflow page 2,tid = 4608
Overflow page 3,tid = 5120
Overflow page 4,tid = 5632

Last modified date: 11/28/2023