ASA SQL User's Guide
Query Optimization and Execution
Physical data organization and access
Every new row that is smaller than the page size of the database file will always be stored on a single page. If no present page has enough free space for the new row, Adaptive Server Anywhere writes the row to a new page. For example, if the new row requires 600 bytes of space but only 500 bytes are available on a partially filled page, then Adaptive Server Anywhere places the row on a new page.
To make table pages more contiguous on the disk, Adaptive Server Anywhere allocates table pages in blocks of eight pages. For example, when it needs to allocate a page it allocates eight pages, inserts the page in the block, and then fills up with the block with the next seven pages. In addition, it uses a free page bitmap to find contiguous blocks of pages within the dbspace, and performs sequential scans by reading groups of 64K, using the bitmap to find relevant pages. This leads to more efficient sequential scans.
Adaptive Server Anywhere locates space on pages and inserts rows in the order it receives them in. It assigns each to a page, but the locations it chooses in the table may not correspond to the order they were inserted in. For example, the engine may have to start a new page to store a long row contiguously. Should the next row be shorter, it may fit in an empty location on a previous page.
The rows of all tables are unordered. If the order that you receive or process the rows is important, use an ORDER BY clause in your SELECT statement to apply an ordering to the result. Applications that rely on the order of rows in a table can fail without warning.
If you frequently require the rows of a table to be in a particular order, consider creating an index on those columns specified in the query's ORDER BY clause.
Whenever Adaptive Server Anywhere inserts a row, it reserves only the space necessary to store the row with the values it contains at the time of creation. It reserves no space to store values that are NULL. It reserves no extra space to accommodate fields, such as text strings, which may enlarge.
Once assigned a home position on a page, a row never moves from that page. If an update changes any of the values in the row so it no longer fits in its assigned page, then the row splits and the extra information is inserted on another page.
This characteristic deserves special attention, especially since Adaptive Server Anywhere allows no extra space when you insert the row. For example, suppose you insert a large number of empty rows into a table, then fill in the values, one column at a time, using update statements. The result would be that almost every value in a single row will be stored on a separate page. To retrieve all the values from one row, the engine may need to read several disk pages. This simple operation would become extremely and unnecessarily slow.
You should consider filling new rows with data at the time of insertion. Once inserted, they then have sufficient room for the data you expect them to hold.
As you insert and delete rows from the database, Adaptive Server Anywhere automatically reuses the space they occupy. Thus, Adaptive Server Anywhere may insert a row into space formerly occupied by another row.
Adaptive Server Anywhere keeps a record of the amount of empty space on each page. When you ask it to insert a new row, it first searches its record of space on existing pages. If it finds enough space on an existing page, it places the new row on that page, reorganizing the contents of the page if necessary. If not, it starts a new page.
Over time, however, if you delete a number of rows and don't insert new rows small enough to use the empty space, the information in the database may become sparse. You can reload the table, or use the REORGANIZE TABLE statement to defragment the table.
For more information, see REORGANIZE TABLE statement.