Changes in This Release for Oracle Database VLDB and Partitioning Guide

Automatic big table caching enhances in-memory query capabilities of Oracle Database in both single instance and Oracle Real Application Clusters (Oracle RAC) environments using a temperature based algorithm with the big table cache.

In Oracle RAC environments, this feature is supported only with parallel queries. In single instance environments, this feature is supported with both parallel and serial queries.

For information about integrating queries with the buffer cache using automatic big table caching, refer to "Automatic Big Table Caching".

You can specify that individual partitions are loaded into the In-Memory Column Store using the INMEMORY clause with the partitioning clauses of the CREATE TABLE and ALTER TABLE SQL statements. For an example, refer to "Creating a Table Using In-Memory Column Store With Partitioning".

Force full database caching mode enables you to cache the entire database in memory, which may provide substantial performance improvements when performing full table scans or accessing LOBs.

Attribute clustering of tables enables you to store data in close proximity on disk in a ordered way that is based on the values of certain columns in the table.

For partition maintenance operations, tables with the clustering option enabled continue to be clustered unless the partition clustering option specifically prohibits it.

A zone map is an access structure that enables pruning during scan disk blocks of a table based on predicates on its columns.

A zone is a set of contiguous data blocks that stores the minimum and maximum values of relevant columns. When a SQL statement contains predicates on columns stored in a zone, the database compares the predicate values to the minimum and maximum stored in the zone to determine which zones to read during SQL execution. This significantly improves the I/O and CPU cost of scans.

Zone maps can be combined with attribute clustering. The primary benefit of attribute-clustered tables is I/O pruning, which can significantly reduce the I/O cost and CPU cost of table scans. For information about pruning with zone maps, refer to "Partition Pruning with Zone Maps".

Advanced index compression is a next generation compression solution provided by Oracle. Creating an index using advanced index compression reduces the size of all supported unique and non-unique indexes. Advanced index compression improves the compression ratios significantly while still providing efficient access to the indexes. Advanced compression works well on all supported indexes, including those indexes that are not good candidates for prefix compression.

For a partitioned index, you can specify the compression type on a partition by partition basis. You can also specify advanced index compression on index partitions even when the parent index is not compressed.

For information about advanced index compression and index partitioning, refer to "Advanced Index Compression With Partitioned Indexes". For information about partitioning and table compression, refer to "Partitioning and Table Compression".

Oracle XML DB and other applications that use domain indexes can use hash partitioned methods. Hash partitioning is an effective approach to balancing I/O evenly over a series of partitions. The advantages of this partitioning method are now available to users of XML DB and other applications that use domain indexes. For more information, refer to "Hash Partitioning".

Hash, list, and range partitioning are supported for XMLIndex. For more information, refer to "Partitioning of XMLIndex for Binary XML Tables".

Partitioned hash clusters are supported in the Oracle Database. Only single-level range partitioning is supported for partitioned hash clusters.

This feature enables partition maintenance operations on multiple partitions, providing simplified application development and more efficient partition maintenance.

Multipartition maintenance operations enable adding multiple partitions to a table, dropping multiple partitions, merging multiple partitions into one partition, splitting of a single partition into multiple partitions, and truncating multiple partitions using a single SQL data definition language (DDL) statement. For a summary of maintenance operations that are valid, refer to "Maintenance Operations on Partitions That Can Be Performed"

For more information, refer to "Adding Multiple Partitions", "Dropping Multiple Partitions", "Merging Multiple Partitions", "Splitting into Multiple Partitions", and "Truncating Multiple Partitions".

This feature provides a heat map of hot and cold data, enabling you to specify the archive state for each row in the database as needed and assisting you to implement your Information Lifecycle Management (ILM) strategy. Each row in a database can be managed appropriately based on its archive state, providing fine-grained control over the visibility, compression, and storage tier for all of the data in the database. In combination with other ILM-related features, this feature enables users to automatically optimize their database storage to maximize performance and minimize cost.

You can track both access and modification operations to data at the row, segment, and table levels. You can also implement policy-driven automation based on the information tracked with this feature.

For more information, refer to "Using Heat Map" and "Managing ILM Heat Map and ADO with Oracle Enterprise Manager".

This feature provides SQL statement options for specifying policies at the row, segment, and tablespace level, assisting you to implement your Information Lifecycle Management (ILM) strategy.

You can use this feature to automate the movement of data between different tiers of storage within the database. This includes the ability to specify different compression levels for each tier, and to control when the data movement takes place. You can specify compression at the row and segment level within each table in a database. The combination of row and segment level compression tiering provides fine-grained control over how the data in the database is stored and managed.

For more information, refer to "Using Automatic Data Optimization" and "Managing ILM Heat Map and ADO with Oracle Enterprise Manager".

The In-Database Archiving and Temporal Validity features enable you to manage the validity and visibility of data for real world situations.

