Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documentation

What's New in Oracle Database?

Oracle Database 10g Release 2 (10.2) New Features in Data Warehousing

Oracle Database 10g Release 1 (10.1) New Features in Data Warehousing

Part I Concepts

1 Data Warehousing Concepts

What is a Data Warehouse?

Subject Oriented

Contrasting OLTP and Data Warehousing Environments

Data Warehouse Architectures

Data Warehouse Architecture (Basic)

Data Warehouse Architecture (with a Staging Area)

Data Warehouse Architecture (with a Staging Area and Data Marts)

Part II Logical Design

2 Logical Design in Data Warehouses

Logical Versus Physical Design in Data Warehouses

Creating a Logical Design

Data Warehousing Schemas

Data Warehousing Objects

Creating a New Fact Table

Dimension Tables

Typical Dimension Hierarchy

Unique Identifiers

Example of Data Warehousing Objects and Their Relationships

Part III Physical Design

3 Physical Design in Data Warehouses

Moving from Logical to Physical Design

Physical Design

Physical Design Structures

Tables and Partitioned Tables

Table Compression

Integrity Constraints

Indexes and Partitioned Indexes

Materialized Views

4 Hardware and I/O Considerations in Data Warehouses

Overview of Hardware and I/O Considerations in Data Warehouses

Configure I/O for Bandwidth not Capacity

Stripe Far and Wide

Test the I/O System Before Building the Database

Plan for Growth

Storage Management

5 Partitioning in Data Warehouses

Types of Partitioning

Partitioning Methods

Range Partitioning

Hash Partitioning

List Partitioning

Composite Partitioning

Index Partitioning

Performance Issues for Range, List, Hash, and Composite Partitioning

When to Use Range Partitioning

When to Use Hash Partitioning

When to Use List Partitioning

When to Use Composite Range-Hash Partitioning

Using Composite Range-Hash Partitioning

When to Use Composite Range-List Partitioning

Using Composite Range-List Partitioning

Partitioning and Table Compression

Table Compression and Bitmap Indexes

Example of Table Compression and Partitioning

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

Basic Partition Pruning Techniques

Advanced Partition Pruning Techniques

Partition Pruning Tips

Partition-Wise Joins

Full Partition-Wise Joins

(Composite-Hash)-Hash

(Composite-List)-List

