The basic unit of database server I/O is a page. Page size might vary among computers.
In Dynamic Server, the page size depends on the operating system.
The database server can store rows that are longer than a page. The database server also supports the VARCHAR data type, which results in rows of varying length. As a result, rows do not conform to a single format.
Rows within a table are not necessarily the same length if the table contains one or more columns of type VARCHAR. In addition, the length of a row in such a table might change when an end user modifies data contained in the VARCHAR column.
The length of a row can be greater than a page.
TEXT and BYTE data is not stored within the data row. Instead, the data row contains a 56-byte descriptor that points to the location of the data. The descriptor can point to a dbspace page.
The descriptor can point to a blobspace blobpage. If you are using the Optical Subsystem, the descriptor can also point to an optical-storage subsystem.
For instructions about how to estimate the length of fixed-length and variable-length data rows, see your IBM Informix Performance Guide.
Informix uses two different types of rowids to identify data in tables:
These rowids are fields in a table and are assigned to tables created with the WITH ROWID option.
The database server identifies each data row in a table with a unique internal rowid. This rowid identifies the location of the row within the dbspace.
To obtain the internal rowids for a table, use the oncheck -pD option. For more information, see Check Pages with -cd and -cD.
In a nonfragmented table, the term rowid refers to a unique 4-byte integer that defines the physical location of the row in the table. The page that contains the first byte of the data row is the page that is specified by the rowid. This page is called the data row home page.
Fragmented tables can also have rowids, but they are implemented in a different way. For more information on this topic, see Rows in Fragmented Tables.
Every data row in a nonfragmented table is uniquely identified by an unchanging rowid. When you create an index for a nonfragmented table, the rowid is stored in the index pages associated with the table to which the data row belongs. When the database server requires a data row, it searches the index to find the key value and uses the corresponding rowid to locate the requested row. If the table is not indexed, the database server might sequentially read all the rows in the table.
Eventually, a row might outgrow its original storage location. If this occurs, a forward pointer to the new location of the data row is left at the position defined by the rowid. The forward pointer is itself a rowid that defines the page and the location on the page where the data row is now stored.
Unlike rows in a nonfragmented table, the database server does not assign a rowid to rows in fragmented tables. If you want to access data by rowid, you must explicitly create a rowid column as described in your IBM Informix Performance Guide. If user applications attempt to reference a rowid in a fragmented table that does not contain a rowid that you explicitly created, the database server returns an appropriate error code to the application.
From the viewpoint of an application, the functionality of a rowid column in a fragmented table is identical to the rowid of a nonfragmented table. However, unlike the rowid of a nonfragmented table, the database server uses an index to map the rowid to a physical location.
When the database server accesses a row in a fragmented table using the rowid column, it uses this index to look up the physical address of the row before it attempts to access the row. For a nonfragmented table, the database server uses direct physical access without an index lookup. As a consequence, accessing a row in a fragmented table using rowid takes slightly longer than accessing a row using rowid in a nonfragmented table. You should also expect a small performance impact on the processing of inserts and deletes due to the cost of maintaining the rowid index for fragmented tables.
Primary-key access can lead to significantly improved performance in many situations, particularly when access is in parallel.
It is recommended that application developers use primary keys as a method of access rather than rowids. Because primary keys are defined in the ANSI specification of SQL, using them to access data makes your applications more portable.
For a complete description on how to define and use primary keys to access data, see the IBM Informix Guide to SQL: Reference and the IBM Informix Guide to SQL: Tutorial.
The variable length of a data row has the following consequences for row storage:
The following paragraphs describe the guidelines that the database server follows during data storage.
To minimize retrieval time, rows are not broken across page boundaries unnecessarily. Rows that are shorter than a page are always stored as whole rows. A page is considered full when the count of free bytes is less than the number of bytes needed to store a row of maximum size.
When the database server receives a row that is longer than a page, the row is stored in as many whole pages as required. The database server then stores the trailing portion in less than a full page.
The page that contains the first byte of the row is the row home page. The number of the home page becomes the logical page number contained in the rowid. Each full page that follows the home page is referred to as a big-remainder page. If the trailing portion of the row is less than a full page, it is stored on a remainder page.
After the database server creates a remainder page to accommodate a long row, it can use the remaining space in this page to store other rows.
Figure 12 illustrates the concepts of home page, big-remainder page, and remainder page.
Over time, the free space on a page can become fragmented. When the database server attempts to store data, it first checks row length against the number of free bytes on a page to determine if the row fits. If adequate space is available, the database server checks if the page contains adequate contiguous free space to hold the row (or row portion). If the free space is not contiguous, the database server calls for page compression.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]