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
Scripting on this page enhances content navigation, but does not change the content in any way.