Table of Contents

• List of Examples
• List of Figures
• List of Tables
• Title and Copyright Information
• Preface
  • Audience
  • Documentation Accessibility
  • Related Documents
  • Conventions
• Changes in This Release for Oracle Database VLDB and Partitioning Guide
  • Changes for VLDB and Partitioning in Oracle Database 12c Release 2 (12.2.0.1)
  • Changes for VLDB and Partitioning in Oracle Database 12c Release 1 (12.1.0.2)
  • Changes for VLDB and Partitioning in Oracle Database 12c Release 1 (12.1.0.1)
• 1 Introduction to Very Large Databases
  • 1.1 Introduction to Partitioning
  • 1.2 VLDB and Partitioning
  • 1.3 Partitioning As the Foundation for Information Lifecycle Management
  • 1.4 Partitioning for All Databases
• 2 Partitioning Concepts
  • 2.1 Partitioning Overview
    • 2.1.1 Basics of Partitioning
    • 2.1.2 Partitioning Key
    • 2.1.3 Partitioned Tables
      • 2.1.3.1 When to Partition a Table
      • 2.1.3.2 When to Partition an Index
    • 2.1.4 Partitioned Index-Organized Tables
    • 2.1.5 System Partitioning
    • 2.1.6 Partitioning for Information Lifecycle Management
    • 2.1.7 Range Partitioning for Hash Clusters
    • 2.1.8 Partitioning and LOB Data
    • 2.1.9 Partitioning on External Tables
    • 2.1.10 Collections in XMLType and Object Data
  • 2.2 Benefits of Partitioning
    • 2.2.1 Partitioning for Performance
      • 2.2.1.1 Partition Pruning
      • 2.2.1.2 Partition-Wise Joins
    • 2.2.2 Partitioning for Manageability
    • 2.2.3 Partitioning for Availability
  • 2.3 Partitioning Strategies
    • 2.3.1 Single-Level Partitioning
      • 2.3.1.1 Range Partitioning
      • 2.3.1.2 Hash Partitioning
      • 2.3.1.3 List Partitioning
    • 2.3.2 Composite Partitioning
      • 2.3.2.1 Composite Range-Range Partitioning
      • 2.3.2.2 Composite Range-Hash Partitioning
      • 2.3.2.3 Composite Range-List Partitioning
      • 2.3.2.4 Composite List-Range Partitioning
      • 2.3.2.5 Composite List-Hash Partitioning
      • 2.3.2.6 Composite List-List Partitioning
      • 2.3.2.7 Composite Hash-Hash Partitioning
      • 2.3.2.8 Composite Hash-List Partitioning
      • 2.3.2.9 Composite Hash-Range Partitioning
  • 2.4 Partitioning Extensions
    • 2.4.1 Manageability Extensions
      • 2.4.1.1 Interval Partitioning
      • 2.4.1.2 Partition Advisor
    • 2.4.2 Partitioning Key Extensions
      • 2.4.2.1 Reference Partitioning
      • 2.4.2.2 Virtual Column-Based Partitioning
  • 2.5 Indexing on Partitioned Tables
    • 2.5.1 Deciding on the Type of Partitioned Index to Use
    • 2.5.2 Local Partitioned Indexes
    • 2.5.3 Global Partitioned Indexes
      • 2.5.3.1 Global Range Partitioned Indexes
      • 2.5.3.2 Global Hash Partitioned Indexes
      • 2.5.3.3 Maintenance of Global Partitioned Indexes
    • 2.5.4 Global Nonpartitioned Indexes
    • 2.5.5 Miscellaneous Information about Creating Indexes on Partitioned Tables
    • 2.5.6 Partial Indexes for Partitioned Tables
    • 2.5.7 Partitioned Indexes on Composite Partitions
