1. VLDB and Partitioning Guide
  2. Changes in This Release for Oracle Database VLDB and Partitioning Guide

Changes in This Release for Oracle Database VLDB and Partitioning Guide

This chapter describes changes to Oracle Database VLDB and Partitioning Guide.

See Also:

Changes for VLDB and Partitioning in Oracle Database Release 18c

The following are changes in Very Large Databases and Partitioning for Oracle Database release 18c, version 18.1.

New Features

These are the new features in Oracle Database release 18c, version 18.1, to support very large databases:

  • Enhanced Parallel Partition-wise Operations

    Parallel partition-wise SQL operations can improve query performance significantly, leading to better response time. Parallel partition-wise joins are used commonly for processing large joins efficiently and fast.

    In addition to parallel partition-wise joins, queries using the SELECT DISTINCT clause and SQL window functions can perform parallel partition-wise operations.

    See Also:

  • Modifying the Partitioning Strategy

    You can change the partitioning strategy of a regular (heap-organized) table with the ALTER TABLE MODIFY PARTITION SQL statement. Modifying the partitioning strategy, such as hash partitioning to range partitioning, can be performed offline or online. Indexes are maintained as part of the table modification. When performed in online mode, the conversion has no impact on ongoing DML operations.

    This functionality enables partitioned tables to evolve without manually recreating the tables. Changing an existing partitioning strategy of a table online enables applications to adjust partitioning for new business requirements without application downtime.

    See Also:

  • Online Merging of Partitions and Subpartitions

    You can use the ONLINE keyword with the ALTER TABLE MERGE PARTITION and SUBPARTITION SQL statements to enable online merge operations for regular (heap-organized) tables, providing concurrent data manipulation language (DML) operations with the ongoing partition merge operation.

    Enabling partition maintenance operations online enables you to schedule and execute all of the operations as needed, without the necessity to plan around periods of query-only windows. This capability both increases application availability and simplifies application development.

    See Also:

  • Automatic In-Memory Management With Heat Map Data

    Heat Map data can assist Automatic Data Optimization (ADO) to automatically manage the contents of the In-Memory column store (IM column store). Using Heat Map data, which includes column statistics and other relevant statistics, the IM column store can determine when it is almost full (under memory pressure). If the determination is almost full, then inactive segments can be evicted if there are more frequently accessed segments that would benefit from population in the IM column store.

    See Also:

  • Enhancements to Multitenant Parallel Statement Queuing

    Parallel execution has been enhanced to work more effectively in a multitenant database. With these enhancements, such as updates to the PARALLEL_MAX_SERVERS and PARALLEL_SERVERS_TARGET initialization parameters, parallel statement queuing in a multitenant environment can perform as effectively as in a non-PDB multitenant.

    See Also:

    Oracle Multitenant Administrator's Guide for information about parallel execution (PX) servers and utilization limits for CDBs and PDBs

  • Timeout and Dequeue Actions for Parallel Statement Queuing

    You can specify queue timeout and dequeue actions for parallel statement with enhanced functionality to the PARALLEL_QUEUE_TIMEOUT_ACTION resource manager directive and the DBMS_RESOURCE_MANAGER.DEQUEUE_PARALLEL_STATEMENT PL/SQL procedure.

    See Also:

  • PARALLEL_MIN_DEGREE Initialization Parameter

    See Also:

Changes for VLDB and Partitioning in Oracle Database 12c Release 2 (12.2.0.1)

The following are changes in Very Large Databases and Partitioning for Oracle Database 12c Release 2 (12.2.0.1).

New Features