(Composite-Composite (Hash/List Dimension)

Range-Range and List-List

Range-Composite, Composite-Composite (Range Dimension)

Partial Partition-Wise Joins

Benefits of Partition-Wise Joins

Reduction of Communications Overhead

Reduction of Memory Requirements

Performance Considerations for Parallel Partition-Wise Joins

Partition Maintenance

Partitioning and Subpartitioning Columns and Keys

Partition Bounds for Range Partitioning

Comparing Partitioning Keys with Partition Bounds

Multicolumn Partitioning Keys

Implicit Constraints Imposed by Partition Bounds

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

6 Indexes

Using Bitmap Indexes in Data Warehouses

Benefits for Data Warehousing Applications

How to Determine Candidates for Using a Bitmap Index

Bitmap Indexes and Nulls

Bitmap Indexes on Partitioned Tables

Using Bitmap Join Indexes in Data Warehouses

Four Join Models for Bitmap Join Indexes

Bitmap Join Index Restrictions and Requirements

Using B-Tree Indexes in Data Warehouses

Using Index Compression

Choosing Between Local Indexes and Global Indexes

7 Integrity Constraints

Why Integrity Constraints are Useful in a Data Warehouse

Overview of Constraint States

Typical Data Warehouse Integrity Constraints

UNIQUE Constraints in a Data Warehouse

FOREIGN KEY Constraints in a Data Warehouse

RELY Constraints

NOT NULL Constraints

Integrity Constraints and Parallelism

Integrity Constraints and Partitioning

View Constraints

8 Basic Materialized Views

Overview of Data Warehousing with Materialized Views

Materialized Views for Data Warehouses

Materialized Views for Distributed Computing

Materialized Views for Mobile Computing

The Need for Materialized Views

Components of Summary Management

Data Warehousing Terminology

Materialized View Schema Design

Schemas and Dimension Tables

Materialized View Schema Design Guidelines

Loading Data into Data Warehouses

Overview of Materialized View Management Tasks

Types of Materialized Views

Materialized Views with Aggregates

Requirements for Using Materialized Views with Aggregates

Materialized Views Containing Only Joins

Materialized Join Views FROM Clause Considerations

Nested Materialized Views

Why Use Nested Materialized Views?

Nesting Materialized Views with Joins and Aggregates

Nested Materialized View Usage Guidelines

Restrictions When Using Nested Materialized Views

Creating Materialized Views

Creating Materialized Views with Column Alias Lists

Naming Materialized Views

Storage And Table Compression

Enabling Query Rewrite

Query Rewrite Restrictions

Materialized View Restrictions

General Query Rewrite Restrictions

Refresh Options

General Restrictions on Fast Refresh

Restrictions on Fast Refresh on Materialized Views with Joins Only

Restrictions on Fast Refresh on Materialized Views with Aggregates

Restrictions on Fast Refresh on Materialized Views with UNION ALL

Achieving Refresh Goals

Refreshing Nested Materialized Views

ORDER BY Clause

Materialized View Logs

Using the FORCE Option with Materialized View Logs

Using Oracle Enterprise Manager

Using Materialized Views with NLS Parameters

Adding Comments to Materialized Views

Registering Existing Materialized Views

Choosing Indexes for Materialized Views

Dropping Materialized Views

Analyzing Materialized View Capabilities

Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure

DBMS_MVIEW.EXPLAIN_MVIEW Declarations

Using MV_CAPABILITIES_TABLE

MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details

MV_CAPABILITIES_TABLE Column Details

9 Advanced Materialized Views

Partitioning and Materialized Views

Partition Change Tracking

Join Dependent Expression

Partition Marker

Partial Rewrite

Partitioning a Materialized View

Partitioning a Prebuilt Table

Benefits of Partitioning a Materialized View

Rolling Materialized Views

Materialized Views in OLAP Environments

Partitioning Materialized Views for OLAP

Compressing Materialized Views for OLAP

Materialized Views with Set Operators

Examples of Materialized Views Using UNION ALL

Materialized Views and Models

Invalidating Materialized Views

Security Issues with Materialized Views

Querying Materialized Views with Virtual Private Database (VPD)

Using Query Rewrite with Virtual Private Database

Restrictions with Materialized Views and Virtual Private Database

Altering Materialized Views

10 Dimensions

What are Dimensions?

Creating Dimensions

Dropping and Creating Attributes with Columns

Multiple Hierarchies

Using Normalized Dimension Tables

Viewing Dimensions

Using Oracle Enterprise Manager

Using the DESCRIBE_DIMENSION Procedure

Using Dimensions with Constraints

Validating Dimensions

Altering Dimensions

Deleting Dimensions

Part IV Managing the Data Warehouse Environment

11 Overview of Extraction, Transformation, and Loading

Overview of ETL in Data Warehouses

ETL Tools for Data Warehouses

Daily Operations in Data Warehouses

Evolution of the Data Warehouse

12 Extraction in Data Warehouses

Overview of Extraction in Data Warehouses

Introduction to Extraction Methods in Data Warehouses

Logical Extraction Methods

Full Extraction

Incremental Extraction

Physical Extraction Methods

Online Extraction

Offline Extraction

Change Data Capture

Data Warehousing Extraction Examples

Extraction Using Data Files

Extracting into Flat Files Using SQL*Plus

Extracting into Flat Files Using OCI or Pro*C Programs

Exporting into Export Files Using the Export Utility

Extracting into Export Files Using External Tables

Extraction Through Distributed Operations

13 Transportation in Data Warehouses

Overview of Transportation in Data Warehouses

Introduction to Transportation Mechanisms in Data Warehouses

Transportation Using Flat Files

Transportation Through Distributed Operations

Transportation Using Transportable Tablespaces

Transportable Tablespaces Example

Other Uses of Transportable Tablespaces

14 Loading and Transformation

Overview of Loading and Transformation in Data Warehouses

Transformation Flow

Multistage Data Transformation

Pipelined Data Transformation

Loading Mechanisms

Loading a Data Warehouse with SQL*Loader

Loading a Data Warehouse with External Tables

Loading a Data Warehouse with OCI and Direct-Path APIs

Loading a Data Warehouse with Export/Import

Transformation Mechanisms

Transforming Data Using SQL

CREATE TABLE ... AS SELECT And INSERT /*+APPEND*/ AS SELECT

Transforming Data Using UPDATE

Transforming Data Using MERGE

Transforming Data Using Multitable INSERT

Transforming Data Using PL/SQL

Transforming Data Using Table Functions

What is a Table Function?

Error Logging and Handling Mechanisms

Business Rule Violations

Data Rule Violations (Data Errors)

Handling Data Errors in PL/SQL

Handling Data Errors with an Error Logging Table

Loading and Transformation Scenarios

Key Lookup Scenario

Business Rule Violation Scenario

Data Error Scenarios

Pivoting Scenarios

15 Maintaining the Data Warehouse

Using Partitioning to Improve Data Warehouse Refresh

Refresh Scenarios

Scenarios for Using Partitioning for Refreshing Data Warehouses

Refresh Scenario 1

Refresh Scenario 2

Optimizing DML Operations During Refresh

Implementing an Efficient MERGE Operation

Maintaining Referential Integrity

Refreshing Materialized Views

Complete Refresh

Partition Change Tracking (PCT) Refresh

ON COMMIT Refresh

Manual Refresh Using the DBMS_MVIEW Package

Refresh Specific Materialized Views with REFRESH

Refresh All Materialized Views with REFRESH_ALL_MVIEWS

Refresh Dependent Materialized Views with REFRESH_DEPENDENT

Using Job Queues for Refresh

When Fast Refresh is Possible

Recommended Initialization Parameters for Parallelism

Monitoring a Refresh

Checking the Status of a Materialized View

Scheduling Refresh

Tips for Refreshing Materialized Views with Aggregates

Tips for Refreshing Materialized Views Without Aggregates

Tips for Refreshing Nested Materialized Views

Tips for Fast Refresh with UNION ALL

Tips After Refreshing Materialized Views

Using Materialized Views with Partitioned Tables

Fast Refresh with Partition Change Tracking

PCT Fast Refresh Scenario 1

PCT Fast Refresh Scenario 2

PCT Fast Refresh Scenario 3

Fast Refresh with CONSIDER FRESH

16 Change Data Capture

Overview of Change Data Capture

Capturing Change Data Without Change Data Capture

Capturing Change Data with Change Data Capture

Publish and Subscribe Model

Change Sources and Modes of Change Data Capture

Synchronous Change Data Capture

Asynchronous Change Data Capture

Asynchronous HotLog Mode

Asynchronous Distributed HotLog Mode

Asynchronous AutoLog Mode

Valid Combinations of Change Sources and Change Sets

Getting Information About the Change Data Capture Environment

Preparing to Publish Change Data

Creating a User to Serve As a Publisher

Granting Privileges and Roles to the Publisher

Creating a Default Tablespace for the Publisher

Password Files and Setting the REMOTE_LOGIN_PASSWORDFILE Parameter

Determining the Mode in Which to Capture Data

Setting Initialization Parameters for Change Data Capture Publishing

Initialization Parameters for Synchronous Publishing

Initialization Parameters for Asynchronous HotLog Publishing

Initialization Parameters for Asynchronous Distributed HotLog Publishing

Initialization Parameters for Asynchronous AutoLog Publishing

Adjusting Initialization Parameter Values When Oracle Streams Values Change

Adjustments for HotLog and AutoLog Change Data Capture

Adjustments for Distributed HotLog Change Data Capture

Publishing Change Data

Performing Synchronous Publishing

Performing Asynchronous HotLog Publishing

Performing Asynchronous Distributed HotLog Publishing

Performing Asynchronous AutoLog Publishing

Subscribing to Change Data

Managing Published Data

Managing Asynchronous Change Sources

Enabling And Disabling Asynchronous Distributed HotLog Change Sources

Managing Asynchronous Change Sets

Creating Asynchronous Change Sets with Starting and Ending Dates

Enabling and Disabling Asynchronous Change Sets

Stopping Capture on DDL for Asynchronous Change Sets

Recovering from Errors Returned on Asynchronous Change Sets

Managing Change Tables

Creating Change Tables

Understanding Change Table Control Columns

Understanding TARGET_COLMAP$ and SOURCE_COLMAP$ Values

Controlling Subscriber Access to Change Tables

Purging Change Tables of Unneeded Data

Dropping Change Tables

Exporting and Importing Change Data Capture Objects Using Oracle Data Pump

Restrictions on Using Oracle Data Pump with Change Data Capture

Examples of Oracle Data Pump Export and Import Commands

Publisher Considerations for Exporting and Importing Change Tables

Re-Creating AutoLog Change Data Capture Objects After an Import Operation

Impact on Subscriptions When the Publisher Makes Changes

Considerations for Synchronous Change Data Capture

Restriction on Direct-Path INSERT

Limitation on Restoring Source Tables from the Recycle Bin

Considerations for Asynchronous Change Data Capture

Asynchronous Change Data Capture and Redo Log Files

Asynchronous Change Data Capture and Supplemental Logging

Asynchronous Change Data Capture and Oracle Streams Components

Datatypes and Table Structures Supported for Asynchronous Change Data Capture

Restrictions for NOLOGGING and UNRECOVERABLE Operations

Implementation and System Configuration

Database Configuration Assistant Considerations

Summary of Supported Distributed HotLog Configurations and Restrictions

Oracle Database Releases for Source and Staging Databases

Upgrading a Distributed HotLog Change Source to Oracle Release 10.2

Hardware Platforms and Operating Systems

Requirements for Multiple Publishers on the Staging Database

Requirements for Database Links

Part V Data Warehouse Performance

17 Basic Query Rewrite

Overview of Query Rewrite

When Does Oracle Rewrite a Query?

Ensuring that Query Rewrite takes Effect

Initialization Parameters for Query Rewrite

Controlling Query Rewrite

Accuracy of Query Rewrite

Privileges for Enabling Query Rewrite

Sample Schema and Materialized Views

How to Verify Query Rewrite Occurred

Example of Query Rewrite

18 Advanced Query Rewrite

How Oracle Rewrites Queries

Cost-Based Optimization

General Query Rewrite Methods

When are Constraints and Dimensions Needed?

Checks Made by Query Rewrite

Join Compatibility Check

Data Sufficiency Check

Grouping Compatibility Check

Aggregate Computability Check

Rewrite Using Dimensions

Benefits of Using Dimensions

How to Define Dimensions

Types of Query Rewrite

Text Match Rewrite

Aggregate Computability

Aggregate Rollup

Rollup Using a Dimension

When Materialized Views Have Only a Subset of Data

Query Rewrite Definitions

Selection Categories

Examples of Query Rewrite Selection

Handling of the HAVING Clause in Query Rewrite

Query Rewrite When the Materialized View has an IN-List

Partition Change Tracking (PCT) Rewrite

PCT Rewrite Based on Range Partitioned Tables

PCT Rewrite Based on Range-List Partitioned Tables

PCT Rewrite Based on List Partitioned Tables

PCT Rewrite and PMARKER

PCT Rewrite Using Rowid as PMARKER

Multiple Materialized Views

Other Query Rewrite Considerations

Query Rewrite Using Nested Materialized Views

Query Rewrite in the Presence of Inline Views

Query Rewrite in the Presence of Duplicate Tables

Query Rewrite Using Date Folding

Query Rewrite Using View Constraints

View Constraints Restrictions

Query Rewrite Using Set Operator Materialized Views

UNION ALL Marker

Query Rewrite in the Presence of Grouping Sets

Query Rewrite When Using GROUP BY Extensions

Hint for Queries with Extended GROUP BY

Query Rewrite in the Presence of Window Functions

Query Rewrite and Expression Matching

Query Rewrite Using Partially Stale Materialized Views

Cursor Sharing and Bind Variables

Handling Expressions in Query Rewrite

Advanced Query Rewrite Using Equivalences

Verifying that Query Rewrite has Occurred

Using EXPLAIN PLAN with Query Rewrite

Using the EXPLAIN_REWRITE Procedure with Query Rewrite

DBMS_MVIEW.EXPLAIN_REWRITE Syntax

Using REWRITE_TABLE

EXPLAIN_REWRITE Benefit Statistics

Support for Query Text Larger than 32KB in EXPLAIN_REWRITE

EXPLAIN_REWRITE and Multiple Materialized Views

EXPLAIN_REWRITE Output

Design Considerations for Improving Query Rewrite Capabilities

Query Rewrite Considerations: Constraints

Query Rewrite Considerations: Dimensions

Query Rewrite Considerations: Outer Joins

Query Rewrite Considerations: Text Match

Query Rewrite Considerations: Aggregates

Query Rewrite Considerations: Grouping Conditions

Query Rewrite Considerations: Expression Matching

Query Rewrite Considerations: Date Folding

Query Rewrite Considerations: Statistics

Query Rewrite Considerations: Hints

REWRITE and NOREWRITE Hints

REWRITE_OR_ERROR Hint

Multiple Materialized View Rewrite Hints

EXPAND_GSET_TO_UNION Hint

19 Schema Modeling Techniques

Schemas in Data Warehouses

Third Normal Form

Optimizing Third Normal Form Queries

Snowflake Schemas

Optimizing Star Queries

Tuning Star Queries

Using Star Transformation

Star Transformation with a Bitmap Index

Execution Plan for a Star Transformation with a Bitmap Index

Star Transformation with a Bitmap Join Index

Execution Plan for a Star Transformation with a Bitmap Join Index

How Oracle Chooses to Use Star Transformation

Star Transformation Restrictions

20 SQL for Aggregation in Data Warehouses

Overview of SQL for Aggregation in Data Warehouses

Analyzing Across Multiple Dimensions

Optimized Performance

An Aggregate Scenario

Interpreting NULLs in Examples

ROLLUP Extension to GROUP BY

When to Use ROLLUP

CUBE Extension to GROUP BY

When to Use CUBE

Calculating Subtotals Without CUBE

GROUPING Functions

GROUPING Function

When to Use GROUPING

GROUPING_ID Function

GROUP_ID Function

GROUPING SETS Expression

GROUPING SETS Syntax

Composite Columns

Concatenated Groupings

Concatenated Groupings and Hierarchical Data Cubes

Considerations when Using Aggregation

Hierarchy Handling in ROLLUP and CUBE

Column Capacity in ROLLUP and CUBE

HAVING Clause Used with GROUP BY Extensions

ORDER BY Clause Used with GROUP BY Extensions

Using Other Aggregate Functions with ROLLUP and CUBE

Computation Using the WITH Clause

Working with Hierarchical Cubes in SQL

Specifying Hierarchical Cubes in SQL

Querying Hierarchical Cubes in SQL

SQL for Creating Materialized Views to Store Hierarchical Cubes

Examples of Hierarchical Cube Materialized Views

21 SQL for Analysis and Reporting

Overview of SQL for Analysis and Reporting

Ranking Functions

RANK and DENSE_RANK Functions

Ranking on Multiple Expressions

RANK and DENSE_RANK Difference

Per Group Ranking

Per Cube and Rollup Group Ranking

Treatment of NULLs

Bottom N Ranking

CUME_DIST Function

PERCENT_RANK Function

ROW_NUMBER Function

Windowing Aggregate Functions

Treatment of NULLs as Input to Window Functions

Windowing Functions with Logical Offset

Centered Aggregate Function

Windowing Aggregate Functions in the Presence of Duplicates

Varying Window Size for Each Row

Windowing Aggregate Functions with Physical Offsets

FIRST_VALUE and LAST_VALUE Functions

Reporting Aggregate Functions

RATIO_TO_REPORT Function

LAG/LEAD Functions

LAG/LEAD Syntax

FIRST/LAST Functions

FIRST/LAST Syntax

FIRST/LAST As Regular Aggregates

FIRST/LAST As Reporting Aggregates

Inverse Percentile Functions

Normal Aggregate Syntax

Inverse Percentile Example Basis

As Reporting Aggregates

Inverse Percentile Restrictions

Hypothetical Rank and Distribution Functions

Hypothetical Rank and Distribution Syntax

Linear Regression Functions

REGR_COUNT Function

REGR_AVGY and REGR_AVGX Functions

REGR_SLOPE and REGR_INTERCEPT Functions

REGR_R2 Function

REGR_SXX, REGR_SYY, and REGR_SXY Functions

Linear Regression Statistics Examples

Sample Linear Regression Calculation

Frequent Itemsets

Other Statistical Functions

Descriptive Statistics

Hypothesis Testing - Parametric Tests

Crosstab Statistics

Hypothesis Testing - Non-Parametric Tests

Non-Parametric Correlation

WIDTH_BUCKET Function

WIDTH_BUCKET Syntax

User-Defined Aggregate Functions

CASE Expressions

Creating Histograms With User-Defined Buckets

Data Densification for Reporting

Partition Join Syntax

Sample of Sparse Data

Filling Gaps in Data

Filling Gaps in Two Dimensions

Filling Gaps in an Inventory Table

Computing Data Values to Fill Gaps

Time Series Calculations on Densified Data

Period-to-Period Comparison for One Time Level: Example

Period-to-Period Comparison for Multiple Time Levels: Example

Creating a Custom Member in a Dimension: Example

22 SQL for Modeling

Overview of SQL Modeling

How Data is Processed in a SQL Model

Why Use SQL Modeling?

SQL Modeling Capabilities

Basic Topics in SQL Modeling

MODEL Clause Syntax

Keywords in SQL Modeling

Assigning Values and Null Handling

Calculation Definition

Cell Referencing

Symbolic Dimension References

Positional Dimension References

Single Cell References

Multi-Cell References on the Right Side

Multi-Cell References on the Left Side

Use of the CV Function

Use of the ANY Wildcard

Nested Cell References

Order of Evaluation of Rules

Global and Local Keywords for Rules

UPDATE, UPSERT, and UPSERT ALL Behavior

UPDATE Behavior

UPSERT Behavior

UPSERT ALL Behavior

Treatment of NULLs and Missing Cells

Distinguishing Missing Cells from NULLs

Use Defaults for Missing Cells and NULLs

Using NULLs in a Cell Reference

Reference Models

Advanced Topics in SQL Modeling

Evaluation of Formulas with FOR Loops

Iterative Models

Rule Dependency in AUTOMATIC ORDER Models

Analytic Functions

Unique Dimensions Versus Unique Single References

Rules and Restrictions when Using SQL for Modeling

Performance Considerations with SQL Modeling

Parallel Execution

Aggregate Computation

Using EXPLAIN PLAN to Understand Model Queries

Using ORDERED FAST: Example

Using ORDERED: Example

Using ACYCLIC FAST: Example

Using ACYCLIC: Example

Using CYCLIC: Example

Examples of SQL Modeling

23 OLAP and Data Mining

OLAP and Data Mining Comparison

Benefits of OLAP and RDBMS Integration

Backup and Recovery

The OLAP Option

Tools for Querying OLAP Data Stores

Tools for Developing OLAP Applications

Tools for Administration

Oracle Data Mining Overview

Benefits of Data Mining in the Database

Data Mining Functionality

Oracle Data Mining Interfaces

Programmatic Interfaces

Graphical User Interface

Data Mining in the Life Sciences

Oracle Data Mining Documentation

24 Advanced Business Intelligence Queries

Examples of Business Intelligence Queries

25 Using Parallel Execution

Introduction to Parallel Execution Tuning

When to Implement Parallel Execution

When Not to Implement Parallel Execution

Operations That Can Be Parallelized

How Parallel Execution Works

Degree of Parallelism

The Parallel Execution Server Pool

Variations in the Number of Parallel Execution Servers

Processing Without Enough Parallel Execution Servers

How Parallel Execution Servers Communicate

Parallelizing SQL Statements

Dividing Work Among Parallel Execution Servers

Parallelism Between Operations

Producer Operations

Granules of Parallelism

Block Range Granules

Partition Granules

Types of Parallelism

Parallel Queries on Index-Organized Tables

Nonpartitioned Index-Organized Tables

Partitioned Index-Organized Tables

Parallel Queries on Object Types

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

Advantages of Parallel DML over Manual Parallelism

When to Use Parallel DML

Enabling Parallel DML

Transaction Restrictions for Parallel DML

Rollback Segments

Recovery for Parallel DML

Space Considerations for Parallel DML

Lock and Enqueue Resources 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

Initializing and Tuning Parameters for Parallel Execution

Using Default Parameter Settings

Setting the Degree of Parallelism for Parallel Execution

How Oracle Determines the Degree of Parallelism for Operations

Hints and Degree of Parallelism

Table and Index Definitions

Default Degree of Parallelism

Adaptive Multiuser Algorithm

Minimum Number of Parallel Execution Servers

Limiting the Number of Available Instances

Balancing the Workload

Parallelization Rules for SQL Statements

Rules for Parallelizing Queries

Rules for UPDATE, MERGE, and DELETE

Rules for INSERT ... SELECT

Rules for DDL Statements

Rules for [CREATE | REBUILD] INDEX or [MOVE | SPLIT] PARTITION

Rules for CREATE TABLE AS SELECT

Summary of Parallelization Rules

Enabling Parallelism for Tables and Queries

Degree of Parallelism and Adaptive Multiuser: How They Interact

How the Adaptive Multiuser Algorithm Works

Forcing Parallel Execution for a Session

Controlling Performance with the Degree of Parallelism

Tuning General Parameters for Parallel Execution

Parameters Establishing Resource Limits for Parallel Operations

PARALLEL_MAX_SERVERS

Increasing the Number of Concurrent Users

Limiting the Number of Resources for a User

PARALLEL_MIN_SERVERS

SHARED_POOL_SIZE

Computing Additional Memory Requirements for Message Buffers

Adjusting Memory After Processing Begins

PARALLEL_MIN_PERCENT

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_FILE_MULTIBLOCK_READ_COUNT

DISK_ASYNCH_IO and TAPE_ASYNCH_IO

Monitoring and Diagnosing Parallel Execution Performance

Is There Regression?

Is There a Plan Change?

Is There a Parallel Plan?

Is There a Serial Plan?

Is There Parallel Execution?

Is the Workload Evenly Distributed?

Monitoring Parallel Execution Performance with Dynamic Performance Views

V$PX_BUFFER_ADVICE

V$PX_PROCESS_SYSSTAT

V$SESSTAT and V$SYSSTAT

Monitoring Session Statistics

Monitoring System Statistics

Monitoring Operating System Statistics

Affinity and Parallel Operations

Affinity and Parallel Queries

Affinity and Parallel DML

Miscellaneous Parallel Execution Tuning Tips

Setting Buffer Cache Size for Parallel Operations

Overriding the Default Degree of Parallelism

Rewriting SQL Statements

Creating and Populating Tables in Parallel

Creating Temporary Tablespaces for Parallel Sort and Hash Join

Size of Temporary Extents

Executing Parallel SQL Statements

Using EXPLAIN PLAN to Show Parallel Operations Plans

Additional Considerations for Parallel DML

PDML and Direct-Path Restrictions

Limitation on the Degree of Parallelism

Using Local and Global Striping

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

Using Hints with Query Optimization

FIRST_ROWS(n) Hint

Enabling Dynamic Sampling

Glossary

Index