• 3 Partitioning for Availability, Manageability, and Performance
  • 3.1 Partition Pruning
    • 3.1.1 Benefits of Partition Pruning
    • 3.1.2 Information That Can Be Used for Partition Pruning
    • 3.1.3 How to Identify Whether Partition Pruning Has Been Used
    • 3.1.4 Static Partition Pruning
    • 3.1.5 Dynamic Partition Pruning
      • 3.1.5.1 Dynamic Pruning with Bind Variables
      • 3.1.5.2 Dynamic Pruning with Subqueries
      • 3.1.5.3 Dynamic Pruning with Star Transformation
      • 3.1.5.4 Dynamic Pruning with Nested Loop Joins
    • 3.1.6 Partition Pruning with Zone Maps
    • 3.1.7 Partition Pruning Tips
      • 3.1.7.1 Data Type Conversions
      • 3.1.7.2 Function Calls
      • 3.1.7.3 Collection Tables
  • 3.2 Partition-Wise Joins
    • 3.2.1 Full Partition-Wise Joins
      • 3.2.1.1 Querying a Full Partition-Wise Join
      • 3.2.1.2 Full Partition-Wise Joins: Single-Level - Single-Level
      • 3.2.1.3 Full Partition-Wise Joins: Composite - Single-Level
      • 3.2.1.4 Full Partition-Wise Joins: Composite - Composite
    • 3.2.2 Partial Partition-Wise Joins
      • 3.2.2.1 Partial Partition-Wise Joins: Single-Level Partitioning
      • 3.2.2.2 Partial Partition-Wise Joins: Composite
  • 3.3 Index Partitioning
    • 3.3.1 Local Partitioned Indexes
      • 3.3.1.1 Local Prefixed Indexes
      • 3.3.1.2 Local Nonprefixed Indexes
    • 3.3.2 Global Partitioned Indexes
      • 3.3.2.1 Prefixed and Nonprefixed Global Partitioned Indexes
      • 3.3.2.2 Management of Global Partitioned Indexes
    • 3.3.3 Summary of Partitioned Index Types
    • 3.3.4 The Importance of Nonprefixed Indexes
    • 3.3.5 Performance Implications of Prefixed and Nonprefixed Indexes
    • 3.3.6 Advanced Index Compression With Partitioned Indexes
    • 3.3.7 Guidelines for Partitioning Indexes
    • 3.3.8 Physical Attributes of Index Partitions
  • 3.4 Partitioning and Table Compression
    • 3.4.1 Table Compression and Bitmap Indexes
    • 3.4.2 Example of Table Compression and Partitioning
  • 3.5 Recommendations for Choosing a Partitioning Strategy
    • 3.5.1 When to Use Range or Interval Partitioning
    • 3.5.2 When to Use Hash Partitioning
    • 3.5.3 When to Use List Partitioning
    • 3.5.4 When to Use Composite Partitioning
      • 3.5.4.1 When to Use Composite Range-Hash Partitioning
      • 3.5.4.2 When to Use Composite Range-List Partitioning
      • 3.5.4.3 When to Use Composite Range-Range Partitioning
      • 3.5.4.4 When to Use Composite List-Hash Partitioning
      • 3.5.4.5 When to Use Composite List-List Partitioning
      • 3.5.4.6 When to Use Composite List-Range Partitioning
    • 3.5.5 When to Use Interval Partitioning
    • 3.5.6 When to Use Reference Partitioning
    • 3.5.7 When to Partition on Virtual Columns
    • 3.5.8 Considerations When Using Read-Only Tablespaces
