Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

1 Introduction to Very Large Databases

• Introduction to Partitioning
• VLDB and Partitioning
• Partitioning As the Foundation for Information Lifecycle Management
• Partitioning for Every Database

2 Partitioning Concepts

• Basics of Partitioning
  • Partitioning Key
  • Partitioned Tables
    • When to Partition a Table
    • When to Partition an Index
  • Partitioned Index-Organized Tables
  • System Partitioning
  • Partitioning for Information Lifecycle Management
  • Partitioning and LOB Data
• Benefits of Partitioning
  • Partitioning for Performance
    • Partition Pruning
    • Partition-Wise Joins
  • Partitioning for Manageability
  • Partitioning for Availability
• Partitioning Strategies
  • Single-Level Partitioning
    • Range Partitioning
    • Hash Partitioning
    • List 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
• Partitioning Extensions
  • Manageability Extensions
    • Interval Partitioning
    • Partition Advisor
  • Partitioning Key Extensions
    • Reference Partitioning
    • Virtual Column-Based Partitioning
• Overview of Partitioned Indexes
  • Local Partitioned Indexes
  • Global Partitioned Indexes
    • Global Range Partitioned Indexes
    • Global Hash Partitioned Indexes
    • Maintenance of Global Partitioned Indexes
  • Global Non-Partitioned Indexes
  • Miscellaneous Information about Creating Indexes on Partitioned Tables
  • Partitioned Indexes on Composite Partitions

3 Partition Administration

• Creating Partitions
  • Creating Range-Partitioned Tables and Global Indexes
    • Creating a Range Partitioned Table
    • Creating a Range-Partitioned Global Index
  • Creating Interval-Partitioned Tables
  • Creating Hash-Partitioned Tables and Global Indexes
    • Creating a Hash Partitioned Table
    • Creating a Hash-Partitioned Global Index
  • Creating List-Partitioned Tables
  • Creating Reference-Partitioned Tables
  • Creating Composite Partitioned Tables
    • Creating Composite Range-Hash Partitioned Tables
    • Creating Composite Range-List Partitioned Tables
    • Creating Composite Range-Range Partitioned Tables
    • Creating Composite List-* Partitioned Tables
    • Creating Composite Interval-* Partitioned Tables
  • Using Subpartition Templates to Describe Composite Partitioned Tables
    • Specifying a Subpartition Template for a *-Hash Partitioned Table
    • Specifying a Subpartition Template for a *-List Partitioned Table
  • Using Multicolumn Partitioning Keys
  • Using Virtual Column-Based Partitioning
  • Using Table Compression with Partitioned Tables
  • Using Key Compression with Partitioned Indexes
  • Creating Partitioned Index-Organized Tables
    • Creating Range-Partitioned Index-Organized Tables
    • Creating Hash-Partitioned Index-Organized Tables
    • Creating List-Partitioned Index-Organized Tables
  • Partitioning Restrictions for Multiple Block Sizes