These are the new features in Oracle Database 12c Release 2 (12.2.0.1) to support very large databases:

  • Automatic List Partitioning

    The automatic list partitioning method enables list partition creation on demand. An auto-list partitioned table is similar to a regular list partitioned table, except that this partitioned table is easier to manage. You can create an auto-list partitioned table using only the partitioning key values that are known. As data is loaded into the table, the database automatically creates a new partition if the loaded partitioning key value does not correspond to any of the existing partitions. Because partitions are automatically created on demand, the auto-list partitioning method is similar to the existing interval partitioning method.

    For information about creating automatic list partitioned tables, refer to Creating an Automatic List-Partitioned Table.

  • Multi-column List Partitioning

    Multi-column list partitioning enables you to partition a table based on list values of multiple columns. Similar to single-column list partitioning, individual partitions can contain sets containing lists of values.

    For information about multi-column list partitioning, refer to Creating a Multi-column List-Partitioned Table.

  • Deferred Segment Creation for Automatic List Partitions and Interval Subpartitions

    Automatic list composite partitioned tables and interval subpartitions only create subpartitions in the presence of data; this action saves space. Deferring subpartition segment creation when creating new partitions on demand ensures that a subpartition segment is only created when the first matching row is inserted.

    For information about creating automatic list partitioned tables, refer to Specifying Partitioning When Creating Tables and Indexes.

  • Read-Only Partitions

    You can set tables, partitions, and subpartitions to read-only status to protect data from unintentional DML operations by any user or trigger.

    For information about creating tables with read-only partitions, refer to Creating a Table with Read-Only Partitions or Subpartitions.

  • Conversion of a Non-Partitioned Table to a Partitioned Table

    A non-partitioned table can be converted to a partitioned table with a MODIFY clause added to the ALTER TABLE SQL statement. In addition, the keyword ONLINE can be specified, enabling concurrent DML operations while the conversion is ongoing.

    For information about online conversion to partitioned tables, refer to Converting a Non-Partitioned Table to a Partitioned Table.

  • Creating a Table for Exchange With a Partitioned Table

    Tables can be created with the FOR EXCHANGE WITH clause to exactly match the shape of a partitioned table and be eligible for a partition exchange command, except that indexes are not created as an operation of this command. Because this feature provides an exact match between a non-partitioned and partitioned table, this is an improvement over the CREATE TABLE AS SELECT statement.

    For information about creating a table for exchange with a partitioned table, refer to Creating a Table for Exchange With a Partitioned Table.

  • Filtered Partition Maintenance Operations

    Partition maintenance operations support the addition of data filtering, enabling the combination of partition and data maintenance. A filtered partition maintenance operation only preserves the data satisfying the data filtering as part of the partition maintenance. The capability of data filtering applies to MOVE PARTITION, MERGE PARTITION, and SPLIT PARTITION .

    For information about filtering maintenance operations, refer to Filtering Maintenance Operations.

  • Online Partition Maintenance with SPLIT Operations

    Partition maintenance with SPLIT operations are supported as online operations with the keyword ONLINE for heap organized tables, enabling concurrent DML operations while a partition maintenance operation is ongoing.

    For information about splitting partitions, refer to About Splitting Partitions and Subpartitions.

  • Creating a Partitioned External Table

    Partitioning of external tables is supported to enable better optimization for queries over partitioned external tables.

    For information about creating a partitioned external table, refer to Creating a Partitioned External Table.

    See Also:

    Oracle Database Administrator’s Guide for information about partitioning external tables

  • Parallel Execution for Real World Performance

    Real world performance provides guidance about when and how to use parallel execution, and how parallel execution distribution methods are related to real world performance.

    For information about parallel execution, refer to Parallel Execution Concepts.

    See Also:

    Oracle Real-World Performance Learning Library for information about techniques for real world performance

  • Multiple Parallelizers

    Some SQL statements can have more than one parallelizer. Multiple parallelizers can be viewed as multiple PX COORDINATOR entries in an explain plan.

    For information about multiple parallelizers, refer to Multiple Parallelizers.

  • Oracle Parallel Query Services on Oracle RAC

    Oracle parallel query services on Oracle RAC read-only nodes represents a scalable parallel data processing architecture. The architecture enables the distribution of a high number of processing engines dedicated to parallel execution of queries.

    For information about parallel execution on Oracle Real Application Clusters (Oracle RAC) nodes, refer to Parallel Execution on Oracle RAC.

    See Also:

    Oracle Real Application Clusters Administration and Deployment Guide for information about Oracle RAC database instances

  • Heat Map and Automatic Data Optimization Enhancements

    Heat map and Automatic Data Optimization (ADO) features, introduced in Oracle Database 12c Release 1 to implement an Information Lifecycle Management (ILM) strategy, are supported in Oracle Database 12c Release 2 multitenant environments.

    For information about using ADO to implement an Information Lifecycle Management (ILM) strategy, refer to Implementing an ILM Strategy With Heat Map and ADO.

  • HCC Compression with ADO Row-Level Policy

    Automatic Data Optimization (ADO) supports Hybrid Columnar Compression (HCC) for row-level policies.

    For information about using row-level compression with ADO, refer to Specifying Row-Level Compression Tiering With ADO.

    See Also:

    Oracle Database Administrator’s Guide for information about table compression

  • Automatic Data Optimization Support for In-Memory Column Store

    Automatic Data Optimization (ADO) supports the In-Memory Column Store.

    For information about using ADO to implement an Information Lifecycle Management (ILM) strategy, refer to Implementing an ILM Strategy With Heat Map and ADO.

    See Also:

    Oracle Database In-Memory Guide for information about In-Memory Column Store and ADO support

  • Oracle Database Vault Support for Information Lifecycle Management (ILM)

    This enhancement enables you to use Database Vault realms and command rules with the Automatic Data Optimization (ADO), including granting the authorization that enables an ADO administrative user to perform ILM operations on Database Vault-protected objects.

    For information about using ADO to implement an Information Lifecycle Management (ILM) strategy, refer to Implementing an ILM Strategy With Heat Map and ADO.

    See Also:

    Oracle Database Vault Administrator’s Guide for information about using Information Lifecycle Management (ILM) with Oracle Database Vault.

  • Advanced Index Compression (HIGH)

    For information about advanced index compression with partitioned indexes, refer to Advanced Index Compression With Partitioned Indexes.

    See Also:

    Oracle Database Administrator’s Guide for information about advanced index compression

  • Support for Partitioning with XMLType Columns, XMLType Table, and XMLIndex

    Interval and reference partitioning are now supported for XMLType columns, XMLType table, and XMLIndex.

    For information about partitioning an XMLIndex for binary XML tables, refer to Partitioning of XMLIndex for Binary XML Tables.

    See Also:

    Oracle XML DB Developer’s Guide for information about partitioning XMLType tables and columns.

