IBM® Informix® 12.10
Table performance considerations
Some performance issues are associated with unfragmented tables and table fragments.
Issues include:
- Table placement on disk to increase throughput and reduce contention
- Space estimates for tables, blobpages, sbspaces, and extents
- Changes to tables that add or delete historical data
- Denormalization of the database to reduce overhead
- Placing tables on disk
Tables that the database server supports reside on one or more portions of one or more disks. You control the placement of a table on disk when you create it by assigning it to a dbspace. - Estimating table size
You can calculate the approximate sizes (in disk pages) of tables. - Managing the size of first and next extents for the tblspace tblspace
The tblspace tblspace is a collection of pages that describe the location and structure of all tblspaces in a dbspace. Each dbspace has one tblspace tblspace. When you create a dbspace, you can use the TBLTBLFIRST and TBLTBLNEXT configuration parameters to specify the first and next extent sizes for the tblspace tblspace in a root dbspace. - Managing sbspaces
An sbspace is a logical storage unit composed of one or more chunks that store smart large objects. You can estimate the amount of storage needed for smart large objects, improve metadata I/O, monitor sbspaces, and change storage characteristics. - Managing extents
As you add rows to a table, the database server allocates disk space in units called extents. Each extent is a block of physically contiguous pages from the dbspace. Even when the dbspace includes more than one chunk, each extent is allocated entirely within a single chunk, so that it remains contiguous. - Storing multiple table fragments in a single dbspace
You can store multiple fragments of the same table or index in a single dbspace, thus reducing the total number of dbspaces needed for a fragmented table. You must specify a name for each fragment that you want to store in the same dbspace. Storing multiple table or index fragments in a single dbspace simplifies the management of dbspaces. - Displaying a list of table and index partitions
Use the onstat -g opn option to display a list of the table and index partitions, by thread ID, that are currently open in the system. - Changing tables to improve performance
You can change tables to improve performance by dropping indexes, attaching or detaching fragments, and altering table definitions. You can also create databases for decision-support applications by unloading and loading tables in OLTP databases. - Denormalize the data model to improve performance
You might need to denormalize the data model to reduce overhead and optimize performance. - Reduce disk space in tables with variable length rows
You can enable the database server to insert more rows per page into tables with variable-length rows, if you set the MAX_FILL_DATA_PAGES configuration parameter to 1. Allowing more variable length rows per page has advantages and disadvantages. - Reduce disk space by compressing tables and fragments
You can reduce disk space by compressing data in tables and table fragments. After compressing data, you can repack the data to consolidate the free space in a table or fragment, and shrink the space for the data to return the free space to the dbspace.
Parent topic: Performance Guide
To find the PDF, see Publications for the IBM Informix 12.10 family of products.
For the release notes, documentation notes, and/or machine notes, see the Release Notes page.
For the release notes, documentation notes, and/or machine notes, see the Release Notes page.