• Maintaining Partitions
  • Updating Indexes Automatically
  • Adding Partitions
    • 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
    • Adding Partitions to a Composite [Range | List | Interval]-Hash Partitioned Table
    • Adding Partitions to a Composite [Range | List | Interval]-List Partitioned Table
    • Adding Partitions to a Composite [Range | List | Interval]-Range Partitioned Table
    • Adding a Partition or Subpartition to a Reference-Partitioned Table
    • Adding Index Partitions
  • Coalescing Partitions
    • Coalescing a Partition in a Hash-Partitioned Table
    • Coalescing a Subpartition in a *-Hash Partitioned Table
    • Coalescing Hash-partitioned Global Indexes
  • Dropping Partitions
    • Dropping Table Partitions
    • Dropping Interval Partitions
    • Dropping Index Partitions
  • Exchanging Partitions
    • Exchanging a Range, Hash, or List Partition
    • Exchanging a Partition of an Interval Partitioned Table
    • Exchanging a Partition of a Reference Partitioned Table
    • 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
    • Exchanging a Subpartition of a *-List Partitioned Table
    • Exchanging a Range-Partitioned Table with a *-Range Partition
    • Exchanging a Subpartition of a *-Range Partitioned Table
  • Merging Partitions
    • Merging Range Partitions
    • Merging Interval Partitions
    • Merging List Partitions
    • Merging *-Hash Partitions
    • Merging *-List Partitions
    • Merging *-Range Partitions
  • Modifying Default Attributes
    • Modifying Default Attributes of a Table
    • Modifying Default Attributes of a Partition
    • Modifying Default Attributes of Index Partitions
  • Modifying Real Attributes of Partitions
    • Modifying Real Attributes for a Range or List Partition
    • Modifying Real Attributes for a Hash Partition
    • Modifying Real Attributes of a Subpartition
    • Modifying Real Attributes of Index Partitions
  • Modifying List Partitions: Adding Values
    • Adding Values for a List Partition
    • Adding Values for a List Subpartition
  • Modifying List Partitions: Dropping Values
    • Dropping Values from a List Partition
    • Dropping Values from a List Subpartition
  • Modifying a Subpartition Template
  • Moving Partitions
    • Moving Table Partitions
    • Moving Subpartitions
    • Moving Index Partitions
  • Redefining Partitions Online
  • Rebuilding Index Partitions
    • Rebuilding Global Index Partitions
    • Rebuilding Local Index Partitions
  • Renaming Partitions
    • Renaming a Table Partition
    • Renaming a Table Subpartition
    • Renaming Index Partitions
  • Splitting Partitions
    • 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
    • Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations
  • Truncating Partitions
    • Truncating a Table Partition
    • Truncating a Subpartition
• Dropping Partitioned Tables
• Partitioned Tables and Indexes Example
• Viewing Information About Partitioned Tables and Indexes

4 Partitioning for Availability, Manageability, and Performance

• 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
    • Dynamic Pruning with Bind Variables
    • Dynamic Pruning with Subqueries
    • Dynamic Pruning with Star Transformation
    • Dynamic Pruning with Nested Loop Joins
  • Partition Pruning Tips
    • Datatype Conversions
    • Function Calls
• Partition-Wise Joins
  • Full Partition-Wise Joins
    • Full Partition-Wise Joins: Single-Level - Single-Level
    • Full Partition-Wise Joins: Composite - Single-Level
    • Full Partition-Wise Joins: Composite - Composite
  • Partial Partition-Wise Joins
    • Partial Partition-Wise Joins: Single-Level Partitioning
    • Partial Partition-Wise Joins: Composite
• Index Partitioning
  • Local Partitioned Indexes
    • Local Prefixed Indexes
    • Local Nonprefixed Indexes
  • Global Partitioned Indexes
    • Prefixed and Nonprefixed Global Partitioned Indexes
    • Management of Global Partitioned Indexes
  • Summary of Partitioned Index Types
  • The Importance of Nonprefixed Indexes
  • Performance Implications of Prefixed and Nonprefixed 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 Composite Range-Hash Partitioning
    • When to Use Composite Range-List Partitioning
    • When to Use Composite Range-Range Partitioning
    • When to Use Composite List-Hash Partitioning
    • When to Use Composite List-List Partitioning
    • When to Use Composite List-Range Partitioning
  • When to Use Interval Partitioning
  • When to Use Reference Partitioning
  • When to Partition on Virtual Columns

5 Using Partitioning for Information Lifecycle Management

• What Is ILM?
  • Oracle Database for ILM
    • Oracle Database Manages All Types of Data
  • Regulatory Requirements
