Table of Contents
- List of Examples
- List of Figures
- List of Tables
- Title and Copyright Information
- Preface
-
Changes in This Release for Oracle Database VLDB and Partitioning Guide
- Changes for VLDB and Partitioning in Oracle Database 19c
- Changes for VLDB and Partitioning in Oracle Database Release 18c
- 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
-
2
Partitioning Concepts
-
Partitioning Overview
- Basics of Partitioning
- Partitioning Key
- Partitioned Tables
- Partitioned Index-Organized Tables
- System Partitioning
- Partitioning for Information Lifecycle Management
- Range Partitioning for Hash Clusters
- Partitioning and LOB Data
- Partitioning on External Tables
- Hybrid Partitioned Tables
- Collections in XMLType and Object Data
- Benefits of Partitioning
-
Partitioning Strategies
- Single-Level Partitioning
-
Composite Partitioning
- Composite Range-Range Partitioning
- Composite Range-Hash Partitioning
- Composite Range-List Partitioning
- Composite List-Range Partitioning
- Composite List-Hash Partitioning
- Composite List-List Partitioning
- Composite Hash-Hash Partitioning
- Composite Hash-List Partitioning
- Composite Hash-Range Partitioning
- Partitioning Extensions
- Indexing on Partitioned Tables
-
Partitioning Overview
-
3
Partitioning for Availability, Manageability, and Performance
- Partition Pruning
- Partition-Wise Operations
-
Index Partitioning
- Local Partitioned Indexes
- Global Partitioned Indexes
- Summary of Partitioned Index Types
- The Importance of Nonprefixed Indexes
- Performance Implications of Prefixed and Nonprefixed Indexes
- Advanced Index Compression With Partitioned Indexes
- Guidelines for Partitioning Indexes
- Physical Attributes of Index Partitions
- Partitioning and Table Compression
- Recommendations for Choosing a Partitioning Strategy
-
4
Partition Administration
-
Specifying Partitioning When Creating Tables and Indexes
- About Creating Range-Partitioned Tables and Global Indexes
- Creating Range-Interval-Partitioned Tables
- About Creating Hash Partitioned Tables and Global Indexes
- About Creating List-Partitioned Tables
- Creating Reference-Partitioned Tables
- Creating Interval-Reference Partitioned Tables
- Creating a Table Using In-Memory Column Store With Partitioning
- Creating a Table with Read-Only Partitions or Subpartitions
- Creating a Partitioned External Table
- Specifying Partitioning on Key Columns
- Using Virtual Column-Based Partitioning
- Using Table Compression with Partitioned Tables
- Using Key Compression with Partitioned Indexes
- Specifying Partitioning with Segments
- Specifying Partitioning When Creating Index-Organized Tables
- Partitioning Restrictions for Multiple Block Sizes
- Partitioning of Collections in XMLType and Objects
-
Specifying Composite Partitioning When Creating Tables
- Creating Composite Hash-* Partitioned Tables
- Creating Composite Interval-* Partitioned Tables
- Creating Composite List-* Partitioned Tables
- Creating Composite Range-* Partitioned Tables
- Specifying Subpartition Templates to Describe Composite Partitioned Tables
- Maintenance Operations Supported on Partitions
-
Maintenance Operations for Partitioned Tables and Indexes
-
About Adding Partitions and Subpartitions
- Adding a Partition to a Range-Partitioned Table
- Adding a Partition to a Hash-Partitioned Table
- Adding a Partition to a List-Partitioned Table
- Adding a Partition to an Interval-Partitioned Table
- About Adding Partitions to a Composite *-Hash Partitioned Table
- About Adding Partitions to a Composite *-List Partitioned Table
- About Adding Partitions to a Composite *-Range Partitioned Table
- About Adding a Partition or Subpartition to a Reference-Partitioned Table
- Adding Index Partitions
- Adding Multiple Partitions
- About Coalescing Partitions and Subpartitions
- About Dropping Partitions and Subpartitions
-
About Exchanging Partitions and Subpartitions
- Creating a Table for Exchange With a Partitioned Table
- Exchanging a Range, Hash, or List Partition
- Exchanging a Partition of an Interval Partitioned Table
- Exchanging a Partition of a Reference-Partitioned Table
- About Exchanging a Partition of a Table with Virtual Columns
- Exchanging a Hash Partitioned Table with a *-Hash Partition
- Exchanging a Subpartition of a *-Hash Partitioned Table
- Exchanging a List-Partitioned Table with a *-List Partition
- About Exchanging a Subpartition of a *-List Partitioned Table
- Exchanging a Range-Partitioned Table with a *-Range Partition
- About Exchanging a Subpartition of a *-Range Partitioned Table
- About Exchanging a Partition with the Cascade Option
- About Merging Partitions and Subpartitions
- About Modifying Attributes of Tables, Partitions, and Subpartitions
- About Modifying List Partitions
- About Modifying the Partitioning Strategy
- About Moving Partitions and Subpartitions
- About Rebuilding Index Partitions
- About Renaming Partitions and Subpartitions
-
About Splitting Partitions and Subpartitions
- Splitting a Partition of a Range-Partitioned Table
- Splitting a Partition of a List-Partitioned Table
- Splitting a Partition of an Interval-Partitioned Table
- Splitting a *-Hash Partition
- Splitting Partitions in a *-List Partitioned Table
- Splitting a *-Range Partition
- Splitting Index Partitions
- Splitting into Multiple Partitions
- Fast SPLIT PARTITION and SPLIT SUBPARTITION Operations
- About Truncating Partitions and Subpartitions
-
About Adding Partitions and Subpartitions
- About Dropping Partitioned Tables
- Changing a Nonpartitioned Table into a Partitioned Table
- Managing Hybrid Partitioned Tables
- Viewing Information About Partitioned Tables and Indexes
-
Specifying Partitioning When Creating Tables and Indexes
-
5
Managing and Maintaining Time-Based Information
- Managing Data in Oracle Database With ILM
-
Implementing an ILM Strategy With Heat Map and ADO
- Using Heat Map
-
Using Automatic Data Optimization
- Managing Policies for Automatic Data Optimization
- Creating a Table With an ILM ADO Policy
- Adding ILM ADO Policies
- Disabling and Deleting ILM ADO Policies
- Specifying Segment-Level Compression and Storage Tiering With ADO
- Specifying Row-Level Compression Tiering With ADO
- Managing ILM ADO Parameters
- Using PL/SQL Functions for Policy Management
- Using Views to Monitor Policies for ADO
- Limitations and Restrictions With ADO and Heat Map
- Controlling the Validity and Visibility of Data in Oracle Database
- Implementing an ILM System Manually Using Partitioning
-
Managing ILM Heat Map and ADO with Oracle Enterprise Manager
- Accessing the Database Administration Menu
- Viewing Automatic Data Optimization Activity at the Tablespace Level
- Viewing the Segment Activity Details of Any Tablespace
- Viewing the Segment Activity Details of Any Object
- Viewing the Segment Activity History of Any Object
- Searching Segment Activity in Automatic Data Optimization
- Viewing Policies for a Segment
- Disabling Background Activity
- Changing Execution Frequency of Background Automatic Data Optimization
- Viewing Policy Executions In the Last 24 Hours
- Viewing Objects Moved In Last 24 Hours
- Viewing Policy Details
- Viewing Objects Associated With a Policy
- Evaluating a Policy Before Execution
- Executing a Single Policy
- Stopping a Policy Execution
- Viewing Policy Execution History
-
6
Using Partitioning in a Data Warehouse Environment
- What Is a Data Warehouse?
- Scalability in a Data Warehouse
- Partitioning for Performance in a Data Warehouse
- Manageability in a Data Warehouse
-
7
Using Partitioning in an Online Transaction Processing Environment
- What Is an Online Transaction Processing System?
- Performance in an Online Transaction Processing Environment
- Manageability in an Online Transaction Processing Environment
-
8
Using Parallel Execution
- Parallel Execution Concepts
- Setting the Degree of Parallelism
- In-Memory Parallel Execution
-
Parallel Statement Queuing
-
About Managing Parallel Statement Queuing with Oracle Database Resource Manager
- About Managing the Order of the Parallel Statement Queue
- About Limiting the Parallel Server Resources for a Consumer Group
- Specifying a Parallel Statement Queue Timeout for Each Consumer Group
- Specifying a Degree of Parallelism Limit for Consumer Groups
- Critical Parallel Statement Prioritization
- A Sample Scenario for Managing Statements in the Parallel Queue
- Grouping Parallel Statements with BEGIN_SQL_BLOCK END_SQL_BLOCK
- About Managing Parallel Statement Queuing with Hints
-
About Managing Parallel Statement Queuing with Oracle Database Resource Manager
-
Types of Parallelism
- About Parallel Queries
- About Parallel DDL Statements
-
About Parallel DML Operations
- When to Use Parallel DML
- Enable Parallel DML Mode
- Rules for UPDATE, MERGE, and DELETE
- Rules for INSERT SELECT
- Transaction Restrictions for Parallel DML
- Rollback Segments
- Recovery for Parallel DML
- Space Considerations for Parallel DML
- Restrictions on Parallel DML
- Data Integrity Restrictions
- Trigger Restrictions
- Distributed Transaction Restrictions
- Examples of Distributed Transaction Parallelization
- Concurrent Execution of Union All
- About Parallel Execution of Functions
- About Other Types of Parallelism
- Degree of Parallelism Rules for SQL Statements
- About Initializing and Tuning Parameters for Parallel Execution
- Monitoring Parallel Execution Performance
-
Tips for Tuning Parallel Execution
- Implementing a Parallel Execution Strategy
- Optimizing Performance by Creating and Populating Tables in Parallel
- Using EXPLAIN PLAN to Show Parallel Operations Plans
- Additional Considerations for Parallel DML
- Optimizing Performance by Creating Indexes in Parallel
- Parallel DML Tips
- Incremental Data Loading in Parallel
-
9
Backing Up and Recovering VLDBs
- Data Warehouses
- Oracle Backup and Recovery
- Data Warehouse Backup and Recovery
-
The Data Warehouse Recovery Methodology
- Best Practice 1: Use ARCHIVELOG Mode
- Best Practice 2: Use RMAN
- Best Practice 3: Use Block Change Tracking
- Best Practice 4: Use RMAN Multisection Backups
- Best Practice 5: Leverage Read-Only Tablespaces
- Best Practice 6: Plan for NOLOGGING Operations in Your Backup/Recovery Strategy
- Best Practice 7: Not All Tablespaces Should Be Treated Equally
- 10 Storage Management for VLDBs
-
Glossary
- Index