Defining Table Storage

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

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 table's indexes 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 Storage tab of the Table Editor.

You can define how the indexes in your tables are stored using the options in the Index tab of the Table Editor. For more information, see Defining Index Storage.

Also to be considered, is how to store the overflow records when a row is updated and no longer fits on the page where it was originally written. The Overflow tab of the Table Editor is available for index-organized tables on the Oracle 8.x and 9.x platforms. For more information, see Define Table Overflow Options:

  1. In the Data Model Window, double-click the table for which you want to specify storage options.
  2. From the Table Editor, select the Storage tab.
  3. Complete the Storage tab options as required and then click OK to exit the editor.
  4. The table storage options available in the Table Editor depend on the database platform.

Available platforms

Click a link below for information on the storage options for your database platform:

Firebird Table Storage Options

External File Specifies the file where the table data is stored.

GreenPlum Table Storage Options

Tablespace ( TABLESPACE ) Displays the tablespace on which the table is stored.

HiRDB LOB Column Storage

If the column datatype specified on the Datatype tab is BLOB, this tab becomes available where you can specify a large object storage location.

HiRDB Table Storage Options

FIXX Specifies a fixed length table, without null data. Select this option to reduce table access time and thus improve performance.
Table Storage Select to enable the IN and PARTITIONED BY options. Select IN to define the RD area of the table, the space where the table and index information is stored.

Select PARTITIONED BY to define the partition setting, such as (RDINDX19) 10, (RDDATA10).

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 a data row's length. Tables that will not be updated should have this value set to 0. Specify PCTFREE in one of the following formats:
  • UNUSED-SPACE-PERCENTAGE
  • (UNUSED-SPACE-PERCENTAGE, FREE-PAGES-PERCENTAGE-PER-SEGMENT)
  • (, FREE-PAGES-PERCENTAGE-PER-SEGMENT)
Lock Mode Select the locking mode. Select Page to lock all rows on a page when one row is locked for update. Select Row to lock only the row being updated.

Hive Column Storage

Row Format Row Format determines the format of the data.
  • Delimited: Can be specified if data are delimited. The escape character is available in case your clause contains the delimiter character. A custom NULL format can also be specified using 'NULL DEFINED AS' clause (default is '\N').
  • SerDe: Used to enter the Serializer/Deserializer clauses and options for custom Storage Handlers for reading/writing data. If not specified, native SerDe handlers will be used.
Stored As/Stored By
  • Stored As: Option for defining type of stored file (how the file will be stored), TEXTFILE, SEQUENCEFILE (compressed), BINARY SEQUENCEFILE, ORC, RCFILE.
  • Stored By: Used to enter the Serializer/Deserializer clauses and options for custom Storage Handlers for creating tables and accessing data stored in other systems (for example HBase).
Location The file system directory used to store the data files. Usage example: If the table is External the data will not be deleted during a DROP TABLE.
As Select Specifies any valid select-statement.
Table Properties Used for tagging the table definition.

IBM DB2 Common Server and IBM DB2 for LUW Table Storage Options

Database Displays the database on which the table is stored. Enter the name of the database or choose a database from the list.
Tablespace Displays the tablespace on which the table is stored. Enter the name of the tablespace or choose another tablespace from the list.
Encoding Select to enable encoding and then choose the encoding format desired. Creates a CCSID statement.
Restrict On Drop Select to prevent users from accidentally dropping the table (removing the table from the database) until the RESTRICT ON DROP attribute is removed. Creates a RESTRICT ON DROP statement.
Volatile (VOLATILE) Select to define the table as volatile so that when querying the table, the DB2 optimizer uses the indexes defined on the table even though statistics can indicate that using the index would result in slower performance. This prevents the optimizer from using table statistics that are out of date that would result in poor performance.
Log Data Changes (DATA CAPTURE) Select to log INSERT, UPDATE, and DELETE statements.
Table Procedures (EDITPROC and VALIDPROC) Specify edit procedures and validation procedures which can be run against the table. Creates the EDITPROC and VALIDPROC statements.
Audit Option (AUDIT CHANGES or AUDIT ALL) Specify the audit policy to use to monitor table activity. Audited events are determined by the server's associated audit policy.
Index Tablespace Displays the tablespace on which the table index is stored. Choose another tablespace from the list.
LONG Tablespace Displays the name of the tablespace on which the long objects (LOB, CLOB) stored on the table reside. Choose another tablespace from the list if you like.
Log for Replication Select to keep a record of all changes in the table. Replication enables the data to be copied from a source table to one or more target tables and synchronize the changes in the source to the target.
Not Logged Initially Select to prevent logging on operations that are performed in the same unit of work in which the table is created; however, other operations against the table in subsequent units of work are logged.