• Implementing ILM Using Oracle Database
  • Step 1: Define the Data Classes
    • Partitioning
    • The Lifecycle of Data
  • Step 2: Create Storage Tiers for the Data Classes
    • Assigning Classes to Storage Tiers
    • The Costs Savings of using Tiered Storage
  • Step 3: Create Data Access and Migration Policies
    • Controlling Access to Data
    • Moving Data using Partitioning
  • Step 4: Define and Enforce Compliance Policies
    • Data Retention
    • Immutability
    • Privacy
    • Auditing
    • Expiration
• The Benefits of an Online Archive
• Oracle ILM Assistant
  • Lifecycle Setup
    • Logical Storage Tiers
    • Lifecycle Definitions
    • Lifecycle Tables
    • Preferences
  • Lifecycle Management
    • Lifecycle Events Calendar
    • Lifecycle Events
    • Event Scan History
  • Compliance & Security
    • Current Status
    • Digital Signatures and Immutability
    • Privacy & Security
    • Auditing
  • Reports
• Implementing an ILM System Manually

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
    • Basic Partition Pruning Techniques
    • Advanced Partition Pruning Techniques
  • Partition-Wise Joins
    • Full Partition-Wise Joins
    • Partial Partition-Wise Joins
    • Benefits of Partition-Wise Joins
    • Performance Considerations for Parallel Partition-Wise Joins
  • Indexes and Partitioned Indexes
    • Local Partitioned Indexes
    • Non-Partitioned Indexes
    • Global Partitioned Indexes
    • Partitioning and Data Compression
    • Materialized Views and Partitioning
• Manageability
  • Partition Exchange Load
  • Partitioning and Indexes
  • Partitioning and Materialized View Refresh Strategies
  • Removing Data from Tables
  • Partitioning and Data Compression
  • Gathering Statistics on Large Partitioned Tables

7 Using Partitioning in an Online Transaction Processing Environment

• What is an OLTP System?
• Performance
  • Deciding Whether or not to Partition Indexes
    • Using Index-Organized Tables
• 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
    • Removing (Purging) Old Data
    • Moving and/or Merging Older Partitions to a Low Cost Storage Tier Device

8 Backing Up and Recovering VLDBs

• Data Warehousing
  • Data Warehouse Characteristics
• Oracle Backup and Recovery
  • Physical Database Structures Used in Recovering Data
    • Datafiles
    • Redo Logs
    • Control Files
  • Backup Type
  • Backup Tools
    • Recovery Manager (RMAN)
    • Oracle Enterprise Manager
    • Oracle Data Pump
    • User-Managed Backups
• Data Warehouse Backup and Recovery
  • Recovery Time Objective (RTO)
  • Recovery Point Objective (RPO)
    • More Data Means a Longer Backup Window
    • Divide and Conquer
• The Data Warehouse Recovery Methodology
• Best Practice 1: Use ARCHIVELOG Mode
  • Is Downtime Acceptable?
• Best Practice 2: Use RMAN
• Best Practice 3: Use Block Change Tracking
• Best Practice 4: Use RMAN Multi-Section Backups
• Best Practice 5: Leverage Read-Only Tablespaces
• Best Practice 6: Plan for NOLOGGING Operations in Your Backup/Recovery Strategy
  • Extract, Transform, and Load
  • The ETL Strategy
  • Incremental Backup
  • The Incremental Approach
  • Flashback Database and Guaranteed Restore Points
• Best Practice 7: Not All Tablespaces Are Created Equal

9 Storage Management for VLDBs

• High Availability
  • Hardware-Based Mirroring
    • RAID 1 Mirroring
    • RAID 5 Mirroring
  • Mirroring using ASM
• Performance
  • Hardware-Based Striping
    • RAID 0 Striping
    • RAID 5 Striping
  • Striping Using ASM
  • ILM
  • Partition Placement
  • Bigfile Tablespaces
• Scalability and Manageability
  • Stripe and Mirror Everything (S.A.M.E.)
  • S.A.M.E. and Manageability
• ASM Settings Specific to VLDBs
• Monitoring Database Storage Using Database Control

Index