Deprecated Features

The following are the Oracle Database 12c Release 2 (12.2.0.1) deprecated features.

  • Deprecation of PARALLEL_ADAPTIVE_MULTI_USER Initialization Parameter

    The initialization parameter PARALLEL_ADAPTIVE_MULTI_USER is deprecated in Oracle Database 12c Release 2 (12.2.0.1) and may be desupported in a future release. Also, the default value of the parameter has been changed from TRUE to FALSE in 12.2. Oracle recommends using parallel statement queuing instead.

    For information about adaptive parallelism, refer to Adaptive Parallelism.

Changes for VLDB and Partitioning in Oracle Database 12c Release 1 (12.1.0.2)

The following are changes in Very Large Databases and Partitioning for Oracle Database 12c Release 1 (12.1.0.2).

New Features

These are the new features in Oracle Database 12c Release 1 (12.1.0.2) to support very large databases.

  • Automatic Big Table Caching

    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.

    See Also:

  • In-Memory Column Store

    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.

    See Also:

  • Force Full Database Caching Mode

    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.

    See Also:

    Oracle Database Performance Tuning Guide for information about full database caching mode

  • Attribute Clustering

    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.

    See Also:

    Oracle Database Data Warehousing Guide for information about attribute clustering within a table

  • Zone Maps

    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.

    See Also:

    Oracle Database Data Warehousing Guide for information about zone maps and attribute clustering

  • Advanced Index Compression

    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.

    See Also:

    Oracle Database Administrator's Guide for information about advanced index compression

  • Oracle XML DB and Domain Index Support of Hash Partitioned Tables

    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.

    See Also:

  • Range Partitioning for Hash Clusters

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

    See Also:

    Oracle Database SQL Language Reference for information about SQL syntax related to partitioned hash clusters.

Changes for VLDB and Partitioning in Oracle Database 12c Release 1 (12.1.0.1)

The following are changes in Very Large Databases and Partitioning for Oracle Database 12c Release 1 (12.1.0.1).

New Features

These are the new features in Oracle Database 12c Release 1 (12.1.0.1) to support very large databases.

  • Partition Maintenance Operations on Multiple Partitions

    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 Supported on Partitions

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

  • Heat Map

    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.

    See Also:

  • Automatic Data Optimization

    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.

    See Also:

  • In-Database Archiving and Temporal Validity

    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.

    See Also:

  • Concurrent Execution of Union All

    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.

  • Enhancements to Incremental Statistics

    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.

  • Parallel Statement Queuing Enhancements

    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:

    • 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:

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

  • Partial Indexes for Partitioned Tables

    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.

  • Asynchronous Global Index Maintenance for DROP and TRUNCATE PARTITION

    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.

  • Interval-Reference Partitioning

    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.

  • Cascade Functionality for TRUNCATE PARTITION and EXCHANGE PARTITION

    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.

  • Online Move Partition

    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.

    See Also:

    Oracle Database SQL Language Reference for information about online move partition, including any limitations of this feature