• 4 Partition Administration
  • 4.1 Specifying Partitioning When Creating Tables and Indexes
    • 4.1.1 About Creating Range-Partitioned Tables and Global Indexes
      • 4.1.1.1 Creating a Range-Partitioned Table
      • 4.1.1.2 Creating a Range-Partitioned Table With More Complexity
      • 4.1.1.3 Creating a Range-Partitioned Global Index
    • 4.1.2 Creating Range-Interval-Partitioned Tables
    • 4.1.3 About Creating Hash Partitioned Tables and Global Indexes
      • 4.1.3.1 Creating a Hash Partitioned Table
      • 4.1.3.2 Creating a Hash Partitioned Global Index
    • 4.1.4 About Creating List-Partitioned Tables
      • 4.1.4.1 Creating a List-Partitioned Table
      • 4.1.4.2 Creating a List-Partitioned Table With a Default Partition
      • 4.1.4.3 Creating an Automatic List-Partitioned Table
      • 4.1.4.4 Creating a Multi-column List-Partitioned Table
    • 4.1.5 Creating Reference-Partitioned Tables
    • 4.1.6 Creating Interval-Reference Partitioned Tables
    • 4.1.7 Creating a Table Using In-Memory Column Store With Partitioning
    • 4.1.8 Creating a Table with Read-Only Partitions or Subpartitions
    • 4.1.9 Creating a Partitioned External Table
    • 4.1.10 Specifying Partitioning on Key Columns
      • 4.1.10.1 Creating a Multicolumn Range-Partitioned Table By Date
      • 4.1.10.2 Creating a Multicolumn Range-Partitioned Table to Enforce Equal-Sized Partitions
    • 4.1.11 Using Virtual Column-Based Partitioning
    • 4.1.12 Using Table Compression with Partitioned Tables
    • 4.1.13 Using Key Compression with Partitioned Indexes
    • 4.1.14 Specifying Partitioning with Segments
      • 4.1.14.1 Deferred Segment Creation for Partitioning
      • 4.1.14.2 Truncating Segments That Are Empty
      • 4.1.14.3 Maintenance Procedures for Segment Creation on Demand
    • 4.1.15 Specifying Partitioning When Creating Index-Organized Tables
      • 4.1.15.1 Creating Range-Partitioned Index-Organized Tables
      • 4.1.15.2 Creating Hash Partitioned Index-Organized Tables
      • 4.1.15.3 Creating List-Partitioned Index-Organized Tables
    • 4.1.16 Partitioning Restrictions for Multiple Block Sizes
    • 4.1.17 Partitioning of Collections in XMLType and Objects
      • 4.1.17.1 Performing PMOs on Partitions that Contain Collection Tables
      • 4.1.17.2 Partitioning of XMLIndex for Binary XML Tables
  • 4.2 Specifying Composite Partitioning When Creating Tables
    • 4.2.1 Creating Composite Hash-* Partitioned Tables
    • 4.2.2 Creating Composite Interval-* Partitioned Tables
      • 4.2.2.1 Creating Composite Interval-Hash Partitioned Tables
      • 4.2.2.2 Creating Composite Interval-List Partitioned Tables
      • 4.2.2.3 Creating Composite Interval-Range Partitioned Tables
    • 4.2.3 Creating Composite List-* Partitioned Tables
      • 4.2.3.1 Creating Composite List-Hash Partitioned Tables
      • 4.2.3.2 Creating Composite List-List Partitioned Tables
      • 4.2.3.3 Creating Composite List-Range Partitioned Tables
    • 4.2.4 Creating Composite Range-* Partitioned Tables
      • 4.2.4.1 About Creating Composite Range-Hash Partitioned Tables
        • 4.2.4.1.1 Creating a Composite Range-Hash Partitioned Table With the Same Tablespaces
        • 4.2.4.1.2 Creating a Composite Range-Hash Partitioned Table With Varying Tablespaces
        • 4.2.4.1.3 Creating a Local Index Across Multiple Tablespaces
      • 4.2.4.2 About Creating Composite Range-List Partitioned Tables
        • 4.2.4.2.1 Creating a Composite Range-List Partitioned Table
        • 4.2.4.2.2 Creating a Composite Range-List Partitioned Table Specifying Tablespaces
      • 4.2.4.3 Creating Composite Range-Range Partitioned Tables
    • 4.2.5 Specifying Subpartition Templates to Describe Composite Partitioned Tables
      • 4.2.5.1 Specifying a Subpartition Template for a *-Hash Partitioned Table
      • 4.2.5.2 Specifying a Subpartition Template for a *-List Partitioned Table
  • 4.3 Maintenance Operations Supported on Partitions
    • 4.3.1 Updating Indexes Automatically
    • 4.3.2 Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
    • 4.3.3 Modifying a Subpartition Template
    • 4.3.4 Filtering Maintenance Operations
  • 4.4 Maintenance Operations for Partitioned Tables and Indexes
    • 4.4.1 About Adding Partitions and Subpartitions
      • 4.4.1.1 Adding a Partition to a Range-Partitioned Table
      • 4.4.1.2 Adding a Partition to a Hash-Partitioned Table
      • 4.4.1.3 Adding a Partition to a List-Partitioned Table
      • 4.4.1.4 Adding a Partition to an Interval-Partitioned Table
      • 4.4.1.5 About Adding Partitions to a Composite *-Hash Partitioned Table
        • 4.4.1.5.1 Adding a Partition to a *-Hash Partitioned Table
        • 4.4.1.5.2 Adding a Subpartition to a *-Hash Partitioned Table
      • 4.4.1.6 About Adding Partitions to a Composite *-List Partitioned Table
        • 4.4.1.6.1 Adding a Partition to a *-List Partitioned Table
        • 4.4.1.6.2 Adding a Subpartition to a *-List Partitioned Table
      • 4.4.1.7 About Adding Partitions to a Composite *-Range Partitioned Table
        • 4.4.1.7.1 Adding a Partition to a *-Range Partitioned Table
        • 4.4.1.7.2 Adding a Subpartition to a *-Range Partitioned Table
      • 4.4.1.8 About Adding a Partition or Subpartition to a Reference-Partitioned Table
      • 4.4.1.9 Adding Index Partitions
      • 4.4.1.10 Adding Multiple Partitions
    • 4.4.2 About Coalescing Partitions and Subpartitions
      • 4.4.2.1 Coalescing a Partition in a Hash Partitioned Table
      • 4.4.2.2 Coalescing a Subpartition in a *-Hash Partitioned Table
      • 4.4.2.3 Coalescing Hash Partitioned Global Indexes
    • 4.4.3 About Dropping Partitions and Subpartitions
      • 4.4.3.1 Dropping Table Partitions
        • 4.4.3.1.1 Dropping a Partition from a Table that Contains Data and Global Indexes
        • 4.4.3.1.2 Dropping a Partition Containing Data and Referential Integrity Constraints
      • 4.4.3.2 Dropping Interval Partitions
      • 4.4.3.3 Dropping Index Partitions
      • 4.4.3.4 Dropping Multiple Partitions
    • 4.4.4 About Exchanging Partitions and Subpartitions
      • 4.4.4.1 Creating a Table for Exchange With a Partitioned Table
      • 4.4.4.2 Exchanging a Range, Hash, or List Partition
      • 4.4.4.3 Exchanging a Partition of an Interval Partitioned Table
      • 4.4.4.4 Exchanging a Partition of a Reference-Partitioned Table
      • 4.4.4.5 About Exchanging a Partition of a Table with Virtual Columns
      • 4.4.4.6 Exchanging a Hash Partitioned Table with a *-Hash Partition
      • 4.4.4.7 Exchanging a Subpartition of a *-Hash Partitioned Table
      • 4.4.4.8 Exchanging a List-Partitioned Table with a *-List Partition
      • 4.4.4.9 About Exchanging a Subpartition of a *-List Partitioned Table
      • 4.4.4.10 Exchanging a Range-Partitioned Table with a *-Range Partition
      • 4.4.4.11 About Exchanging a Subpartition of a *-Range Partitioned Table
      • 4.4.4.12 About Exchanging a Partition with the Cascade Option
    • 4.4.5 About Merging Partitions and Subpartitions
      • 4.4.5.1 Merging Range Partitions
      • 4.4.5.2 Merging Interval Partitions
      • 4.4.5.3 Merging List Partitions
      • 4.4.5.4 Merging *-Hash Partitions
      • 4.4.5.5 About Merging *-List Partitions
        • 4.4.5.5.1 Merging Partitions in a *-List Partitioned Table
        • 4.4.5.5.2 Merging Subpartitions in a *-List Partitioned Table
      • 4.4.5.6 About Merging *-Range Partitions
        • 4.4.5.6.1 Merging Partitions in a *-Range Partitioned Table
      • 4.4.5.7 Merging Multiple Partitions
    • 4.4.6 About Modifying Attributes of Tables, Partitions, and Subpartitions
      • 4.4.6.1 About Modifying Default Attributes
        • 4.4.6.1.1 Modifying Default Attributes of a Table
        • 4.4.6.1.2 Modifying Default Attributes of a Partition
        • 4.4.6.1.3 Modifying Default Attributes of Index Partitions
      • 4.4.6.2 About Modifying Real Attributes of Partitions
        • 4.4.6.2.1 Modifying Real Attributes for a Range or List Partition
        • 4.4.6.2.2 Modifying Real Attributes for a Hash Partition
        • 4.4.6.2.3 Modifying Real Attributes of a Subpartition
        • 4.4.6.2.4 Modifying Real Attributes of Index Partitions
    • 4.4.7 About Modifying List Partitions
      • 4.4.7.1 About Modifying List Partitions: Adding Values
        • 4.4.7.1.1 Adding Values for a List Partition
        • 4.4.7.1.2 Adding Values for a List Subpartition
      • 4.4.7.2 About Modifying List Partitions: Dropping Values
        • 4.4.7.2.1 Dropping Values from a List Partition
        • 4.4.7.2.2 Dropping Values from a List Subpartition
    • 4.4.8 About Moving Partitions and Subpartitions
      • 4.4.8.1 Moving Table Partitions
      • 4.4.8.2 Moving Subpartitions
      • 4.4.8.3 Moving Index Partitions
    • 4.4.9 About Rebuilding Index Partitions
      • 4.4.9.1 About Rebuilding Global Index Partitions
      • 4.4.9.2 About Rebuilding Local Index Partitions
        • 4.4.9.2.1 Using ALTER INDEX to Rebuild a Partition
        • 4.4.9.2.2 Using ALTER TABLE to Rebuild an Index Partition
    • 4.4.10 About Renaming Partitions and Subpartitions
      • 4.4.10.1 Renaming a Table Partition
      • 4.4.10.2 Renaming a Table Subpartition
      • 4.4.10.3 About Renaming Index Partitions
        • 4.4.10.3.1 Renaming an Index Partition
        • 4.4.10.3.2 Renaming an Index Subpartition
    • 4.4.11 About Splitting Partitions and Subpartitions
      • 4.4.11.1 Splitting a Partition of a Range-Partitioned Table
      • 4.4.11.2 Splitting a Partition of a List-Partitioned Table
      • 4.4.11.3 Splitting a Partition of an Interval-Partitioned Table
      • 4.4.11.4 Splitting a *-Hash Partition
      • 4.4.11.5 Splitting Partitions in a *-List Partitioned Table
        • 4.4.11.5.1 Splitting a *-List Partition
        • 4.4.11.5.2 Splitting a *-List Subpartition
      • 4.4.11.6 Splitting a *-Range Partition
        • 4.4.11.6.1 Splitting a *-Range Subpartition
      • 4.4.11.7 Splitting Index Partitions
      • 4.4.11.8 Splitting into Multiple Partitions
      • 4.4.11.9 Fast SPLIT PARTITION and SPLIT SUBPARTITION Operations
    • 4.4.12 About Truncating Partitions and Subpartitions
      • 4.4.12.1 About Truncating a Table Partition
        • 4.4.12.1.1 Truncating Table Partitions Containing Data and Global Indexes
        • 4.4.12.1.2 Truncating a Partition Containing Data and Referential Integrity Constraints
      • 4.4.12.2 Truncating a Subpartition
      • 4.4.12.3 Truncating a Partition with the Cascade Option
      • 4.4.12.4 Truncating Multiple Partitions
  • 4.5 About Dropping Partitioned Tables
  • 4.6 Changing a Nonpartitioned Table into a Partitioned Table
    • 4.6.1 Using Online Redefinition to Partition Collection Tables
    • 4.6.2 Converting a Non-Partitioned Table to a Partitioned Table
  • 4.7 Viewing Information About Partitioned Tables and Indexes
