Partitioning a Table Index

From ER/Studio Data Architect
Jump to: navigation, search

Go Up to Developing the Physical Model

For database platforms that support this feature, partitioning the index can substantially reduce query time, limiting access to relevant partitions only. A partitioned index can be efficiently maintained. Partition independence enables a partition to be maintained without affecting the availability of other partitions.

You can access the Index Partition Editor through the Table Editor > Index tab, where you can choose to partition the index and add new partitions as required. The options available in the Index Partition Editor depend upon the selected database platform. For information on the options for your database, click one of the following links:

IBM DB2 for z/OS Index Partitions Options

  • For IBM DB2 for z/OS, the Cluster option on the Options tab must be selected in order to enable the partitioning options on the Partitions tab. Click Add on the Partitions tab to launch the Index Partition Editor.
  • High Value: Lets you specify the maximum value of the column for this partition, entries that exceed this amount will not be included in the partition.
  • Vcat: ( VCAT ) If selected, allocates space for the index on a data set managed using the virtual catalog ( VCAT ) whose name is specified next to this option, and the user manages the data sets required for the index. The VCAT method is usually used for defining system catalogs. It requires that the VSAM dataset be pre-defined before the index space is created.
  • StoGRoup: ( STOGROUP ) If selected, stores the index on a data set managed by DB2 in the named storage group. Stogroup defined index spaces let DB2 do all the VSAM allocation work for you. If you have created a Stogroup for the data model, using the Stogroup Editor (see [ERStudio.htm#50614277_67903 See Creating and Editing StoGroups]), you can select that Stogroup from the list. If you select Stogroup, you can also define values for PRIQTY and SECQTY. To prevent wasted space for non-partitioned indexes, you should not define PRIQTY and SECQTY ; instead let DB2 manage the amount of primary and secondary space allocated for the index.
  • PriQty: ( PRIQTY ) Specifies the minimum number of disk space in kilobytes to initially allocate for the index. The primary allocation should be large enough to handle the storage needs that you anticipate.
  • SecQty: ( SECQTY ) Specifies the amount of disk space in kilobytes to allocate to the index when it reaches the size of the primary allocation. If the secondary allocation space is too small, the data set might have to be extended more times to satisfy those activities that need a large space. The default value of -1 indicates that DB2 should determine the size of the secondary allocation, which is usually 10% of the primary allocation.
  • 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 a data row's length. Tables that will not be updated should have this value set to 0.
  • Free Page: ( FREEPAGE ) Specifies how often to leave a page of free space when index entries are created. One free page is left for every number of pages specified here in the range of 0 to 255.
  • GBP Cache: ( GPBCACHE ) In a data sharing environment, specifies what index pages are written to the group buffer pool. This option is ignored in a non-data-sharing environment unless the index is on a declared temporary table.
  • Changed: Writes updated pages to the group buffer pool.
  • All: Caches all pages to the group buffer pool as they are read in from DASD.
  • None: Uses the buffer pool only for cross-invalidation.


Oracle Index Partitions Options

Options available on the Partitions tab of the Index Editor depend on the version of the database platform.

  • Type: Select the partition type.
  • Range: ( PARTITION BY RANGE (column_name) ) Select to store distinct ranges of data together, such as dates. Searches can then be limited to partitions with data that is the correct age. Also, once historical data is no longer useful, the whole partition can be removed.
  • Hash: ( PARTITION BY HASH (column_name) PARTITIONS count STORE IN tablespace) ) Select if there is no obvious range key or if range partitioning might cause uneven data distribution. If selected, the Partition Count option is available.
  • Partition Count: Click to launch the Hash Partition Editor where you can specify the number of subpartitions and where to store them.
  • Available Columns: Displays the columns available to add to a partition.
  • Selected Columns: Displays the columns that make up the partition key. Guides how data is divided along the partition boundaries. Typically, a partition key is date-based or based on a numerically ordered data grouping. Use the left and right arrows to move columns to and from this grid.
  • Up / Down buttons: Let you reorder the columns on the partition. The partition order can affect the access speed. The most frequently accessed columns should be at the top of the partition list.
  • Index Scope: Lets you specify the type of index partition to create.
  • Local: ( LOCAL ) All index entries in the partition correspond to a one table partition (equi-partitioned). Supports partition independence, allowing for more efficient queries.
  • Global: ( GLOBAL ) The index in the partition can correspond to many table partitions. Does not support partition independence and must be partitioned by range. If specified, you must specify the partition range values.
  • Add: Click to launch the Index Partition Editor where you can add table partitions. For more information, see Index Partition Editor for Oracle Options.
  • Edit: Click to launch the Index Partition Editor where you can add table partitions. For more information, see Index Partition Editor for Oracle Options.

Index Partition Editor for Oracle Options

  • Max Value: ( VALUES LESS THAN (MAXVALUE) ) Select to create a catch-all partition for values that exceed all specified ranges. Otherwise, specify a High Value for the partition range.
  • High Value: ( VALUES LESS THAN (high value) ) Specifies the ending value for the partition range.
  • Pct 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 a data row's length. Tables that will not be updated should have this value set to 0.
  • Initial: ( 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 Trans: ( 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.
  • 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 that table's rows.
  • 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.
  • Pct Increase: ( PCTINCREASE ) Specifies the percentage by which the next extent should grow over the previous extent's size. 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: ( MAXEXTENTS ) Specifies 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.
  • Free Lists: ( FREELISTS ) Specifies the number of free lists to apply to the index. The default and minimum value is 1. Free lists can help manage the allocation of data blocks when concurrent processes are issued against the index. Oracle uses the free list to determine which data block to use when an INSERT operation occurs. Oracle allows table and indexes to be defined with multiple free lists. All tables and index free lists should be set to the high-water mark of concurrent INSERT or UPDATE activity. For example, if the index has up to 20 end users performing INSERTs at any time, then the index should have FREELISTS=20.Too low a value for free lists will cause poor Oracle performance. An increase in FREELISTS or FREELIST_GROUPS can alleviate segment header contention.
  • Free List Groups: ( FREELIST GROUPS ) Applicable only if you are using Oracle with the Parallel Server option in parallel mode. Specifies the number of free list groups which allow the index to have several segment headers. This enables multiple tasks to insert into the index; thereby alleviating segment header contention. Free list groups should be set to the number of Oracle Parallel Server instances that access the index. For partitioned objects and cases of segment header contention, freelist_groups my be set for non-RAC systems.
  • Buffer Pool: ( BUFFER_POOL ) Specifies the memory structure that is used for caching data blocks in memory, providing faster data access.
  • DEFAULT caches data blocks in the default buffer pool.
  • KEEP retains the object in memory to avoid I/O conflicts.
  • RECYCLE removes data blocks from memory as soon as they are no longer in use, thereby saving cache space.

See Also

Retrieved from "https://docwiki.embarcadero.com/ERStudioDA/191/e/index.php?title=Partitioning_a_Table_Index&oldid=5899"