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
  • Collections in XMLType and Object 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 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
    • Data Type Conversions
    • Function Calls
    • Collection Tables
• 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
  • Considerations When Using Read-Only Tablespaces

4 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
  • Partitioning of Collections in XMLType and Objects
    • Performing PMOs on Partitions that Contain Collection Tables
• 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
    • Redefining Partitions with Collection Tables
  • 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

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 Using Parallel Execution

• Introduction to Parallel Execution
  • When to Implement Parallel Execution
  • When Not to Implement Parallel Execution
  • Fundamental Hardware Requirements
  • Operations That Can Be Parallelized
• How Parallel Execution Works
  • Parallelizing SQL Statements
    • Dividing Work Among Parallel Execution Servers
    • Parallelism Between Operations
    • Producer/Consumer Operations
  • How Parallel Execution Servers Communicate
  • Degree of Parallelism
    • Manually Specifying the Degree of Parallelism
    • Automatic Degree of Parallelism
    • Controlling Automatic Degree of Parallelism
    • Statement Queuing
    • In-Memory Parallel Execution
    • Controlling Automatic Degree of Parallelism, Statement Queuing, and In-Memory Parallel Execution
    • Adaptive Parallelism
  • The Parallel Execution Server Pool
    • Processing Without Enough Parallel Execution Servers
  • Granules of Parallelism
    • Block Range Granules
    • Partition Granules
  • Balancing the Workload
  • Parallel Execution Using RAC
    • Limiting the Number of Available Instances
• Types of Parallelism
  • Parallel Query
    • Parallel Queries on Index-Organized Tables
    • Nonpartitioned Index-Organized Tables
    • Partitioned Index-Organized Tables
    • Parallel Queries on Object Types
    • Rules for Parallelizing Queries
  • Parallel DDL
    • DDL Statements That Can Be Parallelized
    • CREATE TABLE ... AS SELECT in Parallel
    • Recoverability and Parallel DDL
    • Space Management for Parallel DDL
    • Storage Space When Using Dictionary-Managed Tablespaces
    • Free Space and Parallel DDL
    • Rules for DDL Statements
    • Rules for [CREATE | REBUILD] INDEX or [MOVE | SPLIT] PARTITION
    • Rules for CREATE TABLE AS SELECT
  • Parallel DML
    • When to Use Parallel DML
    • Enabling Parallel DML
    • 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
  • Parallel Execution of Functions
    • Functions in Parallel Queries
    • Functions in Parallel DML and DDL Statements
  • Other Types of Parallelism
    • Summary of Parallelization Rules
• Initializing and Tuning Parameters for Parallel Execution
  • Using Default Parameter Settings
  • Forcing Parallel Execution for a Session
• Tuning General Parameters for Parallel Execution
  • Parameters Establishing Resource Limits for Parallel Operations
    • PARALLEL_FORCE_LOCAL
    • PARALLEL_MAX_SERVERS
    • Limiting the Number of Resources for a User
    • PARALLEL_MIN_PERCENT
    • PARALLEL_MIN_SERVERS
    • PARALLEL_SERVERS_TARGET
    • SHARED_POOL_SIZE
    • Computing Additional Memory Requirements for Message Buffers
    • Adjusting Memory After Processing Begins
  • Parameters Affecting Resource Consumption
    • PGA_AGGREGATE_TARGET
    • PARALLEL_EXECUTION_MESSAGE_SIZE
    • Parameters Affecting Resource Consumption for Parallel DML and Parallel DDL
  • Parameters Related to I/O
    • DB_CACHE_SIZE
    • DB_BLOCK_SIZE
    • DB_FILE_MULTIBLOCK_READ_COUNT
    • DISK_ASYNCH_IO and TAPE_ASYNCH_IO
• Monitoring Parallel Execution Performance
  • Monitoring Parallel Execution Performance with Dynamic Performance Views
    • V$PX_BUFFER_ADVICE
    • V$PX_SESSION
    • V$PX_SESSTAT
    • V$PX_PROCESS
    • V$PX_PROCESS_SYSSTAT
    • V$PQ_SESSTAT
    • V$PQ_TQSTAT
  • Monitoring Session Statistics
  • Monitoring System Statistics
  • Monitoring Operating System Statistics
• Miscellaneous Parallel Execution Tuning Tips
  • Creating and Populating Tables in Parallel
  • Using EXPLAIN PLAN to Show Parallel Operations Plans
    • Example: Using EXPLAIN PLAN to Show Parallel Operations
  • Additional Considerations for Parallel DML
    • Parallel DML and Direct-Path Restrictions
    • Limitation on the Degree of Parallelism
    • Increasing INITRANS
    • Limitation on Available Number of Transaction Free Lists for Segments
    • Using Multiple Archivers
    • Database Writer Process (DBWn) Workload
    • [NO]LOGGING Clause
  • Creating Indexes in Parallel
  • Parallel DML Tips
    • Parallel DML Tip 1: INSERT
    • Parallel DML Tip 2: Direct-Path INSERT
    • Parallel DML Tip 3: Parallelizing INSERT, MERGE, UPDATE, and DELETE
  • Incremental Data Loading in Parallel
    • Updating the Table in Parallel
    • Inserting the New Rows into the Table in Parallel
    • Merging in Parallel

9 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

10 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 (SAME)
  • SAME and Manageability
• ASM Settings Specific to VLDBs
• Monitoring Database Storage Using Database Control

Index