IBM DB2 for z/OS Table Storage Options

Database Displays the database on which the table data is stored. Choose another database from the list if you like.
Tablespace Displays the tablespace on which the table data is stored. Choose another tablespace from the list if you like.
Log Data Changes Select to log any changes to the table data.
Audit Option Choose the audit option desired. Record activity related to changes only or record all access activities.
Edit Proc Enter the name of an edit routine that edits or encodes a row whenever a row is inserted or updated. Typically used to compress the storage of rows to save space, and to encrypt the data.
Valid Proc Enter the name of the validation routine that validates the data whenever a row is inserted or updated. Typically used to impose limits on the information that can be entered in a table; for example, allowable salary ranges for job categories in the employee table.
Encoding Select to enable encoding and then choose the encoding format desired.
Restrict On Drop (RESTRICT ON DROP) Select to prevent users from accidentally dropping the table (removing the table from the database) until the RESTRICT ON DROP attribute is removed.
Volatile Select to define the table as volatile so that when querying the table, the DB2 optimizer uses the indexes defined on the table even though statistics can indicate that using the index would result in slower performance. This prevents the optimizer from using table statistics that are out of date that would result in poor performance.

Informix Table Storage Options

The options available depend on the version of the database.

DB Space Specifies the name of the database in which the table data is stored.
Lock Mode Specifies the locking mode. Select Page to lock all rows on a page when one row is locked for update. Select Row to lock only the row being updated.
Initial Extent (INITEXTENT) Specifies the table's initial number of data blocks. Informix will reserve the data blocks that correspond to the initial extent for that table's rows.
Next Extent (NEXT) Specifies the size of the next extent, in kilobytes. Monitor this value in comparison with the largest available chunk of free space in the tablespace if the free space is less than the next extent size then the table extent cannot be allocated; the table will no longer be able to extend and, therefore, cannot accept additional data.
Path Name Specifies the full path name of the database in which the table data is stored.

Interbase Table Storage Options

External File Specifies the file where the table data is stored.

Microsoft SQL Server Table Storage Options

The options available depend on the version of the database platform.

Segment Specifies where the table data is stored.
File Group Specifies the name of the file group where the table data is stored.
Partition Scheme Specifies the name of the partition scheme created that associates the partitions with their physical location in a file group.
Text Image File Group Specifies the name of the file group where text or image data for the table is stored. This option becomes available when any of the columns in the table have an image datatype.
Partition Keys Specifies the partition key. This column guides how data is divided along the partition boundaries. Typically, a partition key is data-based or a numerically ordered data grouping. You can choose from all available columns in the table.

Oracle 7 Table Storage Options

Tablespace Specifies the name of the tablespace where the table data 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 tables 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 a data row's length. Tables that will not be updated should have this value set to 0.
Percent Used (PCTUSED) Specifies the maximum percentage of available space in each data block before re-adding it to the list of available blocks. When deletes take place and the room available in a block falls below this value, the block is made available for new inserts to take place. Tables that won't be updated should have this value set to 99. The default value is 40% means that blocks are available for insertion when they are less than 40% full.
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. 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 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 materialized view. Once this limit is reached, Oracle prevents further growth of the cluster and cannot accept additional data. As a result, you should carefully monitor the number extents already allocated to the table with this limit.

Oracle 8.x, 9i, 10g, and 11g Table Storage Options

The options available depend on the version of Oracle used.

