Skip Headers
Oracle® Database Data Warehousing Guide
10g Release 2 (10.2)

Part Number B14223-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to next page
Next
View PDF

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience
Documentation Accessibility
Organization
Related Documentation
Conventions

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
Integrated
Nonvolatile
Time Variant
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
Star Schemas
Other Schemas
Data Warehousing Objects
Fact Tables
Creating a New Fact Table
Dimension Tables
Hierarchies
Typical Dimension Hierarchy
Unique Identifiers
Relationships
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
Tablespaces
Tables and Partitioned Tables
Table Compression
Views
Integrity Constraints
Indexes and Partitioned Indexes
Materialized Views
Dimensions

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
Use Redundancy
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
Hash-Hash
Hash-Hash
(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
Hash-List
Composite
Range
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
MAXVALUE
Nulls
DATE Datatypes
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
Cardinality
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
Build Methods
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
Partition Key
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
OLAP Cubes
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
Timestamps
Partitioning
Triggers
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
Purging Data
Refreshing Materialized Views
Complete Refresh
Fast 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
Publisher
Subscribers
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
Change Sets
Valid Combinations of Change Sources and Change Sets
Change Tables
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
Join Back
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
Using a Varray
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
Star Schemas
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
ROLLUP Syntax
Partial Rollup
CUBE Extension to GROUP BY
When to Use CUBE
CUBE Syntax
Partial 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 Order
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
NTILE 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
Linear Algebra
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
Base Schema
MODEL Clause Syntax
Keywords in SQL Modeling
Assigning Values and Null Handling
Calculation Definition
Cell Referencing
Symbolic Dimension References
Positional Dimension References
Rules
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
FOR Loops
Evaluation of Formulas with FOR Loops
Iterative Models
Rule Dependency in AUTOMATIC ORDER Models
Ordered Rules
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
OLAP Overview
Benefits of OLAP and RDBMS Integration
Scalability
Availability
Manageability
Backup and Recovery
Security
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 Query
Parallel Queries on Index-Organized Tables
Nonpartitioned Index-Organized Tables
Partitioned Index-Organized Tables
Parallel Queries on Object Types
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
Parallel DML
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_CACHE_SIZE
DB_BLOCK_SIZE
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_SESSION
V$PX_SESSTAT
V$PX_PROCESS
V$PX_PROCESS_SYSSTAT
V$PQ_SESSTAT
V$FILESTAT
V$PARAMETER
V$PQ_TQSTAT
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