• 5 Managing and Maintaining Time-Based Information
  • 5.1 Managing Data in Oracle Database With ILM
    • 5.1.1 About Oracle Database for ILM
      • 5.1.1.1 Oracle Database Manages All Types of Data
      • 5.1.1.2 Regulatory Requirements
      • 5.1.1.3 The Benefits of an Online Archive
    • 5.1.2 Implementing ILM Using Oracle Database
      • 5.1.2.1 Step 1: Define the Data Classes
        • 5.1.2.1.1 Partitioning for ILM
        • 5.1.2.1.2 The Lifecycle of Data
      • 5.1.2.2 Step 2: Create Storage Tiers for the Data Classes
        • 5.1.2.2.1 Assigning Classes to Storage Tiers
        • 5.1.2.2.2 The Costs Savings of Using Tiered Storage
      • 5.1.2.3 Step 3: Create Data Access and Migration Policies
        • 5.1.2.3.1 Controlling Access to Data
        • 5.1.2.3.2 Moving Data using Partitioning
      • 5.1.2.4 Step 4: Define and Enforce Compliance Policies
        • 5.1.2.4.1 Data Retention
        • 5.1.2.4.2 Immutability
        • 5.1.2.4.3 Privacy
        • 5.1.2.4.4 Auditing
        • 5.1.2.4.5 Expiration
  • 5.2 Implementing an ILM Strategy With Heat Map and ADO
    • 5.2.1 Using Heat Map
      • 5.2.1.1 Enabling and Disabling Heat Map
      • 5.2.1.2 Displaying Heat Map Tracking Data With Views
      • 5.2.1.3 Managing Heat Map Data With DBMS_HEAT_MAP Subprograms
    • 5.2.2 Using Automatic Data Optimization
      • 5.2.2.1 Managing Policies for Automatic Data Optimization
      • 5.2.2.2 Creating a Table With an ILM ADO Policy
      • 5.2.2.3 Adding ILM ADO Policies
      • 5.2.2.4 Disabling and Deleting ILM ADO Policies
      • 5.2.2.5 Specifying Segment-Level Compression and Storage Tiering With ADO
      • 5.2.2.6 Specifying Row-Level Compression Tiering With ADO
      • 5.2.2.7 Managing ILM ADO Parameters
      • 5.2.2.8 Using PL/SQL Functions for Policy Management
      • 5.2.2.9 Using Views to Monitor Policies for ADO
    • 5.2.3 Limitations and Restrictions With ADO and Heat Map
  • 5.3 Controlling the Validity and Visibility of Data in Oracle Database
    • 5.3.1 Using In-Database Archiving
    • 5.3.2 Using Temporal Validity
    • 5.3.3 Creating a Table With Temporal Validity
    • 5.3.4 Limitations and Restrictions With In-Database Archiving and Temporal Validity
  • 5.4 Implementing an ILM System Manually Using Partitioning
  • 5.5 Managing ILM Heat Map and ADO with Oracle Enterprise Manager
    • 5.5.1 Accessing the Database Administration Menu
    • 5.5.2 Viewing Automatic Data Optimization Activity at the Tablespace Level
    • 5.5.3 Viewing the Segment Activity Details of Any Tablespace
    • 5.5.4 Viewing the Segment Activity Details of Any Object
    • 5.5.5 Viewing the Segment Activity History of Any Object
    • 5.5.6 Searching Segment Activity in Automatic Data Optimization
    • 5.5.7 Viewing Policies for a Segment
    • 5.5.8 Disabling Background Activity
    • 5.5.9 Changing Execution Frequency of Background Automatic Data Optimization
    • 5.5.10 Viewing Policy Executions In the Last 24 Hours
    • 5.5.11 Viewing Objects Moved In Last 24 Hours
    • 5.5.12 Viewing Policy Details
    • 5.5.13 Viewing Objects Associated With a Policy
    • 5.5.14 Evaluating a Policy Before Execution
    • 5.5.15 Executing a Single Policy
    • 5.5.16 Stopping a Policy Execution
    • 5.5.17 Viewing Policy Execution History