Heap Organizes the table with physical rowids. Oracle Corporation does not recommend that you specify a column of datatype UROWID for a heap-organized table.
Cache (CACHE) Select for data that is accessed frequently to specify that blocks retrieved for this table be placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.
Index Organized (ORGANIZATION INDEX) Groups data rows according to the primary key. It the table is Index Organized you can specify how overflow is handled. For more information, see Define Table Overflow Options. You can also specify that the index be compressed. For more information, see Defining Index Storage.
Temporary Table (GLOBAL TEMPORARY TABLE) Creates a global temporary table whose definition is visible to all sessions but the data is visible only to the session that created it until the session or transaction is committed. The data persists in the temporary table at either the session or transaction level based on how ON COMMIT is specified. You cannot use this option for tables with relationships.
DELETE ROWS Deletes the temporary table with the user ends the transaction by issuing a commit statement.
PRESERVE ROWS Retains the table changes until the session is ended.
Enable Table Compression (COMPRESS) Select to enable compression, which can reduce disk and buffer cache requirements, while improving query performance in addition to using fewer data blocks thereby reducing disk space requirements. This is most useful for fact tables in a data warehouse environment.
Enable Row Movement (Enable Row Movement) Select to allow Oracle to change ROWIDs to condense table rows and make it easier to reorganize tables. However, this option also allows Oracle to move a row to a discontinuous segment which can cause performance problems. This option must be enabled in order for you to use Oracle features such as ALTER TABLE SHRINK, flashback table, and table reorganization.
Tablespace Specifies the name of the tablespace on which the table is stored.
No Logging (NOLOGGING) Select if you do not want the DLL operations to be logged in the redo file. This can reduce index creation and updates by up to 30%.
Parallel (PARALLEL) Selects Oracle's parallel query option, allowing for parallel processes to scan the table. You can achieve substantial performance gains by using Oracle's parallel query option.
Degrees Specifies the number of query server processes that should be used in the operation. Usually this would be the number of CPUs on the Oracle server -1.
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.
Pct Used (PCTUSED) Specifies the maximum percentage of available space in each data block before re-adding it to the list of available blocks. When deletes take place and the room available in a block falls below this value, the block is made available for new inserts to take place. Tables that will not be updated should have this value set to 99. The default value is 40% means that blocks are available for insertion when they are less than 40% full.
Initial Trans (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. Initial Transactions limit the minimum number of concurrent transactions that can update a data block to avoid the overhead of allocating a transaction entry dynamically. Specify a higher value for tables that may experience many transactions updating the same blocks.
Max Trans (MAXTRANS) Specifies the maximum number of concurrent transactions that can update a data block to avoid performance problems. 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. 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 (MINEXTENT) 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 (MAXEXTENT) Species the maximum number of extents that Oracle can allocate to the materialized view. 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 table with this limit.
Free Lists (FREELISTS) Specifies the number of free lists to apply to the table. 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 table has up to 20 end users performing INSERT 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 table 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, free list 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.

Oracle LOB Segment Storage Options

If a datatype is defined as LOB, CLOB or NLOB on the Dataype tab of the Table Editor, the Edit LOG Segment option is available, which when clicked opens the LOG Segment editor. The following describes the options available on the LOG Segment editor:

Segment Name Specifies the name of the segment on which the LOB data is stored.
Tablespace Specifies the name of the tablespace on which the LOB data is stored.
Blocks Accessed at one time (CHUNK) Determines the granularity of allocation for out of row LOBs.
Percent Space Used for New Version (PCTVERSION) Controls the mechanism that stores the previous image within the data block. The default value for PCTVersion is 10, reserving 10% of the LOB storage space for UNDO operations.
Enable LOB Storage In Row An LOB can be stored either along the row to which it belongs (in row storage) or in the lob segment (out of row storage). The maximum size for in row LOBs is 3964 bytes.
LOB Cache (CACHE) Select for data that is accessed frequently to specify that blocks retrieved for this column be placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed.
Logging (LOGGING) Select if you want the DLL operations to be logged in the redo file. This can increase index creation and updates by up to 30%.
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. 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 (MINEXTENT) 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 (MAXEXTENT) Species the maximum number of extents that Oracle can allocate to the materialized view. 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 table with this limit.
Free Lists (FREELISTS) Specifies the number of free lists to apply to the table. 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 table 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 table 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, free list groups my be set for non-RAC systems.

PostgreSQL Table Storage Options

Table Space Specifies the name of the tablespace on which the table data is stored.

Snowflake Table Storage Options

The following options are Persistence types.

Permanent Specifies a regular persistent table. Selected by default.
Transient Specifies that the table is transient. In contrast to temporary tables, a transient table exists until explicitly dropped and is visible to any user with the appropriate privileges. However, transient tables have a lower level of data protection than permanent tables, meaning that data in a transient table might be lost in the event of a system failure. As such, transient tables should be used only for data that can be recreated externally to Snowflake.
Temporary / Temporary (Local) / Temporary (Global) / Volatile Specifies that the table is temporary. A temporary table persists only for the duration of the user session in which it was created and is not visible to other users. A temporary table and all its contents are dropped at the end of the session.

The synonyms and abbreviations for TEMPORARY are provided for compatibility with other databases (e.g. to prevent errors when migrating CREATE TABLE statements). Tables created with any of the keywords appear and behave identically to tables created using TEMPORARY.

The following option also is available.

DATA_RETENTION_TIME_IN_DAYS Specifies the retention period for the table so that Time Travel actions (SELECT, CLONE, UNDROP) can be performed on historical data in the table.

Sybase Table Storage Options

The options available depend on the version of the database platform.

DB Space Specifies the name of the database where the table data is stored.
Segment Specifies the name of the segment where the table is stored.
Locking Scheme Specifies the locking scheme to implement for DDL transactions.
ALLPAGES Locks the data pages and index pages affected by the query.
DATAPAGES Locks the data pages but not the index pages, and the lock is held until the end of the transaction.
DATAROWS Locks the individual rows on data pages; rows and pages are not locked. This scheme requires more overhead but is beneficial for small tables with much contention.
Max Rows per Page Specifies the maximum number of rows per page.
Reserve Page Gap Specify the reserve gap parameters.
Identity Gap Controls the allocation of ID numbers and potential gaps resulting from restarting the server. Creates ID number blocks of a specific size which improves performance by reducing contention for the table. Overrides the server-wide identity burning set factor. If the identity gap is set to 1000m the server allocates the first 1000 numbers and stores the highest number of the block to disk. When all the numbers are used, the server takes the next 1000 numbers. If after inserting row 2050 the power fails, then when the server restarts it will start with ID 3000, leaving an ID gap of 950 numbers. Each time the server must write the highest number of a block to disk, performance is affected so you must find the best setting to achieve the optimal performance with the lower gap value acceptable for your situation.
Replacement Strategy Select to use the fetch-and-discard (or MRU) replacement strategy that reads pages into cache at the most recently-used (MRU) end of the buffer, so it does not flush other pages. But these pages remain in cache a much shorter time, so subsequent queries are less likely to find the page in cache.
Prefetch Strategies Enables or disables large I/O for a session.

Teradata Table Storage Options

Journal Table (WITH JOURNAL TABLE) Specifies the name of the journal table in the format database_name.table_name. If the database name is not specified, the default database for the current session is assumed. If a default journal table was defined for the database, then this option can override the default. The journal table stores an image of the table before initiating a transaction. If the transaction completes successfully, the journal is deleted; if the transaction fails, the journal data is used to perform a rollback.
Before Journal (BEFORE JOURNAL) Specifies the number of before change images to be maintained. If the JOURNAL keyword is specified without NO or DUAL, then a single copy of the image is maintained unless FALLBACK is in effect or is also specified. If journaling is requested for a table that uses fallback protection, DUAL images are maintained automatically.
After Journal (AFTER JOURNAL) Specifies the number of after-image to be maintained for the table; any existing images are not affected until the table is updated.
Freespace Specifies percent free space (between 0 and 75) that remains on each cylinder during bulk data load operations on this table. This reserved space helps to avoid row migration and chaining caused by an update operation that extends a data row's length. Tables that won't be updated should have this value set to 0.
Datablock Size Specifies the maximum data block size for blocks contain multiple rows. Larger block sizes enhance full-table scans by selecting more rows in a single I/O; smaller block sizes are best for transaction-oriented tables to minimize overhead by retrieving only what is needed. The maximum data block size is 127.5 Kilobytes.
Duplicate Row Control Specifies whether or not duplicate rows are permitted. Specify Set to disallow duplicate rows and Multiset to allow duplicate rows.
Checksum (CHECKSUM) Checksum detects and logs disk I/O errors. The choices available determine the percentage of data used to compute the checksum for the table. This is an example of the SQL generated when Checksum > Medium is selected on the Storage tab of the Table Editor: Checksum = Medium
Fallback Protection (FALLBACK) Select this option to implement software fault tolerance which copies the primary table to create a duplicate copy.

Define Table Overflow Options

The Table Editor Overflow tab specifies how to store the overflow records when a row is updated and no longer fits on the page where it was originally written. The Overflow tab of the Table Editor is available for index-organized tables on the Oracle 8.x, 9i, 10g, 11g and 12c platforms.

  1. In the Data Model Window, double-click the table for which you want to specify storage options.
  2. From the Table Editor, select the Overflow tab.
  3. Complete the Overflow tab options as required and then click OK to exit the editor.

The Overflow tab creates an ORGANIZATION INDEX clause in the CREATE TABLE statement. Any options you specify on this tab will follow this clause.

The following describes options on the Overflow tab that require additional explanation.

Tablespace Specifies the name of the tablespace on which the overflow is stored.
No Logging Select if you do not want the DLL operations to be logged in the redo file. This can reduce index creation and updates by up to 30%.
Pct Threshold (PCTTHRESHOLD) Specifies the percentage of space reserved in the index block for the index-organized table. Any portion of a row that exceeds this threshold is stored in the overflow segment.
Including (INCLUDING) Select the column to include in the overflow.
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.
Pct Used (PCTUSED) Specifies the maximum percentage of available space in each data block before re-adding it to the list of available blocks. When deletes take place and the room available in a block falls below this value, the block is made available for new inserts to take place. Tables that won't be updated should have this value set to 99. The default value is 40% means that blocks are available for insertion when they are less than 40% full.
Initial Trans (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 can 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 "http://docwiki.embarcadero.com/ERStudioDA/184/e/index.php?title=Defining_Table_Storage&oldid=5839"