Table of Contents
- Title and Copyright Information
- Preface
- 1 Introduction to Very Large Databases
-
2
Partitioning Concepts
-
2.1
Partitioning Overview
- 2.1.1 Basics of Partitioning
- 2.1.2 Partitioning Key
- 2.1.3 Partitioned Tables
- 2.1.4 Partitioned Index-Organized Tables
- 2.1.5 System Partitioning
- 2.1.6 Partitioning for Information Lifecycle Management
- 2.1.7 Range Partitioning for Hash Clusters
- 2.1.8 Partitioning and LOB Data
- 2.1.9 Partitioning on External Tables
- 2.1.10 Hybrid Partitioned Tables
- 2.1.11 Collections in XMLType and Object Data
- 2.2 Benefits of Partitioning
-
2.3
Partitioning Strategies
- 2.3.1 Single-Level Partitioning
-
2.3.2
Composite Partitioning
- 2.3.2.1 Composite Range-Range Partitioning
- 2.3.2.2 Composite Range-Hash Partitioning
- 2.3.2.3 Composite Range-List Partitioning
- 2.3.2.4 Composite List-Range Partitioning
- 2.3.2.5 Composite List-Hash Partitioning
- 2.3.2.6 Composite List-List Partitioning
- 2.3.2.7 Composite Hash-Hash Partitioning
- 2.3.2.8 Composite Hash-List Partitioning
- 2.3.2.9 Composite Hash-Range Partitioning
- 2.4 Partitioning Extensions
-
2.5
Indexing on Partitioned Tables
- 2.5.1 Deciding on the Type of Partitioned Index to Use
- 2.5.2 Local Partitioned Indexes
- 2.5.3 Global Partitioned Indexes
- 2.5.4 Global Nonpartitioned Indexes
- 2.5.5 Miscellaneous Information about Creating Indexes on Partitioned Tables
- 2.5.6 Partial Indexes for Partitioned Tables
- 2.5.7 Partitioned Indexes on Composite Partitions
-
2.1
Partitioning Overview
-
3
Partitioning for Availability, Manageability, and Performance
- 3.1 Partition Pruning
- 3.2 Partition-Wise Operations
-
3.3
Index Partitioning
- 3.3.1 Local Partitioned Indexes
- 3.3.2 Global Partitioned Indexes
- 3.3.3 Summary of Partitioned Index Types
- 3.3.4 The Importance of Nonprefixed Indexes
- 3.3.5 Performance Implications of Prefixed and Nonprefixed Indexes
- 3.3.6 Advanced Index Compression With Partitioned Indexes
- 3.3.7 Guidelines for Partitioning Indexes
- 3.3.8 Physical Attributes of Index Partitions
- 3.4 Partitioning and Table Compression
-
3.5
Recommendations for Choosing a Partitioning Strategy
- 3.5.1 When to Use Range or Interval Partitioning
- 3.5.2 When to Use Hash Partitioning
- 3.5.3 When to Use List Partitioning
-
3.5.4
When to Use Composite Partitioning
- 3.5.4.1 When to Use Composite Range-Hash Partitioning
- 3.5.4.2 When to Use Composite Range-List Partitioning
- 3.5.4.3 When to Use Composite Range-Range Partitioning
- 3.5.4.4 When to Use Composite List-Hash Partitioning
- 3.5.4.5 When to Use Composite List-List Partitioning
- 3.5.4.6 When to Use Composite List-Range Partitioning
- 3.5.5 When to Use Interval Partitioning
- 3.5.6 When to Use Reference Partitioning
- 3.5.7 When to Partition on Virtual Columns
- 3.5.8 Considerations When Using Read-Only Tablespaces
-
4
Partition Administration
-
4.1
Specifying Partitioning When Creating Tables and Indexes
- 4.1.1 About Creating Range-Partitioned Tables and Global Indexes
- 4.1.2 Creating Range-Interval-Partitioned Tables
- 4.1.3 About Creating Hash Partitioned Tables and Global Indexes
- 4.1.4 About Creating List-Partitioned Tables
- 4.1.5 Creating Reference-Partitioned Tables
- 4.1.6 Creating Interval-Reference Partitioned Tables
- 4.1.7 Creating a Table Using In-Memory Column Store With Partitioning
- 4.1.8 Creating a Table with Read-Only Partitions or Subpartitions
- 4.1.9 Creating a Partitioned External Table
- 4.1.10 Specifying Partitioning on Key Columns
- 4.1.11 Using Virtual Column-Based Partitioning
- 4.1.12 Using Table Compression with Partitioned Tables
- 4.1.13 Using Key Compression with Partitioned Indexes
- 4.1.14 Specifying Partitioning with Segments
- 4.1.15 Specifying Partitioning When Creating Index-Organized Tables
- 4.1.16 Partitioning Restrictions for Multiple Block Sizes
- 4.1.17 Partitioning of Collections in XMLType and Objects
-
4.2
Specifying Composite Partitioning When Creating Tables
- 4.2.1 Creating Composite Hash-* Partitioned Tables
- 4.2.2 Creating Composite Interval-* Partitioned Tables
- 4.2.3 Creating Composite List-* Partitioned Tables
- 4.2.4 Creating Composite Range-* Partitioned Tables
- 4.2.5 Specifying Subpartition Templates to Describe Composite Partitioned Tables
- 4.3 Maintenance Operations Supported on Partitions
-
4.4
Maintenance Operations for Partitioned Tables and Indexes
-
4.4.1
About Adding Partitions and Subpartitions
- 4.4.1.1 Adding a Partition to a Range-Partitioned Table
- 4.4.1.2 Adding a Partition to a Hash-Partitioned Table
- 4.4.1.3 Adding a Partition to a List-Partitioned Table
- 4.4.1.4 Adding a Partition to an Interval-Partitioned Table
- 4.4.1.5 About Adding Partitions to a Composite *-Hash Partitioned Table
- 4.4.1.6 About Adding Partitions to a Composite *-List Partitioned Table
- 4.4.1.7 About Adding Partitions to a Composite *-Range Partitioned Table
- 4.4.1.8 About Adding a Partition or Subpartition to a Reference-Partitioned Table
- 4.4.1.9 Adding Index Partitions
- 4.4.1.10 Adding Multiple Partitions
- 4.4.2 About Coalescing Partitions and Subpartitions
- 4.4.3 About Dropping Partitions and Subpartitions
-
4.4.4
About Exchanging Partitions and Subpartitions
- 4.4.4.1 Creating a Table for Exchange With a Partitioned Table
- 4.4.4.2 Exchanging a Range, Hash, or List Partition
- 4.4.4.3 Exchanging a Partition of an Interval Partitioned Table
- 4.4.4.4 Exchanging a Partition of a Reference-Partitioned Table
- 4.4.4.5 About Exchanging a Partition of a Table with Virtual Columns
- 4.4.4.6 Exchanging a Hash Partitioned Table with a *-Hash Partition
- 4.4.4.7 Exchanging a Subpartition of a *-Hash Partitioned Table
- 4.4.4.8 Exchanging a List-Partitioned Table with a *-List Partition
- 4.4.4.9 About Exchanging a Subpartition of a *-List Partitioned Table
- 4.4.4.10 Exchanging a Range-Partitioned Table with a *-Range Partition
- 4.4.4.11 About Exchanging a Subpartition of a *-Range Partitioned Table
- 4.4.4.12 About Exchanging a Partition with the Cascade Option
- 4.4.5 About Merging Partitions and Subpartitions
- 4.4.6 About Modifying Attributes of Tables, Partitions, and Subpartitions
- 4.4.7 About Modifying List Partitions
- 4.4.8 About Modifying the Partitioning Strategy
- 4.4.9 About Moving Partitions and Subpartitions
- 4.4.10 About Rebuilding Index Partitions
- 4.4.11 About Renaming Partitions and Subpartitions
-
4.4.12
About Splitting Partitions and Subpartitions
- 4.4.12.1 Splitting a Partition of a Range-Partitioned Table
- 4.4.12.2 Splitting a Partition of a List-Partitioned Table
- 4.4.12.3 Splitting a Partition of an Interval-Partitioned Table
- 4.4.12.4 Splitting a *-Hash Partition
- 4.4.12.5 Splitting Partitions in a *-List Partitioned Table
- 4.4.12.6 Splitting a *-Range Partition
- 4.4.12.7 Splitting Index Partitions
- 4.4.12.8 Splitting into Multiple Partitions
- 4.4.12.9 Fast SPLIT PARTITION and SPLIT SUBPARTITION Operations
- 4.4.13 About Truncating Partitions and Subpartitions
-
4.4.1
About Adding Partitions and Subpartitions
- 4.5 About Dropping Partitioned Tables
- 4.6 Changing a Nonpartitioned Table into a Partitioned Table
-
4.7
Managing Hybrid Partitioned Tables
- 4.7.1 Creating Hybrid Partitioned Tables
- 4.7.2 Converting to Hybrid Partitioned Tables
- 4.7.3 Converting Hybrid Partitioned Tables to Internal Partitioned Tables
- 4.7.4 Using ADO With Hybrid Partitioned Tables
- 4.7.5 Splitting Partitions in a Hybrid Partitioned Table
- 4.7.6 Exchanging Data in Hybrid Partitioned Tables
- 4.8 Viewing Information About Partitioned Tables and Indexes
-
4.1
Specifying Partitioning When Creating Tables and Indexes
-
5
Managing and Maintaining Time-Based Information
- 5.1 Managing Data in Oracle Database With ILM
-
5.2
Implementing an ILM Strategy With Heat Map and ADO
- 5.2.1 Using Heat Map
-
5.2.2
Using Automatic Data Optimization
- 5.2.2.1 Managing Policies for Automatic Data Optimization
- 5.2.2.2 Creating a Table With an ILM ADO Policy
- 5.2.2.3 Adding ILM ADO Policies
- 5.2.2.4 Disabling and Deleting ILM ADO Policies
- 5.2.2.5 Specifying Segment-Level Compression and Storage Tiering With ADO
- 5.2.2.6 Specifying Row-Level Compression Tiering With ADO
- 5.2.2.7 Managing ILM ADO Parameters
- 5.2.2.8 Using PL/SQL Functions for Policy Management
- 5.2.2.9 Using Views to Monitor Policies for ADO
- 5.2.3 Limitations and Restrictions With ADO and Heat Map
- 5.3 Controlling the Validity and Visibility of Data in Oracle Database
- 5.4 Implementing an ILM System Manually Using Partitioning
-
5.5
Managing ILM Heat Map and ADO with Oracle Enterprise Manager
- 5.5.1 Accessing the Database Administration Menu
- 5.5.2 Viewing Automatic Data Optimization Activity at the Tablespace Level
- 5.5.3 Viewing the Segment Activity Details of Any Tablespace
- 5.5.4 Viewing the Segment Activity Details of Any Object
- 5.5.5 Viewing the Segment Activity History of Any Object
- 5.5.6 Searching Segment Activity in Automatic Data Optimization
- 5.5.7 Viewing Policies for a Segment
- 5.5.8 Disabling Background Activity
- 5.5.9 Changing Execution Frequency of Background Automatic Data Optimization
- 5.5.10 Viewing Policy Executions In the Last 24 Hours
- 5.5.11 Viewing Objects Moved In Last 24 Hours
- 5.5.12 Viewing Policy Details
- 5.5.13 Viewing Objects Associated With a Policy
- 5.5.14 Evaluating a Policy Before Execution
- 5.5.15 Executing a Single Policy
- 5.5.16 Stopping a Policy Execution
- 5.5.17 Viewing Policy Execution History
-
6
Using Partitioning in a Data Warehouse Environment
- 6.1 What Is a Data Warehouse?
- 6.2 Scalability in a Data Warehouse
- 6.3 Partitioning for Performance in a Data Warehouse
- 6.4 Manageability in a Data Warehouse
-
7
Using Partitioning in an Online Transaction Processing Environment
- 7.1 What Is an Online Transaction Processing System?
- 7.2 Performance in an Online Transaction Processing Environment
- 7.3 Manageability in an Online Transaction Processing Environment
-
8
Using Parallel Execution
-
8.1
Parallel Execution Concepts
- 8.1.1 When to Implement Parallel Execution
- 8.1.2 When Not to Implement Parallel Execution
- 8.1.3 Fundamental Hardware Requirements
- 8.1.4 How Parallel Execution Works
- 8.1.5 Parallel Execution Server Pool
- 8.1.6 Balancing the Workload to Optimize Performance
- 8.1.7 Multiple Parallelizers
- 8.1.8 Parallel Execution on Oracle RAC
- 8.2 Setting the Degree of Parallelism
- 8.3 In-Memory Parallel Execution
-
8.4
Parallel Statement Queuing
-
8.4.1
About Managing Parallel Statement Queuing with Oracle Database Resource Manager
- 8.4.1.1 About Managing the Order of the Parallel Statement Queue
- 8.4.1.2 About Limiting the Parallel Server Resources for a Consumer Group
- 8.4.1.3 Specifying a Parallel Statement Queue Timeout for Each Consumer Group
- 8.4.1.4 Specifying a Degree of Parallelism Limit for Consumer Groups
- 8.4.1.5 Critical Parallel Statement Prioritization
- 8.4.1.6 A Sample Scenario for Managing Statements in the Parallel Queue
- 8.4.2 Grouping Parallel Statements with BEGIN_SQL_BLOCK END_SQL_BLOCK
- 8.4.3 About Managing Parallel Statement Queuing with Hints
-
8.4.1
About Managing Parallel Statement Queuing with Oracle Database Resource Manager
-
8.5
Types of Parallelism
- 8.5.1 About Parallel Queries
-
8.5.2
About Parallel DDL Statements
- 8.5.2.1 DDL Statements That Can Be Parallelized
- 8.5.2.2 About Using CREATE TABLE AS SELECT in Parallel
- 8.5.2.3 Recoverability and Parallel DDL
- 8.5.2.4 Space Management for Parallel DDL
- 8.5.2.5 Storage Space When Using Dictionary-Managed Tablespaces
- 8.5.2.6 Free Space and Parallel DDL
- 8.5.2.7 Rules for DDL Statements
- 8.5.2.8 Rules for CREATE TABLE AS SELECT
-
8.5.3
About Parallel DML Operations
- 8.5.3.1 When to Use Parallel DML
- 8.5.3.2 Enable Parallel DML Mode
- 8.5.3.3 Rules for UPDATE, MERGE, and DELETE
- 8.5.3.4 Rules for INSERT SELECT
- 8.5.3.5 Transaction Restrictions for Parallel DML
- 8.5.3.6 Rollback Segments
- 8.5.3.7 Recovery for Parallel DML
- 8.5.3.8 Space Considerations for Parallel DML
- 8.5.3.9 Restrictions on Parallel DML
- 8.5.3.10 Data Integrity Restrictions
- 8.5.3.11 Trigger Restrictions
- 8.5.3.12 Distributed Transaction Restrictions
- 8.5.3.13 Examples of Distributed Transaction Parallelization
- 8.5.3.14 Concurrent Execution of Union All
- 8.5.4 About Parallel Execution of Functions
- 8.5.5 About Other Types of Parallelism
- 8.5.6 Degree of Parallelism Rules for SQL Statements
-
8.6
About Initializing and Tuning Parameters for Parallel Execution
- 8.6.1 Default Parameter Settings
- 8.6.2 Forcing Parallel Execution for a Session
-
8.6.3
Tuning General Parameters for Parallel Execution
-
8.6.3.1
Parameters Establishing Resource Limits for Parallel Operations
- 8.6.3.1.1 PARALLEL_FORCE_LOCAL
- 8.6.3.1.2 PARALLEL_MAX_SERVERS
- 8.6.3.1.3 PARALLEL_MIN_PERCENT
- 8.6.3.1.4 PARALLEL_MIN_SERVERS
- 8.6.3.1.5 PARALLEL_MIN_TIME_THRESHOLD
- 8.6.3.1.6 PARALLEL_SERVERS_TARGET
- 8.6.3.1.7 SHARED_POOL_SIZE
- 8.6.3.1.8 Additional Memory Requirements for Message Buffers
- 8.6.3.1.9 Monitor Memory Usage After Processing Begins
- 8.6.3.2 Parameters Affecting Resource Consumption
- 8.6.3.3 Parameters Related to I/O
-
8.6.3.1
Parameters Establishing Resource Limits for Parallel Operations
-
8.7
Monitoring Parallel Execution Performance
-
8.7.1
Monitoring Parallel Execution Performance with Dynamic Performance Views
- 8.7.1.1 V$PX_BUFFER_ADVICE
- 8.7.1.2 V$PX_SESSION
- 8.7.1.3 V$PX_SESSTAT
- 8.7.1.4 V$PX_PROCESS
- 8.7.1.5 V$PX_PROCESS_SYSSTAT
- 8.7.1.6 V$PQ_SESSTAT
- 8.7.1.7 V$PQ_TQSTAT
- 8.7.1.8 V$RSRC_CONS_GROUP_HISTORY
- 8.7.1.9 V$RSRC_CONSUMER_GROUP
- 8.7.1.10 V$RSRC_PLAN
- 8.7.1.11 V$RSRC_PLAN_HISTORY
- 8.7.1.12 V$RSRC_SESSION_INFO
- 8.7.1.13 V$RSRCMGRMETRIC
- 8.7.2 Monitoring Session Statistics
- 8.7.3 Monitoring System Statistics
- 8.7.4 Monitoring Operating System Statistics
-
8.7.1
Monitoring Parallel Execution Performance with Dynamic Performance Views
-
8.8
Tips for Tuning Parallel Execution
- 8.8.1 Implementing a Parallel Execution Strategy
- 8.8.2 Optimizing Performance by Creating and Populating Tables in Parallel
- 8.8.3 Using EXPLAIN PLAN to Show Parallel Operations Plans
-
8.8.4
Additional Considerations for Parallel DML
- 8.8.4.1 Parallel DML and Direct-Path Restrictions
- 8.8.4.2 Limitation on the Degree of Parallelism
- 8.8.4.3 When to Increase INITRANS
- 8.8.4.4 Limitation on Available Number of Transaction Free Lists for Segments
- 8.8.4.5 Multiple Archivers for Large Numbers of Redo Logs
- 8.8.4.6 Database Writer Process (DBWn) Workload
- 8.8.4.7 [NO]LOGGING Clause
- 8.8.5 Optimizing Performance by Creating Indexes in Parallel
- 8.8.6 Parallel DML Tips
- 8.8.7 Incremental Data Loading in Parallel
-
8.1
Parallel Execution Concepts
-
9
Backing Up and Recovering VLDBs
- 9.1 Data Warehouses
- 9.2 Oracle Backup and Recovery
- 9.3 Data Warehouse Backup and Recovery
-
9.4
The Data Warehouse Recovery Methodology
- 9.4.1 Best Practice 1: Use ARCHIVELOG Mode
- 9.4.2 Best Practice 2: Use RMAN
- 9.4.3 Best Practice 3: Use Block Change Tracking
- 9.4.4 Best Practice 4: Use RMAN Multisection Backups
- 9.4.5 Best Practice 5: Leverage Read-Only Tablespaces
- 9.4.6 Best Practice 6: Plan for NOLOGGING Operations in Your Backup/Recovery Strategy
- 9.4.7 Best Practice 7: Not All Tablespaces Should Be Treated Equally
- 10 Storage Management for VLDBs
-
Glossary
- Index