Skip Headers

Table of Contents Image Oracle9i Data Warehousing Guide
Release 2 (9.2)

Part Number A96520-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Index
Index

Master Index

Feedback

Go to next page

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?
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
Dimension Tables
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
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
Why Stripe the Data?
Automatic Striping
Manual Striping
Local and Global Striping
Analyzing Striping
RAID Configurations
RAID 0 (Striping)
RAID 1 (Mirroring)
RAID 0+1 (Striping and Mirroring)
Striping, Mirroring, and Media Recovery
RAID 5
The Importance of Specific Analysis

5 Parallelism and Partitioning in Data Warehouses

Overview of Parallel Execution
When to Implement Parallel Execution
Granules of Parallelism
Block Range Granules
Partition Granules
Partitioning Design Considerations
Types of Partitioning
Partitioning and Data Segment Compression
Partition Pruning
Partition-Wise Joins
Miscellaneous Partition Operations
Adding Partitions
Dropping Partitions
Exchanging Partitions
Moving Partitions
Splitting and Merging Partitions
Truncating Partitions
Coalescing Partitions

6 Indexes

Bitmap Indexes
Bitmap Join Indexes
B-tree Indexes
Local Indexes Versus 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
Integrity Constraints and Parallelism
Integrity Constraints and Partitioning
View Constraints

8 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
Loading Data
Overview of Materialized View Management Tasks
Types of Materialized Views
Materialized Views with Aggregates
Materialized Views Containing Only Joins
Nested Materialized Views
Creating Materialized Views
Naming Materialized Views
Storage And Data Segment Compression
Build Methods
Enabling Query Rewrite
Query Rewrite Restrictions
Refresh Options
ORDER BY Clause
Materialized View Logs
Using Oracle Enterprise Manager
Using Materialized Views with NLS Parameters
Registering Existing Materialized Views
Partitioning and Materialized Views
Partition Change Tracking
Partitioning a Materialized View
Partitioning a Prebuilt Table
Rolling Materialized Views
Materialized Views in OLAP Environments
OLAP Cubes
Specifying OLAP Cubes in SQL
Querying OLAP Cubes in SQL
Partitioning Materialized Views for OLAP
Compressing Materialized Views for OLAP
Materialized Views with Set Operators
Choosing Indexes for Materialized Views
Invalidating Materialized Views
Security Issues with Materialized Views
Altering Materialized Views
Dropping Materialized Views
Analyzing Materialized View Capabilities
Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure
MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details
MV_CAPABILITIES_TABLE Column Details

9 Dimensions

What are Dimensions?
Creating Dimensions
Multiple Hierarchies
Using Normalized Dimension Tables
Viewing Dimensions
Using The DEMO_DIM Package
Using Oracle Enterprise Manager
Using Dimensions with Constraints
Validating Dimensions
Altering Dimensions
Deleting Dimensions
Using the Dimension Wizard
Managing the Dimension Object
Creating a Dimension

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

Overview of the Summary Advisor in the DBMS_OLAP Package
Using the Summary Advisor
Identifier Numbers
Workload Management
Loading a User-Defined Workload
Loading a Trace Workload
Loading a SQL Cache Workload
Validating a Workload
Removing a Workload
Using Filters with the Summary Advisor
Removing a Filter
Recommending Materialized Views
SQL Script Generation
Summary Data Report
When Recommendations are No Longer Required
Stopping the Recommendation Process
Summary Advisor Sample Sessions
Summary Advisor and Missing Statistics
Summary Advisor Privileges and ORA-30446
Estimating Materialized View Size
ESTIMATE_MVIEW_SIZE Parameters
Is a Materialized View Being Used?
DBMS_OLAP.EVALUATE_MVIEW_STRATEGY Procedure
Summary Advisor Wizard
Summary Advisor Steps

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 Function
When to Use GROUPING
GROUPING_ID Function
GROUP_ID Function
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
RANK and DENSE_RANK
Top N Ranking
Bottom N Ranking
CUME_DIST
PERCENT_RANK
NTILE
ROW_NUMBER
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

Glossary

Index


Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Index
Index

Master Index

Feedback