Transcription
Oracle DatabaseVLDB and Partitioning Guide19cE96199-11August 2021
Oracle Database VLDB and Partitioning Guide, 19cE96199-11Copyright 2008, 2021, Oracle and/or its affiliates.Primary Author: Eric BeldenContributors: Penny Avril, Hermann Baer, Yasin Baskan, Gregg Christman, Jean-Pierre Dijcks, SandeepDoraiswamy, Amit Ganesh, Lilian Hobbs, Kevin Jernigan, Dominique Jeunot, Hariharan Lakshmanan, PaulLane, Sue K. Lee, Diana Lorentz, Vineet Marwah, Valarie Moore, Sujatha Muthulingam, Ajit Mylavarapu, TonyMorales, Ananth Raghavan, Venkatesh Radhakrishnan, Vivekanandhan Raja, Andy Rivenes, ChandrajithUnnithan, Mark Van de WielContributors: Frederick KushThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,any programs embedded, installed or activated on delivered hardware, and modifications of such programs)and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government endusers are "commercial computer software" or "commercial computer software documentation" pursuant to theapplicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use,reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/oradaptation of i) Oracle programs (including any operating system, integrated software, any programsembedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oraclecomputer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in thelicense contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloudservices are defined by the applicable contract for such services. No other rights are granted to the U.S.Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc,and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registeredtrademark of The Open Group.This software or hardware and documentation may provide access to or information about content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not beresponsible for any loss, costs, or damages incurred due to your access to or use of third-party content,products, or services, except as set forth in an applicable agreement between you and Oracle.
ContentsPrefaceAudiencexxiiDocumentation AccessibilityxxiiRelated DocumentsxxiiConventionsxxiiiChanges in This Release for Oracle Database VLDB and PartitioningGuide12Changes for VLDB and Partitioning in Oracle Database 19cxxivChanges for VLDB and Partitioning in Oracle Database Release 18cxxvIntroduction to Very Large Databases1.1Introduction to Partitioning1-11.2VLDB and Partitioning1-21.3Partitioning As the Foundation for Information Lifecycle Management1-31.4Partitioning for All Databases1-3Partitioning Concepts2.1Partitioning Overview2-12.1.1Basics of Partitioning2-22.1.2Partitioning Key2-32.1.3Partitioned Tables2-42.1.3.1When to Partition a Table2-42.1.3.2When to Partition an Index2-42.1.4Partitioned Index-Organized Tables2-52.1.5System Partitioning2-52.1.6Partitioning for Information Lifecycle Management2-52.1.7Range Partitioning for Hash Clusters2-62.1.8Partitioning and LOB Data2-62.1.9Partitioning on External Tables2-6iii
2.22.1.10Hybrid Partitioned Tables2.1.11Collections in XMLType and Object DataPartitioning for Performance2-11Partition Pruning for Performance2-112.2.1.2Partition-Wise Joins for Performance2-112.2.2Partitioning for Manageability2-112.2.3Partitioning for Availability2-12Partitioning Strategies2-12Single-Level Partitioning2-122.3.1.1Range Partitioning2-132.3.1.2Hash Partitioning2-132.3.1.3List Partitioning2-142.3.2Composite Partitioning2-142.3.2.1Composite Range-Range Partitioning2-152.3.2.2Composite Range-Hash Partitioning2-152.3.2.3Composite Range-List Partitioning2-152.3.2.4Composite List-Range Partitioning2-162.3.2.5Composite List-Hash Partitioning2-162.3.2.6Composite List-List Partitioning2-162.3.2.7Composite Hash-Hash Partitioning2-162.3.2.8Composite Hash-List Partitioning2-162.3.2.9Composite Hash-Range Partitioning2-16Partitioning Extensions2.4.12-16Manageability Extensions2-172.4.1.1Interval Partitioning2-172.4.1.2Partition Advisor2-17Partitioning Key its of Partitioning2.2.12.32-72.4.2.1Reference Partitioning2-182.4.2.2Virtual Column-Based Partitioning2-19Indexing on Partitioned Tables2-192.5.1Deciding on the Type of Partitioned Index to Use2-202.5.2Local Partitioned Indexes2-202.5.3Global Partitioned Indexes2-212.5.3.1Global Range Partitioned Indexes2-222.5.3.2Global Hash Partitioned Indexes2-222.5.3.3Maintenance of Global Partitioned Indexes2-222.5.4Global Nonpartitioned Indexes2-232.5.5Miscellaneous Information about Creating Indexes on Partitioned Tables2-242.5.6Partial Indexes for Partitioned Tables2-24iv
2.5.73Partitioned Indexes on Composite Partitions2-25Partitioning for Availability, Manageability, and Performance3.13.2Partition Pruning3.1.1Benefits of Partition Pruning3-13.1.2Information That Can Be Used for Partition Pruning3-23.1.3How to Identify Whether Partition Pruning Has Been Used3-33.1.4Static Partition Pruning3-33.1.5Dynamic Partition Pruning3-43.1.5.1Dynamic Pruning with Bind Variables3-43.1.5.2Dynamic Pruning with Subqueries3-53.1.5.3Dynamic Pruning with Star Transformation3-63.1.5.4Dynamic Pruning with Nested Loop Joins3-73.1.6Partition Pruning with Zone Maps3-83.1.7Partition Pruning Tips3-93.1.7.1Data Type Conversions3-103.1.7.2Function Calls3-123.1.7.3Collection Tables3-13Partition-Wise Operations3.2.1Full Partition-Wise Joins3-143-143.2.1.1Querying a Full Partition-Wise Join3-153.2.1.2Full Partition-Wise Joins: Single-Level - Single-Level3-153.2.1.3Full Partition-Wise Joins: Composite - Single-Level3-173.2.1.4Full Partition-Wise Joins: Composite - Composite3-193.2.23.33-1Partial Partition-Wise Joins3-203.2.2.1Partial Partition-Wise Joins: Single-Level Partitioning3-203.2.2.2Partial Partition-Wise Joins: Composite3-22Index Partitioning3.3.1Local Partitioned Indexes3-243-253.3.1.1Local Prefixed Indexes3-263.3.1.2Local Nonprefixed Indexes3-263.3.2Global Partitioned Indexes3-273.3.2.1Prefixed and Nonprefixed Global Partitioned Indexes3-273.3.2.2Management of Global Partitioned Indexes3-283.3.3Summary of Partitioned Index Types3-283.3.4The Importance of Nonprefixed Indexes3-293.3.5Performance Implications of Prefixed and Nonprefixed Indexes3-293.3.6Advanced Index Compression With Partitioned Indexes3-303.3.7Guidelines for Partitioning Indexes3-313.3.8Physical Attributes of Index Partitions3-32v
3.43.54Partitioning and Table Compression3-333.4.1Table Compression and Bitmap Indexes3-333.4.2Example of Table Compression and Partitioning3-34Recommendations for Choosing a Partitioning Strategy3-353.5.1When to Use Range or Interval Partitioning3-353.5.2When to Use Hash Partitioning3-373.5.3When to Use List Partitioning3-383.5.4When to Use Composite Partitioning3-393.5.4.1When to Use Composite Range-Hash Partitioning3-393.5.4.2When to Use Composite Range-List Partitioning3-403.5.4.3When to Use Composite Range-Range Partitioning3-413.5.4.4When to Use Composite List-Hash Partitioning3-423.5.4.5When to Use Composite List-List Partitioning3-423.5.4.6When to Use Composite List-Range Partitioning3-433.5.5When to Use Interval Partitioning3-443.5.6When to Use Reference Partitioning3-453.5.7When to Partition on Virtual Columns3-453.5.8Considerations When Using Read-Only Tablespaces3-46Partition Administration4.1Specifying Partitioning When Creating Tables and Indexes4.1.14-1About Creating Range-Partitioned Tables and Global Indexes4-34.1.1.1Creating a Range-Partitioned Table4-34.1.1.2Creating a Range-Partitioned Table With More Complexity4-44.1.1.3Creating a Range-Partitioned Global Index4-54.1.2Creating Range-Interval-Partitioned Tables4-54.1.3About Creating Hash Partitioned Tables and Global Indexes4-64.1.3.1Creating a Hash Partitioned Table4-64.1.3.2Creating a Hash Partitioned Global Index4-74.1.4About Creating List-Partitioned Tables4-84.1.4.1Creating a List-Partitioned Table4-84.1.4.2Creating a List-Partitioned Table With a Default Partition4-94.1.4.3Creating an Automatic List-Partitioned Table4-104.1.4.4Creating a Multi-column List-Partitioned Table4-124.1.5Creating Reference-Partitioned Tables4-144.1.6Creating Interval-Reference Partitioned Tables4-154.1.7Creating a Table Using In-Memory Column Store With Partitioning4-164.1.8Creating a Table with Read-Only Partitions or Subpartitions4-174.1.9Creating a Partitioned External Table4-184.1.10Specifying Partitioning on Key Columns4-19vi
4.1.10.1Creating a Multicolumn Range-Partitioned Table By Date4-204.1.10.2Creating a Multicolumn Range-Partitioned Table to Enforce Equal-SizedPartitions4-214.1.11Using Virtual Column-Based Partitioning4-224.1.12Using Table Compression with Partitioned Tables4-234.1.13Using Key Compression with Partitioned Indexes4-234.1.14Specifying Partitioning with Segments4-244.1.14.1Deferred Segment Creation for Partitioning4-244.1.14.2Truncating Segments That Are Empty4-254.1.14.3Maintenance Procedures for Segment Creation on Demand4-254.1.154.2Creating Range-Partitioned Index-Organized Tables4-274.1.15.2Creating Hash Partitioned Index-Organized Tables4-274.1.15.3Creating List-Partitioned Index-Organized Tables4-284.1.16Partitioning Restrictions for Multiple Block Sizes4-284.1.17Partitioning of Collections in XMLType and Objects4-294.1.17.1Performing PMOs on Partitions that Contain Collection Tables4-304.1.17.2Partitioning of XMLIndex for Binary XML Tables4-31Specifying Composite Partitioning When Creating Tables4-314.2.1Creating Composite Hash-* Partitioned Tables4-314.2.2Creating Composite Interval-* Partitioned Tables4-324.2.2.1Creating Composite Interval-Hash Partitioned Tables4-334.2.2.2Creating Composite Interval-List Partitioned Tables4-344.2.2.3Creating Composite Interval-Range Partitioned Tables4-34Creating Composite List-* Partitioned Tables4-354.2.3.1Creating Composite List-Hash Partitioned Tables4-364.2.3.2Creating Composite List-List Partitioned Tables4-364.2.3.3Creating Composite List-Range Partitioned Tables4-374.2.4Creating Composite Range-* Partitioned Tables4-384.2.4.1About Creating Composite Range-Hash Partitioned Tables4-394.2.4.2About Creating Composite Range-List Partitioned Tables4-404.2.4.3Creating Composite Range-Range Partitioned Tables4-434.2.54.44-264.1.15.14.2.34.3Specifying Partitioning When Creating Index-Organized TablesSpecifying Subpartition Templates to Describe Composite Partitioned Tables4-454.2.5.1Specifying a Subpartition Template for a *-Hash Partitioned Table4-454.2.5.2Specifying a Subpartition Template for a *-List Partitioned Table4-46Maintenance Operations Supported on Partitions4-474.3.1Updating Indexes Automatically4-524.3.2Asynchronous Global Index Maintenance for Dropping and TruncatingPartitions4-544.3.3Modifying a Subpartition Template4-554.3.4Filtering Maintenance Operations4-55Maintenance Operations for Partitioned Tables and Indexes4-56vii
4.4.1About Adding Partitions and Subpartitions4-574.4.1.1Adding a Partition to a Range-Partitioned Table4-584.4.1.2Adding a Partition to a Hash-Partitioned Table4-584.4.1.3Adding a Partition to a List-Partitioned Table4-594.4.1.4Adding a Partition to an Interval-Partitioned Table4-594.4.1.5About Adding Partitions to a Composite *-Hash Partitioned Table4-604.4.1.6About Adding Partitions to a Composite *-List Partitioned Table4-614.4.1.7About Adding Partitions to a Composite *-Range Partitioned Table4-624.4.1.8About Adding a Partition or Subpartition to a Reference-Partitioned Table4-624.4.1.9Adding Index Partitions4-634.4.1.104.4.2Adding Multiple Partitions4-64About Coalescing Partitions and Subpartitions4-654.4.2.1Coalescing a Partition in a Hash Partitioned Table4-654.4.2.2Coalescing a Subpartition in a *-Hash Partitioned Table4-654.4.2.3Coalescing Hash Partitioned Global Indexes4-664.4.3About Dropping Partitions and Subpartitions4-664.4.3.1Dropping Table Partitions4-664.4.3.2Dropping Interval Partitions4-694.4.3.3Dropping Index Partitions4-694.4.3.4Dropping Multiple Partitions4-704.4.4About Exchanging Partitions and Subpartitions4-704.4.4.1Creating a Table for Exchange With a Partitioned Table4-724.4.4.2Exchanging a Range, Hash, or List Partition4-734.4.4.3Exchanging a Partition of an Interval Partitioned Table4-754.4.4.4Exchanging a Partition of a Reference-Partitioned Table4-754.4.4.5About Exchanging a Partition of a Table with Virtual Columns4-764.4.4.6Exchanging a Hash Partitioned Table with a *-Hash Partition4-774.4.4.7Exchanging a Subpartition of a *-Hash Partitioned Table4-774.4.4.8Exchanging a List-Partitioned Table with a *-List Partition4-784.4.4.9About Exchanging a Subpartition of a *-List Partitioned Table4-784.4.4.10Exchanging a Range-Partitioned Table with a *-Range Partition4-794.4.4.11About Exchanging a Subpartition of a *-Range Partitioned Table4-804.4.4.12About Exchanging a Partition with the Cascade Option4-804.4.5About Merging Partitions and Subpartitions4-814.4.5.1Merging Range Partitions4-824.4.5.2Merging Interval Partitions4-844.4.5.3Merging List Partitions4-854.4.5.4Merging *-Hash Partitions4-854.4.5.5About Merging *-List Partitions4-864.4.5.6About Merging *-Range Partitions4-874.4.5.7Merging Multiple Partitions4-88viii
4.4.6About Modifying Attributes of Tables, Partitions, and Subpartitions4-894.4.6.1About Modifying Default Attributes4-894.4.6.2About Modifying Real Attributes of Partitions4-904.4.7About Modifying List Partitions4-924.4.7.1About Modifying List Partitions: Adding Values4-924.4.7.2About Modifying List Partitions: Dropping Values4-934.4.8About Modifying the Partitioning Strategy4-944.4.9About Moving Partitions and Subpartitions4-964.4.9.1Moving Table Partitions4-974.4.9.2Moving Subpartitions4-984.4.9.3Moving Index Partitions4-98About Rebuilding Index Partitions4-984.4.104.4.10.1About Rebuilding Global Index Partitions4-984.4.10.2About Rebuilding Local Index Partitions4-99About Renaming Partitions and Subpartitions4-994.4.114.4.11.1Renaming a Table Partition4-1004.4.11.2Renaming a Table Subpartition4-1004.4.11.3About Renaming Index Partitions4-1004.4.12About Splitting Partitions and Subpartitions4-1014.4.12.1Splitting a Partition of a Range-Partitioned Table4-1024.4.12.2Splitting a Partition of a List-Partitioned Table4-1034.4.12.3Splitting a Partition of an Interval-Partitioned Table4-1064.4.12.4Splitting a *-Hash Partition4-1064.4.12.5Splitting Partitions in a *-List Partitioned Table4-1074.4.12.6Splitting a *-Range Partition4-1094.4.12.7Splitting Index Partitions4-1114.4.12.8Splitting into Multiple Partitions4-1114.4.12.9Fast SPLIT PARTITION and SPLIT SUBPARTITION Operations4-1124.4.13About Truncating Partitions and Subpartitions4-1134.4.13.1About Truncating a Table Partition4-1144.4.13.2Truncating Multiple Partitions4-1154.4.13.3Truncating Subpartitions4-1184.4.13.4Truncating a Partition with the Cascade Option4-1204.5About Dropping Partitioned Tables4-1204.6Changing a Nonpartitioned Table into a Partitioned Table4-1214.74.6.1Using Online Redefinition to Partition Collection Tables4-1224.6.2Converting a Non-Partitioned Table to a Partitioned Table4-124Managing Hybrid Partitioned Tables4-1254.7.1Creating Hybrid Partitioned Tables4-1254.7.2Converting to Hybrid Partitioned Tables4-1274.7.3Converting Hybrid Partitioned Tables to Internal Partitioned Tables4-128ix
4.854.7.4Using ADO With Hybrid Partitioned Tables4-1294.7.5Splitting Partitions in a Hybrid Partitioned Table4-1304.7.6Exchanging Data in Hybrid Partitioned Tables4-132Viewing Information About Partitioned Tables and Indexes4-136Managing and Maintaining Time-Based Information5.1Managing Data in Oracle Database With ILM5.1.15-2Oracle Database Manages All Types of Data5-25.1.1.2Regulatory Requirements5-35.1.1.3The Benefits of an Online Archive5-3Implementing ILM Using Oracle Database5-45.1.2.1Step 1: Define the Data Classes5-45.1.2.2Step 2: Create Storage Tiers for the Data Classes5-75.1.2.3Step 3: Create Data Access and Migration Policies5-95.1.2.4Step 4: Define and Enforce Compliance Policies5-11Implementing an ILM Strategy With Heat Map and ADO5.2.15-12Using Heat Map5-135.2.1.1Enabling and Disabling Heat Map5-135.2.1.2Displaying Heat Map Tracking Data With Views5-145.2.1.3Managing Heat Map Data With DBMS HEAT MAP Subprograms5-165.2.2Using Automatic Data Optimization5-175.2.2.1Managing Policies for Automatic Data Optimization5-175.2.2.2Creating a Table With an ILM ADO Policy5-195.2.2.3Adding ILM ADO Policies5-195.2.2.4Disabling and Deleting ILM ADO Policies5-205.2.2.5Specifying Segment-Level Compression and Storage Tiering With ADO5-215.2.2.6Specifying Row-Level Compression Tiering With ADO5-215.2.2.7Managing ILM ADO Parameters5-225.2.2.8Using PL/SQL Functions for Policy Management5-245.2.2.9Using Views to Monitor Policies for ADO5-25Limitations and Restrictions With ADO and Heat Map5-265.2.35.3About Oracle Database for ILM5.1.1.15.1.25.25-1Controlling the Validity and Visibility of Data in Oracle Database5-265.3.1Using In-Database Archiving5-275.3.2Using Temporal Validity5-295.3.3Creating a Table With Temporal Validity5-305.3.4Limitations and Restrictions With In-Database Archiving and Temporal Validity5-325.4Implementing an ILM System Manually Using Partitioning5-325.5Managing ILM Heat Map and ADO with Oracle Enterprise Manager5-355.5.1Accessing the Database Administration Menu5-36x
65.5.2Viewing Automatic Data Optimization Activity at the Tablespace Level5-365.5.3Viewing the Segment Activity Details of Any Tablespace5-375.5.4Viewing the Segment Activity Details of Any Object5-375.5.5Viewing the Segment Activity History of Any Object5-385.5.6Searching Segment Activity in Automatic Data Optimization5-385.5.7Viewing Policies for a Segment5-385.5.8Disabling Background Activity5-395.5.9Changing Execution Frequency of Background Automatic Data Optimization5-395.5.10Viewing Policy Executions In the Last 24 Hours5-405.5.11Viewing Objects Moved In Last 24 Hours5-405.5.12Viewing Policy Details5-405.5.13Viewing Objects Associated With a Policy5-415.5.14Evaluating a Policy Before Execution5-415.5.15Executing a Single Policy5-425.5.16Stopping a Policy Execution5-425.5.17Viewing Policy Execution History5-42Using Partitioning in a Data Warehouse Environment6.1What Is a Data Warehouse?6-16.2Scalability in a Data Warehouse6-16.36.2.1Bigger Databases6-26.2.2Bigger Individual Tables: More Rows in Tables6-26.2.3More Users Querying the System6-26.2.4More Complex Queries6-2Partitioning for Performance in a Data Warehouse6.3.1Partition Pruning in a Data Warehouse6-36.3.1.1Basic Partition Pruning Techniques6-36.3.1.2Advanced Partition Pruning Techniques6-46.3.2Partition-Wise Joins in a Data Warehouse6-66.3.2.1Full Partition-Wise Joins6-66.3.2.2Partial Partition-Wise Joins6-86.3.2.3Benefits of Partition-Wise Joins6-96.3.2.4Performance Considerations for Parallel Partition-Wise Joins6.3.3Indexes and Partitioned Indexes in a Data Warehouse6-106-106.3.3.1Local Partitioned Indexes6-116.3.3.2Nonpartitioned Indexes6-126.3.3.3Global Partitioned Indexes6-126.3.4Materialized Views and Partitioning in a Data Warehouse6.3.4.16.46-3Partitioned Materialized ViewsManageability in a Data Warehouse6-136-136-14xi
76.4.1Partition Exchange Load6-146.4.2Partitioning and Indexes6-156.4.3Removing Data from Tables6-156.4.4Partitioning and Data Compression6-16Using Partitioning in an Online Transaction Processing Environment7.1What Is an Online Transaction Processing System?7-17.2Performance in an Online Transaction Processing Environment7-37.37.2.1Deciding Whether to Partition Indexes7-37.2.2How to Use Partitioning on Index-Organized Tables7-4Manageability in an Online Transaction Processing Environment7-57.3.18Impact of a Partition Maintenance Operation on a Partitioned Table with LocalIndexes7-67.3.2Impact of a Partition Maintenance Operation on Global Indexes7-67.3.3Common Partition Maintenance Operations in OLTP Environments7-77.3.3.1Removing (Purging) Old Data7-77.3.3.2Moving or Merging Older Partitions to a Low-Cost Storage Tier Device7-7Using Parallel Execution8.1Parallel Execution Concepts8.1.1When to Implement Parallel Execution8-28.1.2When Not to Implement Parallel Execution8-38.1.3Fundamental Hardware Requirements8-38.1.4How Parallel Execution Works8-48.1.4.1Parallel Execution of SQL Statements8-48.1.4.2Producer/Consumer Model8-48.1.4.3Granules of Parallelism8-58.1.4.4Distribution Methods Between Producers and Consumers8-78.1.4.5How Parallel Execution Servers Communicate8.1.58-10Parallel Execution Server Pool8.1.5.18.28-18-11Processing without Enough Parallel Execution Servers8-118.1.6Balancing the Workload to Optimize Performance8-118.1.7Multiple Parallelizers8-128.1.8Parallel Execution on Oracle RAC8-13Setting the Degree of Parallelism8-148.2.1Manually Specifying the Degree of Parallelism8-148.2.2Default Degree of Parallelism8-158.2.3Automatic Degree of Parallelism8-168.2.4Determining Degree of Parallelism in Auto DOP8-168.2.5Controlling Automatic Degree of Parallelism8-17xii
8.2.68.38.4In-Memory Parallel Execution8-198-198.3.1Buffer Cache Usage in Parallel Execution8-208.3.2Automatic Big Table Caching8-20Parallel Statement Queuing8.4.18.5Adaptive ParallelismAbout Managing Parallel Statement Queuing with Oracle Database ResourceManager8-218-238.4.1.1About Managing the Order of the Parallel Statement Queue8-248.4.1.2About Limiting the Parallel Server Resources for a Consumer Group8-258.4.1.3Specifying a Parallel Statement Queue Timeout for Each ConsumerGroup8-268.4.1.4Specifying a Degree of Parallelism Limit for Consumer Groups8-268.4.1.5Critical Parallel Statement Prioritization8-268.4.1.6A Sample Scenario for Managing Statements in the Parallel Queue8-278.4.2Grouping Parallel Statements with BEGIN SQL BLOCK END SQL BLOCK8-298.4.3About Managing Parallel Statement Queuing with Hints8-30Types of Parallelism8.5.1About Parallel Queries8-318-318.5.1.1Parallel Queries on Index-Organized Tables8-328.5.1.2Nonpartitioned Index-Organized Tables8-328.5.1.3Partitioned Index-Organized Tables8-328.5.1.4Parallel Queries on Object Types8-328.5.1.5Rules for Parallelizing Queries8-338.5.2About Parallel DDL Statements8-348.5.2.1DDL Statements That Can Be Parallelized8-348.5.2.2About Using CREATE TABLE AS SELECT in Parallel8-348.5.2.3Recoverability and Parallel DDL8-358.5.2.4Space Management for Parallel DDL8-358.5.2.5Storage Space When Using Dictionary-Managed Tablespaces8-368.5.2.6Free Space and Parallel DDL8-368.5.2.7Rules for DDL Statements8-378.5.2.8Rules for CREATE TABLE AS SELECT8-388.5.3About Parallel DML Operations8-388.5.3.1When to Use Parallel DML8-398.5.3.2Enable Parallel DML Mode8-408.5.3.3Rules for UPDATE, MERGE, and DELETE8-418.5.3.4Rules for INSERT SELECT8-418.5.3.5Transaction Restrictions for Parallel DML8-428.5.3.6Rollback Segments8-438.5.3.7Recovery for Parallel DML8-438.5.3.8Space Considerations for Parallel DML8-448.5.3.9Restrictions on Parallel DML8-44xiii
8.5.3.10Data Integrity Restrictions8-458.5.3.11Trigger Restrictions8-468.5.3.12Distributed Transaction Restrictions8-468.5.3.13Examples of Distributed Transaction Parallelization8-478.5.3.14Concurrent Execution of Union All8-478.5.48.68.78-488.5.4.1Functions in Parallel Queries8-498.5.4.2Functions in Parallel DML and DDL Statements8-498.5.5About Other Types of Parallelism8-508.5.6Degree of Parallelism Rules for SQL Statements8-50About Initializing and Tuning Parameters for Parallel Execution8-528.6.1Default Parameter Settings8-538.6.2Forcing Parallel Execution for a Session8-548.6.3Tuning General Parameters for Parallel Execution8-558.6.3.1Parameters Establishing Resource Limits for Parallel Operations8-558.6.3.2Parameters Affecting Resource Consumption8-628.6.3.3Parameters Related to I/O8-67Monitoring Parallel Execution Performance8.7.18.8About Parallel Execution of Functions8-68Monitoring Parallel Execution Performance with Dynamic Performance Views8-698.7.1.1V PX BUFFER ADVICE8-698.7.1.2V PX SESSION8-698.7.1.3V PX SESSTAT8-708.7.1.4V PX PROCESS8-708.7.1.5V PX PROCESS SYSSTAT8-708.7.1.6V PQ SESSTAT8-708.7.1.7V PQ TQSTAT8-708.7.1.8V RSRC CONS GROUP HISTORY8-718.7.1.9V RSRC CONSUMER GROUP8-718.7.1.10V RSRC PLAN8-728.7.1.11V RSRC PLAN HISTORY8-728.7.1.12V RSRC SESSION INFO8-728.7.1.13V RSRCMGRMETRIC8-728.7.2Monitoring Session Statistics8-738.7.3Monitoring System Statistics8-748.7.4Monitoring Operating System Statistics8-75Tips for Tuning Parallel Execution8-758.8.1Implementing a Parallel Execution Strategy8-768.8.2Optimizing Performance by Creating and Populating Tables in Parallel8-768.8.3Using EXPLAIN PLAN to Show Parallel Operations Plans8-778.8.3.18.8.4Example: Using EXPLAIN PLAN to Show Parallel Operations8-78Additional Considerations for Parallel DML8-78xiv
8.8.4.1Parallel DML and Direct-Path Restrictions8-798.8.4.2Limitation on the Degree of Parallelism8-798.8.4.3When to Increase INITRANS8-798.8.4.4Limitation on Available Number of Transaction Free Lists for Segments8-798.8.4.5Multiple Archivers for Large Numbers of Redo Logs8-808.8.4.6Database Writer Process (DBWn) Workload8-808.8.4.7[NO]LOGGING Clause8-808.8.5Optimizing Performance by Creating Indexes in Parallel8-818.8.6Parallel DML Tips8-828.8.6.1Parallel DML Tip 1: INSERT8-828.8.6.2Parallel DML Tip 2: Direct-Path INSERT8-838.8.6.3Parallel DML Tip 3: Parallelizing INSERT, MERGE, UPDATE, andDELETE8-848.8.79Incremental Data Loading in Parallel8-858.8.7.1Optimizing Performance for Updating the Table in Parallel8-868.8.7.2Efficiently Inserting the New Rows into the Table in Parallel8-868.8.7.3Optimizing Performance by Merging in Parallel8-86Backing Up and Recovering VLDBs9.1Data Warehouses9.1.19.2Oracle Backup and Recovery9.2.19.39.4Data Warehouse CharacteristicsPhysical Database Structures Used in Recovering Data9-19-29-29-39.2.1.1Data files9-39.2.1.2Redo Logs9-39.2.1.3Control Files9-39.2.2Backup Type9-49.2.3Backup Tools9-49.2.3.1Oracle Recovery Manager (RMAN)9-59.2.3.2Oracle Data Pump9-59.2.3.3User-Managed Backups9-5Data Warehouse Backup and Recovery9-69.3.1Recovery Time Objective (RTO)9-69.3.2Recovery Point Objective (RPO)9-79.3.2.1More Data Means a Longer Backup Window9-79.3.2.2Divide and Conquer9-7The Data Warehouse Recovery Methodology9-89.4.1Best Practice 1: Use ARCHIVELOG Mode9.4.1.1Is Downtime Acceptable?9-89-99.4.2Best Practice 2: Use RMAN9-99.4.3Best Practice 3: Use Block Change Tracking9-9xv
9.4.4Best Practice 4: Use RMAN Multisection Backups9-109.4.5Best Practice 5: Leverage Read-Only Tablespaces9-109.4.6Best Practice 6: Plan for NOLOGGING Operations in Your Backup/RecoveryStrategy9-119.4.6.1Extract, Transform, and Load9-129.4.6.2The Extract, Transform, and Load Strategy9-129.4.6.3Incremental Backup9-139.4.6.4The Incremental Approach9-139.4.6.5Flashback Database and Guaranteed Restore Points9-14Best Practice 7: Not All Tablespaces Should Be Treated Equally9-149.4.710Storage Management for VLDBs10.1High Availability10.1.1Hardware-Based Mirroring10-210.1.1.1RAID 1 Mirroring10-210.1.1.2RAID 5 Mirroring10-2Mirroring Using Oracle -Based Striping10-410.2.1.1RAID 0 Striping10-410.2.1.2RAID 5 Striping10-410.2.2Striping Using Oracle ASM10-510.2.3Information Lifecycle Management10-510.2.4Partition Placement10-610.2.5Bigfile Tablespaces10-610.2.6Oracle Database File System (DBFS)10-610.3Scalability and Manageability10-710.3.1Stripe and Mirror Everything (SAME)10-710.3.2SAME and Manageability10-810.4Oracle ASM Settings Specific to VLDBs10-8GlossaryIndexxvi
List of Examples3-1Creating a table with partition pruning3-23-2Partitioned table sales range with attribute clustering and a zone map on a correlated column3-93-3Execution plan for partition pruning with zone maps3-93-4Querying with a full partition-wise join3-153-5Creating a table with range
Changes for VLDB and Partitioning in Oracle Database 19c xxiv Changes for VLDB and Partitioning in Oracle Database Release 18c xxv 1 Introduction to Very Large Databases 1.1 Introduction to Partitioning 1-1 1.2 VLDB and Partitioning 1-2 1.3 Partitioning As the Foundation for Information Lifecycle Management 1-3 1.4 Partitioning for All .
