1/478

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database VLDB and Partitioning Guide

• Changes for Very Large Databases and Partitioning in Oracle Database 12c Release 1 (12.1.0.2)
  • New Features
• Changes for Very Large Databases and Partitioning in Oracle Database 12c Release 1 (12.1.0.1)
  • New Features

1 Introduction to Very Large Databases

• Introduction to Partitioning
• VLDB and Partitioning
• Partitioning As the Foundation for Information Lifecycle Management
• Partitioning for All 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
  • Collections in XMLType and Object Data
• Benefits of Partitioning
  • Partitioning for Performance
  • Partitioning for Manageability
  • Partitioning for Availability
• Partitioning Strategies
  • Single-Level Partitioning
  • Composite Partitioning
• Partitioning Extensions
  • Manageability Extensions
  • Partitioning Key Extensions
• Indexing on Partitioned Tables
  • Deciding on the Type of Partitioned Index to Use
  • Local Partitioned Indexes
  • Global Partitioned Indexes
  • Global Nonpartitioned Indexes
  • Miscellaneous Information about Creating Indexes on Partitioned Tables
  • Partial Indexes for Partitioned Tables
  • Partitioned Indexes on Composite Partitions

3 Partitioning for Availability, Manageability, and Performance

• Partition Pruning
  • Benefits of Partition Pruning
  • Information That Can Be Used for Partition Pruning
  • How to Identify Whether Partition Pruning Has Been Used
  • Static Partition Pruning
  • Dynamic Partition Pruning
  • Partition Pruning with Zone Maps
  • Partition Pruning Tips
• Partition-Wise Joins
  • Full Partition-Wise Joins
  • Partial Partition-Wise Joins
• 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
  • Table Compression and Bitmap Indexes
  • Example of Table Compression and Partitioning
• Recommendations for Choosing a Partitioning Strategy
  • When to Use Range or Interval Partitioning
  • When to Use Hash Partitioning
  • When to Use List Partitioning
  • When to Use Composite Partitioning
  • When to Use Interval Partitioning
  • When to Use Reference Partitioning
  • When to Partition on Virtual Columns
  • Considerations When Using Read-Only Tablespaces

4 Partition Administration

• Specifying Partitioning When Creating Tables and Indexes
  • About Creating Range-Partitioned Tables and Global Indexes
  • Creating Interval-Partitioned Tables
  • Specifying Hash Partitioning When Creating Tables and Global Indexes
  • About Creating List-Partitioned Tables
  • Creating Reference-Partitioned Tables
  • Creating Interval-Reference Partitioned Tables
  • Specifying Composite Partitioning When Creating Tables
  • Creating a Table Using In-Memory Column Store With Partitioning
  • Specifying Subpartition Templates to Describe Composite Partitioned Tables
  • 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
• Maintenance Operations for Partitioned Tables and Indexes
  • Maintenance Operations on Partitions That Can Be Performed
  • Updating Indexes Automatically
  • Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
  • About Adding Partitions and Subpartitions
  • About Coalescing Partitions and Subpartitions
  • About Dropping Partitions and Subpartitions
  • About Exchanging Partitions and Subpartitions
  • About Merging Partitions and Subpartitions
  • About Modifying Default Attributes
  • About Modifying Real Attributes of Partitions
  • About Modifying List Partitions: Adding Values
  • About Modifying List Partitions: Dropping Values
  • Modifying a Subpartition Template
  • About Moving Partitions and Subpartitions
  • About Rebuilding Index Partitions
  • About Renaming Partitions and Subpartitions
  • About Splitting Partitions and Subpartitions
  • About Truncating Partitions and Subpartitions
• About Dropping Partitioned Tables
• Evolving a Nonpartitioned Table into a Partitioned Table
  • Using Online Redefinition to Partition Collection Tables
• Viewing Information About Partitioned Tables and Indexes

5 Managing and Maintaining Time-Based Information

• Managing Data in Oracle Database With ILM
  • About Oracle Database for ILM
  • Implementing ILM Using Oracle Database
• Implementing an ILM Strategy With Heat Map and ADO
  • Using Heat Map
  • Using Automatic Data Optimization
  • Limitations and Restrictions With ADO and Heat Map
• Controlling the Validity and Visibility of Data in Oracle Database
  • Using In-Database Archiving
  • Using Temporal Validity
  • Creating a Table With Temporal Validity
  • Limitations and Restrictions With In-Database Archiving and Temporal Validity
• 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
  • Bigger Databases
  • Bigger Individual Tables: More Rows in Tables
  • More Users Querying the System
  • More Complex Queries
• Performance
  • Partition Pruning
  • Partition-Wise Joins
  • Indexes and Partitioned Indexes
  • Materialized Views and Partitioning
• Manageability
  • Partition Exchange Load
  • Partitioning and Indexes
  • Removing Data from Tables
  • Partitioning and Data Compression

7 Using Partitioning in an Online Transaction Processing Environment

• What Is an OLTP System?
• Performance
  • Deciding Whether to Partition Indexes
• Manageability
  • Impact of a Partition Maintenance Operation on a Partitioned Table with Local Indexes
  • Impact of a Partition Maintenance Operation on Global Indexes
  • Common Partition Maintenance Operations in OLTP Environments

8 Using Parallel Execution

• Introduction to Parallel Execution
  • When to Implement Parallel Execution
  • When Not to Implement Parallel Execution
  • Fundamental Hardware Requirements
• How Parallel Execution Works
  • Parallel Execution of SQL Statements
  • How Parallel Execution Servers Communicate
  • Degree of Parallelism
  • About Parallel Statement Queuing
  • Parallel Execution Server Pool
  • Granules of Parallelism
  • Balancing the Workload to Optimize Performance
  • Parallel Execution Using Oracle RAC
• Types of Parallelism
  • About Parallel Queries
  • About Parallel DDL Statements
  • About Parallel DML Operations
  • About Parallel Execution of Functions
  • About Other Types of Parallelism
• About Initializing and Tuning Parameters for Parallel Execution
  • Default Parameter Settings
  • Forcing Parallel Execution for a Session
• Tuning General Parameters for Parallel Execution
  • Parameters Establishing Resource Limits for Parallel Operations
  • Parameters Affecting Resource Consumption
  • Parameters Related to I/O
• Monitoring Parallel Execution Performance
  • Monitoring Parallel Execution Performance with Dynamic Performance Views
  • Monitoring Session Statistics
  • Monitoring System Statistics
  • Monitoring Operating System Statistics
• Miscellaneous Parallel Execution Tuning Tips
  • 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
• Automatic Big Table Caching

9 Backing Up and Recovering VLDBs

• Data Warehouses
  • Data Warehouse Characteristics
• Oracle Backup and Recovery
  • Physical Database Structures Used in Recovering Data
  • Backup Type
  • Backup Tools
• Data Warehouse Backup and Recovery
  • Recovery Time Objective (RTO)
  • Recovery Point Objective (RPO)
• 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

• High Availability
  • Hardware-Based Mirroring
  • Mirroring Using Oracle ASM
• Performance
  • Hardware-Based Striping
  • Striping Using Oracle ASM
  • Information Lifecycle Management
  • Partition Placement
  • Bigfile Tablespaces
  • Oracle Database File System (DBFS)
• Scalability and Manageability
  • Stripe and Mirror Everything (SAME)
  • SAME and Manageability
• Oracle ASM Settings Specific to VLDBs

Index