• 6 Using Partitioning in a Data Warehouse Environment
  • 6.1 What Is a Data Warehouse?
  • 6.2 Scalability
    • 6.2.1 Bigger Databases
    • 6.2.2 Bigger Individual Tables: More Rows in Tables
    • 6.2.3 More Users Querying the System
    • 6.2.4 More Complex Queries
  • 6.3 Performance
    • 6.3.1 Partition Pruning
      • 6.3.1.1 Basic Partition Pruning Techniques
      • 6.3.1.2 Advanced Partition Pruning Techniques
    • 6.3.2 Partition-Wise Joins
      • 6.3.2.1 Full Partition-Wise Joins
      • 6.3.2.2 Partial Partition-Wise Joins
      • 6.3.2.3 Benefits of Partition-Wise Joins
        • 6.3.2.3.1 Reduction of Communications Overhead
        • 6.3.2.3.2 Reduction of Memory Requirements
      • 6.3.2.4 Performance Considerations for Parallel Partition-Wise Joins
    • 6.3.3 Indexes and Partitioned Indexes
      • 6.3.3.1 Local Partitioned Indexes
      • 6.3.3.2 Nonpartitioned Indexes
      • 6.3.3.3 Global Partitioned Indexes
    • 6.3.4 Materialized Views and Partitioning
      • 6.3.4.1 Partitioned Materialized Views
  • 6.4 Manageability
    • 6.4.1 Partition Exchange Load
    • 6.4.2 Partitioning and Indexes
    • 6.4.3 Removing Data from Tables
    • 6.4.4 Partitioning and Data Compression
