|
|
|
To improve performance, you can change tables to a more effective storage structure by using modify procedures.
|
|
|
For hash, ISAM, and B-tree structures, you must specify key columns. (Heap and heapsort tables do not have key columns.) There is no limit to the number of key columns that can be specified, but as key columns increase, performance declines slightly.
|
|
|
In VDBA, to change a table from one storage structure to another, use the Modify Table Structure dialog. By enabling the Change Storage Structure radio button and clicking Structure, you activate the Structure of Table dialog, where you can specify the parameters for the storage structure type and other structure-specific characteristics. For secondary indexes, the Modify Index Structure dialog offers a similar option to enable the Structure of Index dialog. For more information, see Modifying Storage Structures in online help.
Using SQL, you can accomplish this task with the modify statement. For more information, see the SQL Reference Guide.
|
|
|
Keep in mind the following effects of the modify procedure when you are modifying the storage structure:
|
|
|
The modify procedure provides several options:
The MinPages, MaxPages, Allocation, Fillfactor, Leaffill, and Nonleaffill options take effect during the modify procedure only, but are remembered in the system catalog description of the table. They will be applied again by a future modify-to-reconstruct, and will be output as part of the table description by copydb and unloaddb. The Extend, Unique, and Compression options are continuously active throughout the life of the table.
In VDBA, these options are in the Structure of Table and Structure of Index dialogs.
|
|
|
Min Pages and Max Pages are valid options only when you are modifying the table to hash. These options allow you to control the hashing algorithm to some extent, extending the control offered by the Fillfactor option.
The Min Pages option is useful if the table will be growing rapidly or if you want few rows per page to increase concurrency so multiple people can update the same table.
You can achieve nearly the same effect by specifying a low value for the Fillfactor option, but the fill factor is based on the current size of the table, as described in Alternate Fill Factors.
To force a specific number of main pages, use the Min Pages option to specify a minimum number of main pages. The number of main pages used are at least as many as specified, although the exact number of Min Pages specified is not used.
|
|
|
For example, for the emp table in the previous chapter you can force a higher number of main pages by specifying the minimum number of main pages when you modify the table to hash. If you specify 30 main pages for the table, which has 31 rows, you have approximately one row per page.
Follow these steps to modify the storage structure of the emp table:
To specify a maximum number of main pages to use, rather than the system choice, use the Max Pages option. If the number of rows does not completely fit on the number of pages specified, overflow pages are allocated. If fewer pages are needed, the lesser number is used. Max Pages is useful mainly for shrinking compressed hash tables more than otherwise happens.
You can achieve nearly the same effect by specifying a high value for the Fillfactor option, but the fill factor is based on the current size of the table, as described in Alternate Fill Factors.
|
|
|
The following example modifies the emp table, specifying a Max Pages value.
Remember that Max Pages controls only the number of main pages; it does not affect overflow pages. For example, assume your data takes 100 pages in heap. If you modify the table to hash and limit the number of main pages to 50, the remainder of the data goes onto overflow pages.
|
|
|
Use the Allocation option to pre-allocate space. You can modify the table to an allocation greater than its current size to leave free space in the table. (The default is four pages if no allocation has been specified.)
Doing this allows you to avoid a failure due to lack of disk space, or to provide enough space for table expansion instead of having to perform a table extend operation. Extending a Table or Index provides more information.
The allocated size must be in the range 4 to 8,388,607 (the maximum number of pages in a table). The specified size is rounded up, if necessary, to make sure the allocation size for a multi-location table or index is always a multiple of sixteen.
Note: If the specified number of pages cannot be allocated, the modify procedure is aborted.
After an allocation is specified, it remains in effect and does not need to be specified again when the table or index is modified.
|
|
|
The following example specifies that 1000 pages be allocated to table inventory:
The space allocated is 1008, due to rounding.
|
|
|
The Extend option allows you to control the amount of space by which a table is extended when more space is required. (The default extension size is 16 pages.)
The size must be in the range 1 to max_size, where the max_size is calculated as:
8,388,607 – allocation_size.
The specified Extend size is rounded up, if necessary, to make sure the size for a multi-location table or index is always a multiple of sixteen.
Note: If the specified number of pages cannot be allocated, the operation fails with an error.
After an extend size has been specified for the table or index, it remains in effect and does not need to be specified again when the table or index is modified.
|
|
|
The following example specifies that the table inventory be extended in blocks of 1000 pages:
The extension space is 1008, due to rounding.
|
|
|
When choosing an extend size, keep the following in mind:
|
|
|
Each storage structure has a different default fill factor. The term fill factor refers to the number of rows that are actually put on a data page divided by the number of rows that fit on a data page for a particular structure.
The various fill factors enable you to add data to the table without running into overflow problems. Because the data pages have room to add data, you do not have to remodify.
For instance, a heap table fits as many rows as possible on a page; this is known as 100% fill factor. However, ISAM and B-tree data pages are filled only to 80% capacity, leaving room to add 20% more data before a page is completely full.
The default data page fill factors are as follows:
Storage Structure |
Default Fill Factor |
Multiply Heap Size by |
Number of Pages Needed for 100 Full Pages |
|---|---|---|---|
B-tree |
80% |
1.25 |
125 + index pages |
compressed B-tree |
100% |
1 |
100 + index pages |
hash |
50% |
2 |
200 |
compressed hash |
75% |
1.34 |
134 |
heap |
100% |
1 |
100 |
compressed heap |
100% |
1 |
100 |
ISAM |
80% |
1.25 |
125 + index pages |
compressed ISAM |
100% |
1 |
100 + index pages |
The default B-tree index page fill factors are as follows:
Storage Structure |
Default Fill Factor |
|---|---|
B-tree leaf |
70% |
B-tree index |
80% |
The first table shows that if a heap table is 100 pages and you modify that table to hash, the table now takes up 200 pages, because each page is only 50% full.
Note: Depending on the system allocation for tracking used and free pages, the number of pages can be approximate. For more information, see the chapter "Calculating Disk Space."
|
|
|
You can tailor the fill factor for various situations. For instance, if the table is not going to grow at all, use a 100% fill factor for the table. On the other hand, if you know you are going to be adding a lot of data, you can use a low fill factor, perhaps 25%. Also, if your environment is one where updates are occurring all the time and good concurrency is important, you can set the fill factor low.
Note: Fill factor is used only at modify time. As you add data, the pages fill up and the fill factor no longer applies.
When specifying a fill factor other than the default, you must keep the following points in mind:
Specifying fill factor is useful for hash and ISAM tables. However, for B-tree tables, because data pages only are affected, the Fillfactor option must be used with the Leaffill or Nonleaffill options. See Leaf Page Fill Factors and Index Page Fill Factors.
For hash tables, typically a 50% fill factor is used for uncompressed tables. You can raise or lower this, but raising it too high can cause more overflow pages than desirable. You must always measure the overflow in a hash table when setting a high fill factor—fill factors higher than 90% are likely to cause overflow.
If you are using compressed ISAM tables and are adding data, make sure you set the fill factor to something lower than the default 100%, or you immediately add overflow pages.
Normally, uncompressed ISAM tables are built with an 80% fill factor. You can set the fill factor on ISAM tables to 100%, and unless you have duplicate keys, you cannot have overflow problems until after you add data to the table.
In VDBA, you control the fill factor of the data pages using the Fillfactor option in the Structure of Table and Structure of Index dialogs.
|
|
|
This example sets the fill factor on a hash table to 25%, rather than the default of 50%, by modifying the emp table:
|
|
|
This example sets the fill factor on an uncompressed ISAM table to 100%:
|
|
|
It is possible to specify B-tree leaf page fill factors at modify time. This is the percentage of the leaf page that is used during the modify procedure. The remaining portion of the page is available for use later when new rows are added to the table.
The purpose of the fill factor is to leave extra room on the leaf pages to do inserts without causing leaf page splits. This is useful if you modify a table to B-tree and plan to add rows to it later.
In VDBA, you control these values using the Leaffill options in the Structure of Table dialog.
The Leaffill option specifies the percentage of each leaf page to be filled at the time the table is modified to B-tree or cB-tree. The Leaffill default is 70, which means that 70% of the leaf page is filled at modify time and 30% remains empty for future use.
For example, assume that the key-tid pair requires 400 bytes of storage. This means that five key-tid pairs fit on a single 2 KB B-tree leaf page. However, if the leaf page fill factor is specified at 60%, only three key-tid pairs are allocated on each B-tree leaf page at modify time. If subsequent updates to the table cause two new rows on this leaf page, they are placed in the empty space on the leaf page. The key-tid pairs are reordered on the leaf page from min to max. If more than two new rows need to be added to this leaf page, there is not enough space and the leaf page has to split.
|
|
|
It is possible to specify B-tree index page fill factors at modify time. This is the percentage of the index page that is used during the modify procedure. The remaining portion of the page is available for use later when new rows are added to the table. The purpose of the fill factor is to leave extra room on the index pages to do inserts without causing index page splits. This is useful if you modify a table to B-tree and plan to add rows to it later.
In VDBA, you control these values using the Nonleaffill options in the Structure of Index dialog.
The Nonleaffill option specifies the percentage of each index page that is to be filled at the time the table is modified to B-tree. That is, it is similar to Leaffill, but for index pages instead of leaf pages. The Nonleaffill default is 80. This means that 80% of the index page is used at modify time and 20% remains empty for future use.
For example, assume that the key-tid pair requires 500 bytes of storage. This means that four key-tid pairs fit on a single B-tree index page. However, if the index page fill factor is specified at 75%, only three key-tid pairs are allocated on each 2 KB B-tree index page at modify time. If subsequent updates to the table cause another leaf page to be allocated, the empty space on the index page is used to hold a key-tid pair for that new leaf page. If there are enough new rows to cause two new leaf pages to be added to that index page, the index page must split. For more information, see Tids.
Setting a fill factor of lower than 60 on leaf pages can help reduce locking contention when B-tree leaf pages are splitting, because index splitting is reduced. Setting Leaffill low for small but quickly growing B-trees is advisable.
When you specify a high Leaffill, index splitting is almost guaranteed to occur because leaf pages immediately fill up when data is added. Thus, you want to avoid a high fill factor unless the B-tree table is relatively static. Even in this case, use an ISAM table.
|
|
|
Unique keys can be enforced automatically for hash, ISAM, and B-tree tables using the modify procedure.
|
|
|
Benefits of unique keys are:
In most cases unique keys are an advantage in your data organization.
|
|
|
The disadvantages of unique keys include a small performance impact in maintaining uniqueness. You must also plan your table use so that you do not add two rows with the same key value.
|
|
|
In VDBA, unique keys can be specified as Row or Statement in the Unique group box in the Structure of Table and Structure of Index dialogs:
If you do not want to create a unique key, select the No option.
|
|
|
The following example prevents the addition of two employees in the emp table with the same empno:
If a new employee is added with the same employee number as an existing record in the table, the row is not added, and you are returned a row count of zero.
Note: An error is not returned in this case; only the row count shows that the row was not added. Be aware of this if you are writing application programs using unique keys.
|
|
|
The following example modifies the emp table to hash and prevents the addition of two employees in the emp table with the same empno.
The rows in the following example have unique keys. Although employee #17 and #18 have the same records except for their employee numbers, the employee numbers are unique, so these are valid rows after the modification:
Empno Name Age Salary
| 17 | Shigio | 29| 28000.000|
| 18 | Shigio | 29| 28000.000|
| 1 | Aitken | 35| 50000.000|
The following two rows do not have unique keys. These two rows cannot both exist in the emp table after modification to hash unique on empno:
Empno Name Age Salary
| 17 | Shigio | 29| 28000.000|
| 17 | Aitken | 35| 50000.000|
|
|
|
All storage structures—except R-tree secondary index and heapsort—permit tables and indexes (where present) to be compressed.
Compression is controlled using the Key and Data options in the Compression group box in the Structure of Table and Structure of Index dialogs. By default, there is no compression when creating or modifying.
Not all parts of all storage structures can be compressed, as summarized in the table below:
Storage Structure |
Data |
Key |
|
|---|---|---|---|
B-tree |
Base Table |
Yes |
Yes |
|
Secondary Index |
No |
Yes |
hash |
Base Table |
Yes |
No |
|
Secondary Index |
Yes |
No |
heap |
Base Table |
Yes |
No |
|
Secondary Index |
N/A |
N/A |
heapsort |
Base Table |
No |
No |
|
Secondary Index |
N/A |
N/A |
ISAM |
Base Table |
Yes |
No |
|
Secondary Index |
Yes |
No |
R-tree |
Base Table |
N/A |
N/A |
|
Secondary Index |
No |
No |
Note: In VDBA, selecting Data in the Compression group box in the Structure of Table dialog does not affect keys stored in ISAM or B-tree index and leaf pages—only the data on the data pages is compressed. To compress index entries on B-tree index pages, select Key instead.
ISAM index pages cannot be compressed.
Compression of tables compresses character and text columns. Integer, floating point, date, and money columns are not compressed, unless they are nullable and have a null value.
Trailing blanks and nulls are compressed in character and text columns. For instance, the emp table contains a comment column that is 478 bytes. However, most employees have comments that are only 20 to 30 bytes in length. This makes the emp table a good candidate for compression because 478 bytes can be compressed into 30 bytes or fewer, saving nearly 450 bytes per row.
Furthermore, as many rows are placed on each page as possible, so that the entire emp table (31 rows) that normally took eight 2KB pages as a heap, takes just one page as a compressed heap. In this example, pages were limited to four rows per page, but by using compression, many more rows can be held per page.
There is no formula for estimating the number of rows per page in a compressed table, because it is entirely data dependent.
|
|
|
When a table is compressed, you can reduce the amount of disk I/O needed to bring a set of rows from disk. This can increase performance if disk I/O is a query-processing bottleneck.
For instance, having compressed the emp table from eight pages down to one page, the following query performs only one disk I/O, whereas prior to compression as many as eight disk I/Os were required:
select * from emp;
In a large table, compression can dramatically reduce the number of disk I/Os performed to scan the table, and thus dramatically improve performance on scans of the entire table. Compression is also useful for conserving the amount of disk space it takes to store a table.
|
|
|
Compression must be used wisely, because the overhead associated with it can sometimes exceed the gains.
If a machine has a fast CPU, disk I/O can be the bottleneck for queries. However, because compression incurs CPU overhead, the benefits must be weighed against the costs, especially for machines with smaller CPUs. Compression can increase CPU usage for a query because data must be decompressed before it is returned to the user. This increase must be weighed against the benefits of decreased disk I/O and how heavily loaded the CPU is. High compression further reduces disk I/O, but uses even more CPU resources.
There is overhead when updating compressed tables. As rows are compressed to fit as many as possible per page, if you update a row so that it is now larger than it was before, it must be moved to a new spot on the page or even to a new page. If a row moves, its tid, or tuple identifier, also changes, requiring that every secondary index on the compressed table also be updated to reflect the new tid. For more information, see Tids.
For example, if you change Shigio's comment from "Good" to "Excellent," Shigio's record length grows from 4 bytes to 9 bytes and does not fit back in exactly the same place. His record needs to be moved to a new place (or page), with updates made to any secondary indexes of this table (if the emp table was B-tree, the appropriate B-tree leaf page is updated instead).
Compressed tables must be avoided when updates that increase the size of text or character columns occur frequently, especially if there are secondary indexes involved—unless you are prepared to incur this overhead. If you do compress and are planning to update, use a fill factor lower than 100% (75% for hash); the default fill factor for compressed tables is 75% for hash with data compression, 100% for the others. With free space on each page, moved rows are less likely to be placed on overflow pages. For more information, see Options to the Modify Procedure.
|
|
|
The default page size is 2 KB. The corresponding buffer cache for the installation must also be configured with the page size you specify or you receive an error. For more information, see the "Configuring Ingres" chapter in the System Administrator Guide.
For more information on page size see Storage Structures and Performance.
In VDBA, you specify page size using the Page Size option in the Structure of Table and Structure of Index dialogs.
|
|
|
To maintain good concurrency and performance, the B-tree index is not rebuilt after deletions. Deletions occur at the leaf and data page level, but an empty leaf page is not released. If your environment is one where many deletions are performed, you must occasionally update the index
In VDBA, you do this using the Shrink B-tree Index option in the Modify Table Structure and Modify Index Structure dialogs.
In SQL, you accomplish this task with the modify statement. The "to merge" clause is the same as the Shrink B-tree Index option. For more information, see the SQL Reference Guide.
The Shrink B-tree Index option is also important for users with incremental keys, which can incur lopsided indexes after heavy appends to the end of the table.
Not updating the index to reflect unused leaf pages can cause the index to be larger than necessary.
For example, if the emp table is keyed on empno (ranging from 1 to 31), and you fire all employees with employee numbers less than 16, the B-tree index does not shrink, but is unbalanced. This is shown in the following "Before" diagram:
Before
<=16 >16
/ \
<=8 >8 <=24 >24
/ \ / \
<=4 >4 <=12 >2 <=20 >20 <=28 >28
Page 1 Page 2 Page 3 Page 4
(deleted (deleted valid valid
data) data) data data
To re-balance the index level, you can use the Shrink B-tree Index option. It also reclaims unused leaf pages that otherwise are never reused. This is shown in the following "After" diagram:
After
<= 24 >24
/ \
<=16 >16 <=28 >28
Page 3 Page 4
valid valid
data data
Free page list: 1,2
The index is rebuilt, and empty leaf pages are marked as free, but otherwise leaf and data pages remain untouched. Therefore, this procedure is neither as time-consuming nor as disk-space intensive as modifying the table structure using the Change Storage Structure option. Shrink B-tree Index, however, does not re-sort the data on the data pages. Modifying the structure to B-tree is the only option for resorting data on data pages.
|
|
|
You can extend (add pages to) a table or index. You must specify the number of pages you want to add. Using this option does not rebuild the table or drop any secondary indexes.
In VDBA, you can extend a table or index by enabling the Add Pages radio button in the Modify Table Structure or Modify Index Structure dialogs and specifying the number of pages to add.
In SQL, you can accomplish this task with the modify statement. The "with extend" clause is the same as the Add Pages option. For more information, see the SQL Reference Guide.
|
|
|
Secondary indexes are destroyed by default when you modify the base table storage structure. They are destroyed automatically because secondary indexes use the tidp column to reference the row of the base table to which they are pointing. When you modify a table, all the tids of the rows in the base table change, rendering the secondary index useless. For more information, see Tids.
|
|
|
You can use the Persistence option when creating or modifying a secondary index to specify that the index be recreated whenever the base table is modified.
In VDBA, this option is found in the Structure of Index and the Create Indexes dialogs. By default, indexes are created with no persistence.
In SQL, you can accomplish this task with the create index and modify statements. The [no]persistence clause is the same as the Persistence option. For more information, see the SQL Reference Guide.
|
|
|
For example, assuming the secondary index empidx was created without enabling the Persistence option, you can modify it to enable this feature, as follows:
|
|
|
The default storage structure for secondary indexes is ISAM; you can choose a different structure when creating an index.
To do this in VDBA, use the Create Indexes dialog.
You can also modify the index to another storage structure after it has been created.
To do this in VDBA, use the Structure of Index dialog.
If a secondary index is modified to B-tree, it cannot contain any data pages. Instead, the leaf pages in the secondary index point directly to data pages in the main table.
Overflow can occur in secondary indexes, as well as base tables, and must be monitored. One way to handle overflow is to use B-tree as the default index structure. If overflow is not a problem, ISAM or hash can be preferable because the indexes are smaller, require less locking, and reuse deleted space.
Secondary indexes are smaller and can be modified more quickly than the base table. When they are used, overflow occurs less frequently because only key values are stored, rather than the entire row.
Because it is quicker to build secondary indexes than to modify the base table, it is easier to experiment with different choices of secondary indexes and different storage structures for them. Remember, however, that it can take longer to update a table with secondary indexes than one without them.
A high degree of duplication in a secondary key can lead to overflow in the secondary index. Repetitive keys are not recommended. Performance benefits can be derived by the inclusion of another column in the secondary index that makes the entire key less repetitive. The less repetitive key reduces the likelihood of overflow chains, resulting in better performance when updates made to the base table require updates to the secondary index. Because overflow chains are reduced, locking and searching overhead is lessened.
If the secondary index to be stored is ISAM or B-tree and the key is not unique, the tidp column is automatically included in the key specified when the index is modified. This achieves key uniqueness without any loss of functionality when the key is used for matches.
|
|
|
The following example creates a B-tree index for the emp table:
|
|
|
This example modifies an existing index to use the B-tree storage structure (assuming it was created using another storage structure):
|
|
|
If you suspect that the data on the data pages is scattered over several data pages, you can modify the table to B-tree again. You can check this by retrieving the tids as well as the column values, and looking at the pages they reflect.
Remodifying sorts the data and builds the B-tree index, placing like keys on the same data pages, which can slightly reduce the number of disk I/Os required to access the data. For more information, see Tids.
This type of modification is especially useful when the key size is small, the row size is large, and the data has not been appended in sorted order. Remodifying a B-tree is also useful when you have deleted many rows and must reclaim disk space. For more information, see Tracking of Used and Free Pages.
|
|
|
The first example represents the table before modification, and the second example shows it after modification.
The following retrieval touches all three data pages before modification but only one page after modification:
select * from emp where emp.age = 35;
The following table shows the leaf and data pages prior to modification. The records with a key of 35 are found on several data pages:
Leaf Page
key page,row (tid)
35 1,2 (514)
35 2,2 (1026)
35 3,3 (1539)
36 2,3 (1027)
37 3,2 (1538)
Data Pages
Page 1 Page 2 Page 3
1,1 (513) 29 2,1 (1025) 29 3,1 (1537) 30
1,2 (514) 35 2,2 (1026) 35 3,2 (1538) 37
1,3 (515) 30 2,3 (1027) 36 3,3 (1539) 35
The following example modifies the emp table, respecifying B-tree as its structure.
After you perform this modification, the table looks as follows. All records with a key of 35 are clustered together on Page 2:
Page 1 Page 2 Page 3
1,1 (513) 29 2,1 (1025) 35 3,1 (1537) 36
1,2 (514) 29 2,2 (1026) 35 3,2 (1538) 37
1,3 (515) 30 2,3 (1027) 35
|
|
|
When using the modify procedure in VDBA, the most common errors include:
You can locate these rows with the following query:
select key_col, count(*) as repeat_number
from table_name
group by key_col
having count(*) > 1;