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.
● Instances: Specifies how you want the parallel query partitioned between the parallel servers.
● 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 won’t 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. 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 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.
● 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.