• 7 Using Partitioning in an Online Transaction Processing Environment
  • 7.1 What Is an OLTP System?
  • 7.2 Performance
    • 7.2.1 Deciding Whether to Partition Indexes
    • 7.2.2 How to Use Partitioning on Index-Organized Tables
  • 7.3 Manageability
    • 7.3.1 Impact of a Partition Maintenance Operation on a Partitioned Table with Local Indexes
    • 7.3.2 Impact of a Partition Maintenance Operation on Global Indexes
    • 7.3.3 Common Partition Maintenance Operations in OLTP Environments
      • 7.3.3.1 Removing (Purging) Old Data
      • 7.3.3.2 Moving or Merging Older Partitions to a Low-Cost Storage Tier Device
• 8 Using Parallel Execution
  • 8.1 Parallel Execution Concepts
    • 8.1.1 When to Implement Parallel Execution
    • 8.1.2 When Not to Implement Parallel Execution
    • 8.1.3 Fundamental Hardware Requirements
    • 8.1.4 How Parallel Execution Works
      • 8.1.4.1 Parallel Execution of SQL Statements
      • 8.1.4.2 Producer/Consumer Model
      • 8.1.4.3 Granules of Parallelism
        • 8.1.4.3.1 Block Range Granules
        • 8.1.4.3.2 Partition Granules
      • 8.1.4.4 Distribution Methods Between Producers and Consumers
      • 8.1.4.5 How Parallel Execution Servers Communicate
    • 8.1.5 Parallel Execution Server Pool
      • 8.1.5.1 Processing without Enough Parallel Execution Servers
    • 8.1.6 Balancing the Workload to Optimize Performance
    • 8.1.7 Multiple Parallelizers
    • 8.1.8 Parallel Execution on Oracle RAC
  • 8.2 Setting the Degree of Parallelism
    • 8.2.1 Manually Specifying the Degree of Parallelism
    • 8.2.2 Default Degree of Parallelism
    • 8.2.3 Automatic Degree of Parallelism
    • 8.2.4 Determining Degree of Parallelism in Auto DOP
    • 8.2.5 Controlling Automatic Degree of Parallelism
    • 8.2.6 Adaptive Parallelism
  • 8.3 In-Memory Parallel Execution
    • 8.3.1 Buffer Cache Usage in Parallel Execution
    • 8.3.2 Automatic Big Table Caching
  • 8.4 Parallel Statement Queuing
    • 8.4.1 About Managing Parallel Statement Queuing with Oracle Database Resource Manager
      • 8.4.1.1 About Managing the Order of the Parallel Statement Queue
      • 8.4.1.2 About Limiting the Parallel Server Resources for a Consumer Group
      • 8.4.1.3 Specifying a Parallel Statement Queue Timeout for Each Consumer Group
      • 8.4.1.4 Specifying a Degree of Parallelism Limit for Consumer Groups
      • 8.4.1.5 Critical Parallel Statement Prioritization
      • 8.4.1.6 About Managing Parallel Statement Queuing for CDBs and PDBs
      • 8.4.1.7 A Sample Scenario for Managing Statements in the Parallel Queue
    • 8.4.2 Grouping Parallel Statements with BEGIN_SQL_BLOCK END_SQL_BLOCK
    • 8.4.3 About Managing Parallel Statement Queuing with Hints
  • 8.5 Types of Parallelism
    • 8.5.1 About Parallel Queries
      • 8.5.1.1 Parallel Queries on Index-Organized Tables
      • 8.5.1.2 Nonpartitioned Index-Organized Tables
      • 8.5.1.3 Partitioned Index-Organized Tables
      • 8.5.1.4 Parallel Queries on Object Types
      • 8.5.1.5 Rules for Parallelizing Queries
    • 8.5.2 About Parallel DDL Statements
      • 8.5.2.1 DDL Statements That Can Be Parallelized
      • 8.5.2.2 About Using CREATE TABLE AS SELECT in Parallel
      • 8.5.2.3 Recoverability and Parallel DDL
      • 8.5.2.4 Space Management for Parallel DDL
      • 8.5.2.5 Storage Space When Using Dictionary-Managed Tablespaces
      • 8.5.2.6 Free Space and Parallel DDL
      • 8.5.2.7 Rules for DDL Statements
      • 8.5.2.8 Rules for CREATE TABLE AS SELECT
    • 8.5.3 About Parallel DML Operations
      • 8.5.3.1 When to Use Parallel DML
        • 8.5.3.1.1 Refreshing Tables in a Data Warehouse System
        • 8.5.3.1.2 Creating Intermediate Summary Tables
        • 8.5.3.1.3 Using Scoring Tables
        • 8.5.3.1.4 Updating Historical Tables
        • 8.5.3.1.5 Running Batch Jobs
      • 8.5.3.2 Enable Parallel DML Mode
      • 8.5.3.3 Rules for UPDATE, MERGE, and DELETE
      • 8.5.3.4 Rules for INSERT SELECT
      • 8.5.3.5 Transaction Restrictions for Parallel DML
      • 8.5.3.6 Rollback Segments
      • 8.5.3.7 Recovery for Parallel DML
        • 8.5.3.7.1 Transaction Recovery for User-Issued Rollback
        • 8.5.3.7.2 Process Recovery
        • 8.5.3.7.3 System Recovery
      • 8.5.3.8 Space Considerations for Parallel DML
      • 8.5.3.9 Restrictions on Parallel DML
        • 8.5.3.9.1 Partitioning Key Restriction
        • 8.5.3.9.2 Function Restrictions
      • 8.5.3.10 Data Integrity Restrictions
        • 8.5.3.10.1 NOT NULL and CHECK
        • 8.5.3.10.2 UNIQUE and PRIMARY KEY
        • 8.5.3.10.3 FOREIGN KEY (Referential Integrity)
        • 8.5.3.10.4 Delete Cascade
        • 8.5.3.10.5 Self-Referential Integrity
        • 8.5.3.10.6 Deferrable Integrity Constraints
      • 8.5.3.11 Trigger Restrictions
      • 8.5.3.12 Distributed Transaction Restrictions
      • 8.5.3.13 Examples of Distributed Transaction Parallelization
      • 8.5.3.14 Concurrent Execution of Union All
    • 8.5.4 About Parallel Execution of Functions
      • 8.5.4.1 Functions in Parallel Queries
      • 8.5.4.2 Functions in Parallel DML and DDL Statements
    • 8.5.5 About Other Types of Parallelism
    • 8.5.6 Degree of Parallelism Rules for SQL Statements
  • 8.6 About Initializing and Tuning Parameters for Parallel Execution
    • 8.6.1 Default Parameter Settings
    • 8.6.2 Forcing Parallel Execution for a Session
    • 8.6.3 Tuning General Parameters for Parallel Execution
      • 8.6.3.1 Parameters Establishing Resource Limits for Parallel Operations
        • 8.6.3.1.1 PARALLEL_FORCE_LOCAL
        • 8.6.3.1.2 PARALLEL_MAX_SERVERS
          • 8.6.3.1.2.1 When Users Have Too Many Processes
          • 8.6.3.1.2.2 When to Limit the Number of Resources for a User using a Consumer Group
        • 8.6.3.1.3 PARALLEL_MIN_PERCENT
        • 8.6.3.1.4 PARALLEL_MIN_SERVERS
        • 8.6.3.1.5 PARALLEL_MIN_TIME_THRESHOLD
        • 8.6.3.1.6 PARALLEL_SERVERS_TARGET
        • 8.6.3.1.7 SHARED_POOL_SIZE
        • 8.6.3.1.8 Additional Memory Requirements for Message Buffers
          • 8.6.3.1.8.1 Required Memory for Message Buffers
          • 8.6.3.1.8.2 Additional Memory for Cursors
        • 8.6.3.1.9 Monitor Memory Usage After Processing Begins
      • 8.6.3.2 Parameters Affecting Resource Consumption
        • 8.6.3.2.1 PGA_AGGREGATE_TARGET
          • 8.6.3.2.1.1 HASH_AREA_SIZE
          • 8.6.3.2.1.2 SORT_AREA_SIZE
        • 8.6.3.2.2 PARALLEL_EXECUTION_MESSAGE_SIZE
        • 8.6.3.2.3 Parameters Affecting Resource Consumption for Parallel DML and Parallel DDL
          • 8.6.3.2.3.1 TRANSACTIONS
          • 8.6.3.2.3.2 FAST_START_PARALLEL_ROLLBACK
          • 8.6.3.2.3.3 DML_LOCKS
      • 8.6.3.3 Parameters Related to I/O
        • 8.6.3.3.1 DB_CACHE_SIZE
        • 8.6.3.3.2 DB_BLOCK_SIZE
        • 8.6.3.3.3 DB_FILE_MULTIBLOCK_READ_COUNT
        • 8.6.3.3.4 DISK_ASYNCH_IO and TAPE_ASYNCH_IO
  • 8.7 Monitoring Parallel Execution Performance
    • 8.7.1 Monitoring Parallel Execution Performance with Dynamic Performance Views
      • 8.7.1.1 V$PX_BUFFER_ADVICE
      • 8.7.1.2 V$PX_SESSION
      • 8.7.1.3 V$PX_SESSTAT
      • 8.7.1.4 V$PX_PROCESS
      • 8.7.1.5 V$PX_PROCESS_SYSSTAT
      • 8.7.1.6 V$PQ_SESSTAT
      • 8.7.1.7 V$PQ_TQSTAT
      • 8.7.1.8 V$RSRC_CONS_GROUP_HISTORY
      • 8.7.1.9 V$RSRC_CONSUMER_GROUP
      • 8.7.1.10 V$RSRC_PLAN
      • 8.7.1.11 V$RSRC_PLAN_HISTORY
      • 8.7.1.12 V$RSRC_SESSION_INFO
      • 8.7.1.13 V$RSRCMGRMETRIC
    • 8.7.2 Monitoring Session Statistics
    • 8.7.3 Monitoring System Statistics
    • 8.7.4 Monitoring Operating System Statistics
  • 8.8 Tips for Tuning Parallel Execution
    • 8.8.1 Implementing a Parallel Execution Strategy
    • 8.8.2 Optimizing Performance by Creating and Populating Tables in Parallel
    • 8.8.3 Using EXPLAIN PLAN to Show Parallel Operations Plans
      • 8.8.3.1 Example: Using EXPLAIN PLAN to Show Parallel Operations
    • 8.8.4 Additional Considerations for Parallel DML
      • 8.8.4.1 Parallel DML and Direct-Path Restrictions
      • 8.8.4.2 Limitation on the Degree of Parallelism
      • 8.8.4.3 When to Increase INITRANS
      • 8.8.4.4 Limitation on Available Number of Transaction Free Lists for Segments
      • 8.8.4.5 Multiple Archivers for Large Numbers of Redo Logs
      • 8.8.4.6 Database Writer Process (DBWn) Workload
      • 8.8.4.7 [NO]LOGGING Clause
    • 8.8.5 Optimizing Performance by Creating Indexes in Parallel
    • 8.8.6 Parallel DML Tips
      • 8.8.6.1 Parallel DML Tip 1: INSERT
      • 8.8.6.2 Parallel DML Tip 2: Direct-Path INSERT
      • 8.8.6.3 Parallel DML Tip 3: Parallelizing INSERT, MERGE, UPDATE, and DELETE
        • 8.8.6.3.1 Parallelizing INSERT SELECT
        • 8.8.6.3.2 Parallelizing UPDATE and DELETE
    • 8.8.7 Incremental Data Loading in Parallel
      • 8.8.7.1 Optimizing Performance for Updating the Table in Parallel
      • 8.8.7.2 Efficiently Inserting the New Rows into the Table in Parallel
      • 8.8.7.3 Optimizing Performance by Merging in Parallel
