Oracle9i Data Warehousing Guide -- Contents

Title and Copyright Information

Send Us Your Comments

Preface

What's New in Data Warehousing?

Part I Concepts

1 Data Warehousing Concepts

What is a Data Warehouse?

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

Example of Data Warehousing Objects and Their Relationships

Part III Physical Design

Part IV Managing the Warehouse Environment

10 Overview of Extraction, Transformation, and Loading

Overview of ETL

ETL Tools

Daily Operations

Evolution of the Data Warehouse

11 Extraction in Data Warehouses

Overview of Extraction in Data Warehouses

Introduction to Extraction Methods in Data Warehouses

Logical Extraction Methods

Physical Extraction Methods

Change Data Capture

Data Warehousing Extraction Examples

Extraction Using Data Files

Extraction Via Distributed Operations

12 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

13 Loading and Transformation

Overview of Loading and Transformation in Data Warehouses

Transformation Flow

Loading Mechanisms

SQL*Loader

External Tables

OCI and Direct-Path APIs

Export/Import

Transformation Mechanisms

Transformation Using SQL

Transformation Using PL/SQL

Transformation Using Table Functions

Loading and Transformation Scenarios

Parallel Load Scenario

Key Lookup Scenario

Exception Handling Scenario

Pivoting Scenarios

14 Maintaining the Data Warehouse

Using Partitioning to Improve Data Warehouse Refresh

Refresh Scenarios

Scenarios for Using Partitioning for Refreshing Data Warehouses

Optimizing DML Operations During Refresh

Implementing an Efficient MERGE Operation

Maintaining Referential Integrity

Purging Data

Refreshing Materialized Views

Complete Refresh

Fast 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 Refresh is Possible

Recommended Initialization Parameters for Parallelism

Monitoring a Refresh

Checking the Status of a Materialized View

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

Fast Refresh with CONSIDER FRESH

15 Change Data Capture

About Change Data Capture

Publish and Subscribe Model

Example of a Change Data Capture System

Components and Terminology for Synchronous Change Data Capture

Installation and Implementation

Change Data Capture Restriction on Direct-Path INSERT

Security

Columns in a Change Table

Change Data Capture Views

Synchronous Mode of Data Capture

Publishing Change Data

Step 1: Decide which Oracle Instance will be the Source System

Step 2: Create the Change Tables that will Contain the Changes

Managing Change Tables and Subscriptions

Subscribing to Change Data

Steps Required to Subscribe to Change Data

What Happens to Subscriptions when the Publisher Makes Changes

Export and Import Considerations

16 Summary Advisor

Part V Warehouse Performance

17 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

18 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 SETS Expression

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

19 SQL for Analysis in Data Warehouses

Overview of SQL for Analysis in Data Warehouses

Ranking Functions

Windowing Aggregate Functions

Treatment of NULLs as Input to Window Functions

Windowing Functions with Logical Offset

Cumulative Aggregate Function Example

Moving Aggregate Function Example

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

Reporting Aggregate Functions

Reporting Aggregate Example

RATIO_TO_REPORT

LAG/LEAD Functions

LAG/LEAD Syntax

FIRST/LAST Functions

FIRST/LAST Syntax

FIRST/LAST As Regular Aggregates

FIRST/LAST As Reporting Aggregates

Linear Regression Functions

REGR_COUNT

REGR_AVGY and REGR_AVGX

REGR_SLOPE and REGR_INTERCEPT

REGR_R2

REGR_SXX, REGR_SYY, and REGR_SXY

Linear Regression Statistics Examples

Sample Linear Regression Calculation

Inverse Percentile Functions

Normal Aggregate Syntax

Inverse Percentile Restrictions

Hypothetical Rank and Distribution Functions

Hypothetical Rank and Distribution Syntax

WIDTH_BUCKET Function

WIDTH_BUCKET Syntax

User-Defined Aggregate Functions

CASE Expressions

CASE Example

Creating Histograms With User-Defined Buckets

20 OLAP and Data Mining

OLAP

Benefits of OLAP and RDBMS Integration

Data Mining

Enabling Data Mining Applications

Predictions and Insights

Mining Within the Database Architecture

Java API

21 Using Parallel Execution

Introduction to Parallel Execution Tuning

When to Implement Parallel Execution

Operations That Can Be Parallelized

The Parallel Execution Server Pool

How Parallel Execution Servers Communicate

Parallelizing SQL Statements

Types of Parallelism

Parallel Query

Parallel DDL

Parallel DML

Parallel Execution of Functions

Other Types of Parallelism

Initializing and Tuning Parameters for Parallel Execution

Selecting Automated or Manual Tuning of Parallel Execution

Using Automatically Derived Parameter Settings

Setting the Degree of Parallelism

How Oracle Determines the Degree of Parallelism for Operations

Balancing the Workload

Parallelization Rules for SQL Statements

Enabling Parallelism for Tables and Queries

Degree of Parallelism and Adaptive Multiuser: How They Interact

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

Parameters Affecting Resource Consumption

Parameters Related to I/O

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

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

Executing Parallel SQL Statements

Using EXPLAIN PLAN to Show Parallel Operations Plans

Additional Considerations for Parallel DML

Creating Indexes in Parallel

Parallel DML Tips

Incremental Data Loading in Parallel

Using Hints with Cost-Based Optimization

FIRST_ROWS(n) Hint

Enabling Dynamic Statistic Sampling

22 Query Rewrite

Overview of Query Rewrite

Cost-Based Rewrite

When Does Oracle Rewrite a Query?

Enabling Query Rewrite

Initialization Parameters for Query Rewrite

Controlling Query Rewrite

Privileges for Enabling Query Rewrite

Accuracy of Query Rewrite

How Oracle Rewrites Queries

Text Match Rewrite Methods

General Query Rewrite Methods

When are Constraints and Dimensions Needed?

Special Cases for Query Rewrite

Query Rewrite Using Partially Stale Materialized Views

Query Rewrite Using Complex Materialized Views

Query Rewrite Using Nested Materialized Views

Query Rewrite When Using GROUP BY Extensions

Did Query Rewrite Occur?

Explain Plan

DBMS_MVIEW.EXPLAIN_REWRITE Procedure

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