For more information, refer to "Controlling the Validity and Visibility of Data in Oracle Database".

The main benefit of this feature is to run multiple remote branches of a UNION or UNION ALL concurrently.

For information, refer to "Concurrent Execution of Union All".

Incremental statistics have been enhanced to support partition exchange loading. Data loaded into a nonpartitioned table can be exchanged with a partition from the table and Oracle automatically and accurate computes the global statistics for the partition table, using the statistics from the nonpartitioned table and the existing partition level statistics. For information, refer to "About Exchanging Partitions and Subpartitions".

This feature adds to existing parallel statement queuing functionality.

Included in this feature:

• Manually running or canceling queued queries

  Queued queries can be manually run or canceled.

  See Also:

  Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_RESOURCE_MANAGER package

• Parallel server limit directive for pluggable database (PDB) plans

  The parallel_server_limit directive replaces the parallel_target_percentage directive. This directive specifies the percentage of the available parallel servers that the consumer group can use when this directive is set.

  For multitenant container database (CDB) resource plans, this limit applies to pluggable databases. For PDB resource plans or non-CDB resource plans, this limit applies to consumer groups.

  For more information, refer to "About Managing Parallel Statement Queuing with Oracle Database Resource Manager".

  See Also:

  • Oracle Database Administrator's Guide for information about Oracle Database Resource Manager support for multitenant container databases

  • Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_RESOURCE_MANAGER package

• Automatic Parallel Queuing Enhancements

  • Queued statement monitoring and analysis

    To facilitate the analysis of queued parallel statements, the V$RSRC_SESSION_INFO view provides the PQ_SERVERS and PQ_STATUS columns.

    V$RSRC_SESSION_INFO.PQ_SERVERS specifies the number of parallel servers used by this parallel operation.

    V$RSRC_SESSION_INFO.PQ_STATUS specifies the reason why the parallel operation is queued.

    For more information, refer to "V$RSRC_SESSION_INFO".

    See Also:

    Oracle Database Reference for information about the V$RSRC_SESSION_INFO view

  • Historical statistics for parallel statement queuing

    Statistics related to parallel statement queuing are added to the resource manager metrics that takes statistics for a given one-minute window and retains them for approximately one hour.

    These statistics enable a DBA to monitor parallel statement queuing over time.

    For more information, refer to "V$RSRCMGRMETRIC".

    See Also:

    Oracle Database Reference for information about the V$RSRCMGRMETRIC view

  • Critical parallel statement prioritization

    The parallel_stmt_critical parameter enables you to mark specific consumer groups as critical in respect to parallel statements in the plan directive.

    This parameter specifies that parallel operations from a particular consumer group should not be queued; instead these statements should be run immediately.

    For more information, refer to "Critical Parallel Statement Prioritization".

    See Also:

    • Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_RESOURCE_MANAGER package

    • Oracle Database Reference for information about the DBA_RSRC_PLAN_DIRECTIVES view

For information about parallel statement queuing, refer to "About Parallel Statement Queuing".

Local and global indexes can be created on a subset of the partitions of a table, enabling more flexibility in index creation. This operation is supported using a default table indexing property. When a table is created or altered, a default indexing property can be specified for the table or its partitions.

For information about creating partial indexes for partitioned tables, refer to "Partial Indexes for Partitioned Tables".

This feature enables global index maintenance to be delayed and decoupled from a DROP and TRUNCATE partition without making a global index unusable. Enhancements include faster DROP and TRUNCATE partition operations and the ability to delay index maintenance to off-peak time.

For more information, refer to "Asynchronous Global Index Maintenance for Dropping and Truncating Partitions".

This feature enables reference-partitioned table to use interval partitioning as a top partitioning strategy, which provides a better partitioning modeling. Interval partitioned tables can be used as parent tables for reference partitioning. Partitions in the reference partitioned table corresponding to interval partitions in the parent table are created upon insert into the reference partitioned table.

For more information, refer to "Creating Interval-Reference Partitioned Tables".

This feature provides a CASCADE option for TRUNCATE PARTITION and EXCHANGE PARTITION operations for reference and interval-reference partitioned tables, which cascades the operation to reference partitioned child tables. This functionality enables simplified application development by enabling the inheritance of the partition maintenance operation from the parent to the child tables. The cascade options are off by default so they do not affect compatibility.

For more information, refer to "About Exchanging a Partition with the Cascade Option" and "Truncating a Partition with the Cascade Option".

This feature provides an ALTER TABLE .. MOVE PARTITION option, where DML operations can continue to run uninterrupted on the partition that is being moved. In addition, global indexes are maintained during the move partition, so a manual index rebuild is no longer required. This feature eliminates the need for any special downtime for the ALTER TABLE MOVE PARTITION ONLINE command.

For more information, refer to "About Moving Partitions and Subpartitions".