• 9 Backing Up and Recovering VLDBs
  • 9.1 Data Warehouses
    • 9.1.1 Data Warehouse Characteristics
  • 9.2 Oracle Backup and Recovery
    • 9.2.1 Physical Database Structures Used in Recovering Data
      • 9.2.1.1 Data files
      • 9.2.1.2 Redo Logs
      • 9.2.1.3 Control Files
    • 9.2.2 Backup Type
    • 9.2.3 Backup Tools
      • 9.2.3.1 Oracle Recovery Manager (RMAN)
      • 9.2.3.2 Oracle Data Pump
      • 9.2.3.3 User-Managed Backups
  • 9.3 Data Warehouse Backup and Recovery
    • 9.3.1 Recovery Time Objective (RTO)
    • 9.3.2 Recovery Point Objective (RPO)
      • 9.3.2.1 More Data Means a Longer Backup Window
      • 9.3.2.2 Divide and Conquer
  • 9.4 The Data Warehouse Recovery Methodology
    • 9.4.1 Best Practice 1: Use ARCHIVELOG Mode
      • 9.4.1.1 Is Downtime Acceptable?
    • 9.4.2 Best Practice 2: Use RMAN
    • 9.4.3 Best Practice 3: Use Block Change Tracking
    • 9.4.4 Best Practice 4: Use RMAN Multisection Backups
    • 9.4.5 Best Practice 5: Leverage Read-Only Tablespaces
    • 9.4.6 Best Practice 6: Plan for NOLOGGING Operations in Your Backup/Recovery Strategy
      • 9.4.6.1 Extract, Transform, and Load
      • 9.4.6.2 The Extract, Transform, and Load Strategy
      • 9.4.6.3 Incremental Backup
      • 9.4.6.4 The Incremental Approach
      • 9.4.6.5 Flashback Database and Guaranteed Restore Points
    • 9.4.7 Best Practice 7: Not All Tablespaces Should Be Treated Equally
• 10 Storage Management for VLDBs
  • 10.1 High Availability
    • 10.1.1 Hardware-Based Mirroring
      • 10.1.1.1 RAID 1 Mirroring
      • 10.1.1.2 RAID 5 Mirroring
    • 10.1.2 Mirroring Using Oracle ASM
  • 10.2 Performance
    • 10.2.1 Hardware-Based Striping
      • 10.2.1.1 RAID 0 Striping
      • 10.2.1.2 RAID 5 Striping
    • 10.2.2 Striping Using Oracle ASM
    • 10.2.3 Information Lifecycle Management
    • 10.2.4 Partition Placement
    • 10.2.5 Bigfile Tablespaces
    • 10.2.6 Oracle Database File System (DBFS)
  • 10.3 Scalability and Manageability
    • 10.3.1 Stripe and Mirror Everything (SAME)
    • 10.3.2 SAME and Manageability
  • 10.4 Oracle ASM Settings Specific to VLDBs
• Glossary
  • Information Lifecycle Management
• Index