Go Up to Developing the Physical Model
Storage parameters affect both how long it takes to access data stored in the database and how efficiently space in the database is used. For heavily accessed tables, place these tables in one location and place the indexes of the table in a different location on different physical disk arrays. This will improve performance, because separate threads will be created to access the tables and indexes.
You can define how your tables are stored using the options in the Index Editor, which is accessible through the Index tab of the Table Editor.
- In the Data Model Window, double-click the table for which you want to specify index storage options.
- From the Table Editor, click the Indexes tab, select the index for which you want to specify storage options, and then click Edit.
- The Index Editor appears with options that are specific to the database platform and version.
- Complete the Index Editor options as required, click OK to exit the Index Editor, and then click OK again to exit the Table Editor.
For information on the storage options available for your database platform, click a link below:
GreenPlum Index Storage Options
| Tablespace (TABLESPACE) |
Specifies the name of the tablespace where the index is stored.
|
Hitachi HiRDB Index Storage Options
| Index Storage (ON) |
Specifies where to store the index.
|
| PCTFREE (PCTFREE) |
Specifies the maximum percentage of space in each data block to reserve for future updates. This reserved space helps to avoid row migration and chaining caused by an update operation that extends the length of a data row. Tables that will not be updated should have this value set to 0.
|
| Unbalanced Split (UNBALANCED SPLIT) |
Specifies that in order to maintain the balance of the index tree and improve performance the index pages can be split.
|
IBM DB2 for z/OS Index Storage Options
The storage options for an IBM DB2 for z/OS appear on the Options tab of the Index Editor for that platform. For more information, see IBM DB2 for z/OS Table Storage Options.
Informix Index Storage Options
| Fill Factor (FILLFACTOR) |
Specifies the percent or degree of index-page fullness. A low value provides room for index growth. A high value compacts the index. If an index is full (100 percent), any new inserts result in splitting nodes.
|
Interbase Index Storage Options
| External File |
Specifies where to store the index.
|
Microsoft SQL Server Index Storage Options
The options available depend on the version of the database.
| Segment (ON) |
Specifies where the index will be stored.
|
| File Group (ON) |
Specifies where the index will be stored.
|
| Fill Factor (FILLFACTOR) |
Specifies the percent or degree of index-page fullness. A low value provides room for index growth. A high value compacts the index. If an index is full (100 percent), any new inserts result in splitting nodes.
|
| Pad Index (PAD_INDEX) |
Specifies how much space to leave available on each page in the intermediate levels of the index. If PAD_INDEX is not used, by default each intermediate index page is given only enough empty space to hold at least one row of the maximum size the index can have. For tables with heavy index updates, this can cause the intermediate index pages to split often, causing unnecessary overhead. To prevent intermediate index page splitting, assign both the FILLFACTOR and the PAD_INDEX options. The PAD_INDEX option uses the FILLFACTOR, and applies it to the intermediate index pages. For example, if a FILLFACTOR of 80 is specified, then the PAD_INDEX will use this value, and only fill 80% of the intermediate index page space, instead of leaving enough space for just one new row.
|
| Sort In Tempdb (SORT_IN_TEMPDB) |
Specifies to use tempdb to store intermediate sort results that are used to build the index. Although this increases the amount of temporary disk space that is used to create an index, it could reduce the time required to create or rebuild an index when tempdb is on a disparate set of disks from that of the user database.
|
| Partition Keys |
Select the key to use when partitioning the index. Partitioning the index can substantially reduce query time, improving the load time and maintainability of the index.
|
| Compression |
Specifies the data compression option for the specified table, partition number, or range of partitions. Options include:
- NONE: (Default) Table or specified partitions are not compressed.
- ROW: Table or specified partitions are compressed using row compression.
- PAGE: Table or specified partitions are compressed using page compression.
|
| On Partitions |
Specifies which partitions are affected. If this field is blank, the compression selection applies to all partitions of a partitioned index.
|
Oracle Index Storage Options
| Tablespace (TABLESPACE) |
Specifies the name of the tablespace where the index is stored.
|
| Initial Transactions (INITTRANS) |
Specifies the number of DML transactions for which space is initially reserved in the data block header. Oracle stores control information in the data block to indicate which rows in the block contain committed and uncommitted changes. The amount of history that is retained is controlled by this parameter. If too many transactions concurrently modify the same data block in a very short period, Oracle may not have sufficient history information to determine whether a row has been updated by a "too recent" transaction. Specify a higher value for indexes that may experience many transactions updating the same blocks.
|
| Max Transactions (MAXTRANS) |
Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of any available free space. Once allocated, this space effectively becomes a permanent part of the block header. This parameter limits the number of transaction entries that can concurrently use data in a data block and therefore limits the amount of free space that can be allocated for transaction entries in a data block.
|
| Percent Free (PCTFREE) |
Specifies the maximum percentage of space in each data block to reserve for future updates. This reserved space helps to avoid row migration and chaining caused by an update operation that extends the length of a data row. Tables that will not be updated should have this value set to 0.
|
| Initial Extent (INITEXTENT) |
Specifies the initial number of data blocks that Oracle should reserve. Oracle will reserve the number of data blocks that correspond to the initial extent for the rows of that table.
|
| Next Extent (NEXT) |
Specifies the size in kilobytes of the next extent. You should monitor this figure against the largest available chunk of free space in the tablespace. If a table cannot allocate its next extent, it will no longer be able to extend and, therefore, cannot accept additional data.
|
| Percent Increase (PCTINCREASE) |
Specifies the percentage by which the next extent should grow over the size of the previous extent. Be careful when setting Percent Increase because it magnifies how an object grows and, therefore, can materially affect available free space in a tablespace.
|
| Min Extents (MINEXTENTS) |
Specifies the number of extents to allocate when the segment is created. Controls free space fragmentation by making sure that every used or free extent is at least as large as the value you specify.
|
| Max Extents (STORAGE (MAXEXTENTS)) |
Species the maximum number of extents that Oracle can allocate to the index. Once this limit is reached, Oracle prevents further growth of the cluster and cannot accept additional data. Carefully monitor the number extents already allocated to the index with this limit.
|
Postgre SQL Index Storage Options
| Tablespace (TABLESPACE) |
Specifies the name of the tablespace where the index is stored.
|
Sybase SQL Anywhere Index Storage Options
| DB Space (IN) |
Specifies where the index is stored.
|
Sybase System 10, ASE Index Storage Options
| Segment (ON) |
Specifies where the index will be stored.
|
| Max Rows per Page (MAX_ROWS_PER_PAGE) |
Specifies the maximum number of rows per page. This restricts the number of rows allowed on a page, reducing lock contention and improving concurrency for frequently accessed tables. The default value is 0, which creates clustered indexes with full data pages, non clustered indexes with full leaf pages, and leaves a comfortable amount of space within the index B-tree in both the clustered and nonclustered indexes.
For heap tables and clustered indexes, the range for MAX_ROWS_PER_PAGE is 0-256. For non clustered indexes, the maximum value for MAX_ROWS_PER_PAGE is the number of index rows that fit on the leaf page, without exceeding 256. To determine the maximum value, subtract 32 (the size of the page header) from the page size and divide the difference by the index key size.
|
| Fill Factor (FILLFACTOR) |
Specifies the percent or degree of index-page fullness. A low value provides room for index growth. A high value compacts the index. If an index is full (100 percent), any new inserts result in splitting nodes.
|
See Also