Go to main content
1/37
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle Database Data Warehousing Guide
Changes in Oracle Database 12
c
Release 2 (12.2.0.1)
Changes in Oracle Database 12
c
Release 1 (12.1.0.2)
New Features
Changes in Oracle Database 12
c
Release 1 (12.1.0.1)
New Features
Desupported Features
Part I Data Warehouse - Fundamentals
1
Introduction to Data Warehousing Concepts
1.1
What Is a Data Warehouse?
1.1.1
Key Characteristics of a Data Warehouse
1.2
Contrasting OLTP and Data Warehousing Environments
1.3
Common Data Warehouse Tasks
1.4
Data Warehouse Architectures
1.4.1
Data Warehouse Architecture: Basic
1.4.2
Data Warehouse Architecture: with a Staging Area
1.4.3
Data Warehouse Architecture: with a Staging Area and Data Marts
2
Data Warehousing Logical Design
2.1
Logical Versus Physical Design in Data Warehouses
2.2
Creating a Logical Design
2.2.1
What is a Schema?
2.3
About Third Normal Form Schemas
2.3.1
About Normalization
2.3.2
Design Concepts for 3NF Schemas
2.3.2.1
Identifying Candidate Primary Keys
2.3.2.2
Foreign Key Relationships and Referential Integrity Constraints
2.3.2.3
Denormalization
2.4
About Star Schemas
2.4.1
About Facts and Dimensions in Star Schemas
2.4.1.1
About Fact Tables in Data Warehouses
2.4.1.2
About Dimension Tables in Data Warehouses
2.4.2
Design Concepts in Star Schemas
2.4.3
About Snowflake Schemas
2.5
Improved Analytics Using the In-Memory Column Store
2.5.1
About Improving Query Performance Using In-Memory Expressions
2.5.2
About Using In-Memory Virtual Columns to Improve Query Performance
2.5.3
About In-Memory Column Store and Automatic Data Optimization
2.6
Automatic Big Table Caching to Improve the Performance of In-Memory Parallel Queries
3
Data Warehousing Physical Design
3.1
Moving from Logical to Physical Design
3.2
About Physical Design
3.2.1
Physical Design Structures
3.2.1.1
About Tablespaces in Data Warehouses
3.2.1.2
About Partitioning in Data Warehouses
3.2.1.2.1
Basic Partitioning Strategies Used in Data Warehouses
3.2.1.3
Index Partitioning in Data Warehouses
3.2.1.4
About Partitioning for Manageability
3.2.1.5
About Partitioning for Performance
3.2.1.6
About Partitioning for Availability
3.2.2
About Views in Data Warehouses
3.2.3
About Integrity Constraints in Data Warehouses
3.2.4
About Indexes and Partitioned Indexes in Data Warehouses
3.2.5
About Materialized Views in Data Warehouses
3.2.6
About Dimensions in Data Warehouses
3.2.6.1
About Dimension Hierarchies
3.2.6.1.1
About Levels
3.2.6.1.2
About Level Relationships
3.2.6.2
Typical Dimension Hierarchy
4
Data Warehousing Optimizations and Techniques
4.1
Using Indexes in Data Warehouses
4.1.1
About Using Bitmap Indexes in Data Warehouses
4.1.1.1
About Bitmap Indexes and Nulls
4.1.1.2
About Bitmap Indexes on Partitioned Tables
4.1.2
Benefits of Indexes for Data Warehousing Applications
4.1.3
About Cardinality and Bitmap Indexes
4.1.4
How to Determine Candidates for Using a Bitmap Index
4.1.5
Using Bitmap Join Indexes in Data Warehouses
4.1.5.1
Four Join Models for Bitmap Join Indexes in Data Warehouses
4.1.5.2
Bitmap Join Index Restrictions and Requirements
4.1.6
Using B-Tree Indexes in Data Warehouses
4.1.7
Using Index Compression
4.1.8
Choosing Between Local Indexes and Global Indexes
4.2
Using Integrity Constraints in a Data Warehouse
4.2.1
Overview of Constraint States
4.2.2
Typical Data Warehouse Integrity Constraints
4.2.2.1
UNIQUE Constraints in a Data Warehouse
4.2.2.2
FOREIGN KEY Constraints in a Data Warehouse
4.2.2.3
RELY Constraints in a Data Warehouse
4.2.2.4
NOT NULL Constraints in a Data Warehouse
4.2.2.5
Integrity Constraints and Parallelism in a Data Warehouse
4.2.2.6
Integrity Constraints and Partitioning in a Data Warehouse
4.2.2.7
View Constraints in a Data Warehouse
4.3
About Parallel Execution in Data Warehouses
4.3.1
Why Use Parallel Execution?
4.3.1.1
When to Implement Parallel Execution
4.3.1.2
When Not to Implement Parallel Execution
4.3.2
Automatic Degree of Parallelism and Statement Queuing
4.3.3
About In-Memory Parallel Execution in Data Warehouses
4.4
About Optimizing Storage Requirements in Data Warehouses
4.4.1
Using Data Compression to Improve Storage in Data Warehouses
4.5
Optimizing Star Queries and 3NF Schemas
4.5.1
Optimizing Star Queries
4.5.1.1
Tuning Star Queries
4.5.2
Using Star Transformation
4.5.2.1
Star Transformation with a Bitmap Index
4.5.2.2
Execution Plan for a Star Transformation with a Bitmap Index
4.5.2.3
Star Transformation with a Bitmap Join Index
4.5.2.4
Execution Plan for a Star Transformation with a Bitmap Join Index
4.5.2.5
How Oracle Chooses to Use Star Transformation
4.5.2.6
Star Transformation Restrictions
4.5.3
Optimizing Third Normal Form Schemas
4.5.3.1
3NF Schemas: Partitioning
4.5.3.1.1
Partitioning for Manageability
4.5.3.1.2
Partitioning for Easier Data Access
4.5.3.1.3
Partitioning for Join Performance
4.5.3.2
3NF Schemas: Parallel Query Execution
4.5.3.2.1
Whether or Not to Use Cross Instance Parallel Execution in Oracle RAC
4.5.4
Optimizing Star Queries Using VECTOR GROUP BY Aggregation
4.6
About Approximate Query Processing
4.6.1
Running Queries Containing Exact Functions Using SQL Functions that Return Approximate Values
Part II Optimizing Data Warehouses
5
Basic Materialized Views
5.1
Overview of Data Warehousing with Materialized Views
5.1.1
About Materialized Views for Data Warehouses
5.1.2
About Materialized Views for Distributed Computing
5.1.3
About Materialized Views for Mobile Computing
5.1.4
The Need for Materialized Views
5.1.5
Components of Summary Management
5.1.6
Data Warehousing Terminology
5.1.7
About Materialized View Schema Design
5.1.7.1
Schemas and Dimension Tables
5.1.7.2
Guidelines for Materialized View Schema Design
5.1.8
About Loading Data into Data Warehouses
5.1.9
Overview of Materialized View Management Tasks
5.2
Types of Materialized Views
5.2.1
About Materialized Views with Aggregates
5.2.1.1
Requirements for Using Materialized Views with Aggregates
5.2.2
About Materialized Views Containing Only Joins
5.2.2.1
Materialized Join Views FROM Clause Considerations
5.2.3
About Nested Materialized Views
5.2.3.1
Why Use Nested Materialized Views?
5.2.3.2
About Nesting Materialized Views with Joins and Aggregates
5.2.3.3
Nested Materialized View Usage Guidelines
5.2.3.4
Restrictions When Using Nested Materialized Views
5.3
Creating Materialized Views
5.3.1
Creating Materialized Views with Column Alias Lists
5.3.2
About Materialized Views Names
5.3.3
About Storage And Table Compression for Materialized Views
5.3.4
About Build Methods for Materialized Views
5.3.5
About Enabling Query Rewrite for Materialized Views
5.3.6
About Query Rewrite Restrictions
5.3.6.1
About Materialized View Restrictions for Query Rewrite
5.3.6.2
General Query Rewrite Restrictions
5.3.7
About Refresh Options for Materialized Views
5.3.7.1
About Refresh Modes for Materialized Views
5.3.7.2
About Types of Materialized View Refresh
5.3.7.3
About Using Trusted Constraints and Materialized View Refresh
5.3.7.4
General Restrictions on Fast Refresh
5.3.7.5
Restrictions on Fast Refresh on Materialized Views with Joins Only
5.3.7.6
Restrictions on Fast Refresh on Materialized Views with Aggregates
5.3.7.7
Restrictions on Fast Refresh on Materialized Views with UNION ALL
5.3.7.8
About Achieving Refresh Goals
5.3.7.8.1
Refreshing Materialized Views on Prebuilt Tables
5.3.7.9
Refreshing Nested Materialized Views
5.3.8
ORDER BY Clause in Materialized Views
5.3.9
Using Oracle Enterprise Manager to Create Materialized Views
5.3.10
Using Materialized Views with NLS Parameters
5.3.11
Adding Comments to Materialized Views
5.4
Creating Materialized View Logs
5.4.1
Using the FORCE Option With Materialized View Logs
5.4.2
Purging Materialized View Logs
5.5
Creating Materialized Views Based on Approximate Queries
5.6
Registering Existing Materialized Views
5.7
Choosing Indexes for Materialized Views
5.8
Dropping Materialized Views
5.9
Analyzing Materialized View Capabilities
5.9.1
Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure
5.9.1.1
DBMS_MVIEW.EXPLAIN_MVIEW Declarations
5.9.1.2
Using MV_CAPABILITIES_TABLE
5.9.1.3
MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details
5.9.1.4
MV_CAPABILITIES_TABLE Column Details
6
Advanced Materialized Views
6.1
About Partitioning and Materialized Views
6.1.1
About Partition Change Tracking
6.1.1.1
About Partition Key and Partition Change Tracking
6.1.1.2
About Join Dependent Expression and Partition Change Tracking
6.1.1.3
About Partition Markers and Partition Change Tracking
6.1.1.4
About Partial Rewrite in Partition Change Tracking
6.1.2
Partitioning a Materialized View
6.1.3
Partitioning a Prebuilt Table
6.1.3.1
Benefits of Partitioning a Materialized View
6.1.4
Rolling Materialized Views
6.2
About Materialized Views in Analytic Processing Environments
6.2.1
About Materialized Views and Analytic Views
6.2.2
About Materialized Views and Hierarchical Cubes
6.2.3
Benefits of Partitioning Materialized Views
6.2.4
About Compressing Materialized Views
6.2.5
About Materialized Views with Set Operators
6.2.5.1
Examples of Materialized Views Using UNION ALL
6.3
About Materialized Views and Models
6.4
About Security Issues with Materialized Views
6.4.1
Querying Materialized Views with Virtual Private Database (VPD)
6.4.1.1
Using Query Rewrite with Virtual Private Database
6.4.1.2
Restrictions with Materialized Views and Virtual Private Database
6.5
Invalidating Materialized Views
6.6
Altering Materialized Views
6.7
Using Real-time Materialized Views
6.7.1
Overview of Real-time Materialized Views
6.7.1.1
Restrictions on Using Real-time Materialized Views
6.7.1.2
About Accessing Real-time Materialized Views
6.7.2
Creating Real-time Materialized Views
6.7.3
Converting an Existing Materialized View into a Real-time Materialized View
6.7.4
Enabling Query Rewrite to Use Real-time Materialized Views
6.7.5
Using Real-time Materialized Views During Query Rewrite
6.7.6
Using Real-time Materialized Views for Direct Query Access
6.7.7
Listing Real-time Materialized Views
6.7.8
Improving Real-time Materialized Views Performance
7
Refreshing Materialized Views
7.1
About Refreshing Materialized Views
7.1.1
About Complete Refresh for Materialized Views
7.1.2
About Fast Refresh for Materialized Views
7.1.3
About Partition Change Tracking (PCT) Refresh for Materialized Views
7.1.4
About the Out-of-Place Refresh Option
7.1.4.1
Types of Out-of-Place Refresh
7.1.4.2
Restrictions and Considerations with Out-of-Place Refresh
7.1.5
About ON COMMIT Refresh for Materialized Views
7.1.6
About ON STATEMENT Refresh for Materialized Views
7.1.7
About Manual Refresh Using the DBMS_MVIEW Package
7.1.8
Refreshing Specific Materialized Views with REFRESH
7.1.9
Refreshing All Materialized Views with REFRESH_ALL_MVIEWS
7.1.10
Refreshing Dependent Materialized Views with REFRESH_DEPENDENT
7.1.11
About Using Job Queues for Refresh
7.1.12
When Fast Refresh is Possible
7.1.13
Refreshing Materialized Views Based on Approximate Queries
7.1.14
About Refreshing Dependent Materialized Views During Online Table Redefinition
7.1.15
Recommended Initialization Parameters for Parallelism
7.1.16
Monitoring a Refresh
7.1.17
Checking the Status of a Materialized View
7.1.17.1
Viewing Partition Freshness
7.1.17.1.1
Examples of Using Views to Determine Freshness
7.1.18
Scheduling Refresh of Materialized Views
7.2
Tips for Refreshing Materialized Views
7.2.1
Tips for Refreshing Materialized Views with Aggregates
7.2.2
Tips for Refreshing Materialized Views Without Aggregates
7.2.3
Tips for Refreshing Nested Materialized Views
7.2.4
Tips for Fast Refresh with UNION ALL
7.2.5
Tips for Fast Refresh with Commit SCN-Based Materialized View Logs
7.2.6
Tips After Refreshing Materialized Views
7.3
Using Materialized Views with Partitioned Tables
7.3.1
Materialized View Fast Refresh with Partition Change Tracking
7.3.1.1
PCT Fast Refresh for Materialized Views: Scenario 1
7.3.1.2
PCT Fast Refresh for Materialized Views: Scenario 2
7.3.1.3
PCT Fast Refresh for Materialized Views: Scenario 3
7.4
Using Partitioning to Improve Data Warehouse Refresh
7.4.1
Data Warehouse Refresh Scenarios
7.4.2
Scenarios for Using Partitioning for Refreshing Data Warehouses
7.4.2.1
Partitioning for Refreshing Data Warehouses: Scenario 1
7.4.2.2
Partitioning for Refreshing Data Warehouses: Scenario 2
7.5
Optimizing DML Operations During Refresh
7.5.1
Implementing an Efficient MERGE Operation
7.5.2
Maintaining Referential Integrity in Data Warehouses
7.5.3
Purging Data from Data Warehouses
8
Synchronous Refresh
8.1
About Synchronous Refresh for Materialized Views
8.1.1
What Is Synchronous Refresh?
8.1.2
Why Use Synchronous Refresh?
8.1.3
Registering Tables and Materialized Views for Synchronous Refresh
8.1.4
Specifying Change Data for Refresh
8.1.5
Synchronous Refresh Preparation and Execution
8.1.6
Materialized View Eligibility Rules and Restrictions for Synchronous Refresh
8.1.6.1
Synchronous Refresh Restrictions: Partitioning
8.1.6.2
Synchronous Refresh Restrictions: Refresh Options
8.1.6.3
Synchronous Refresh Restrictions: Constraints
8.1.6.4
Synchronous Refresh Restrictions: Tables
8.1.6.5
Synchronous Refresh Restrictions: Materialized Views
8.1.6.6
Synchronous Refresh Restrictions: Materialized Views with Aggregates
8.2
Using Synchronous Refresh for Materialized Views
8.2.1
Synchronous Refresh Step 1: Registration Phase
8.2.2
Synchronous Refresh Step 2: Synchronous Refresh Phase
8.2.3
Synchronous Refresh Step 3: The Unregistration Phase
8.3
Using Synchronous Refresh Groups
8.3.1
Examples of Common Actions with Synchronous Refresh Groups
8.3.2
Examples of Working with Multiple Synchronous Refresh Groups
8.4
Specifying and Preparing Change Data for Synchronous Refresh
8.4.1
Working with Partition Operations While Capturing Change Data for Synchronous Refresh
8.4.2
Working with Staging Logs While Capturing Change Data for Synchronous Refresh
8.4.2.1
About the Staging Log Key
8.4.2.2
About Staging Log Rules
8.4.2.3
About Columns Being Updated to NULL
8.4.2.4
Examples of Working with Staging Logs
8.4.2.5
Error Handling in Preparing Staging Logs
8.5
Troubleshooting Synchronous Refresh Operations
8.5.1
Overview of the Status of Refresh Operations
8.5.2
How PREPARE_REFRESH Sets the STATUS Fields
8.5.3
Examples of Preparing for Synchronous Refresh Using PREPARE_REFRESH
8.5.4
How EXECUTE_REFRESH Sets the Status Fields During Synchronous Refresh
8.5.5
Examples of Executing Synchronous Refresh Using EXECUTE_REFRESH
8.5.6
Example of EXECUTE_REFRESH with Constraint Violations
8.6
Performing Synchronous Refresh Eligibility Analysis
8.6.1
Using SYNCREF_TABLE to Store the Results of Synchronous Refresh Eligibility Analysis
8.6.2
Using a VARRAY to Store the Results of Synchronous Refresh Eligibility Analysis
8.6.3
Demo Scripts
8.7
Overview of Synchronous Refresh Security Considerations
9
Monitoring Materialized View Refresh Operations
9.1
About Materialized View Refresh Statistics
9.2
Overview of Managing Materialized View Refresh Statistics
9.3
About Data Dictionary Views that Store Materialized View Refresh Statistics
9.4
Collecting Materialized View Refresh Statistics
9.4.1
About Collecting Materialized View Refresh Statistics
9.4.2
Specifying Default Settings for Collecting Materialized View Refresh Statistics
9.4.3
Modifying the Collection Level for Materialized View Refresh Statistics
9.5
Retaining Materialized View Refresh Statistics
9.5.1
About Retaining Materialized View Refresh Statistics
9.5.2
Specifying the Default Retention Period for Materialized View Refresh Statistics
9.5.3
Modifying the Retention Period for Materialized View Refresh Statistics
9.6
Viewing Materialized View Refresh Statistics Settings
9.7
Purging Materialized View Refresh Statistics
9.8
Viewing Materialized View Refresh Statistics
9.8.1
Viewing Basic Refresh Statistics for a Materialized View
9.8.2
Viewing Detailed Statistics for Each Materialized View Refresh Operation
9.8.3
Viewing Change Data Statistics During Materialized View Refresh Operations
9.8.4
Viewing the SQL Statements Associated with A Materialized View Refresh Operation
9.9
Analyzing Materialized View Refresh Performance Using Refresh Statistics
10
Dimensions
10.1
What are Dimensions?
10.1.1
Requirements for Dimensions in Data Warehouses
10.2
Creating Dimensions
10.2.1
Dropping and Creating Attributes with Columns
10.2.2
Using Multiple Hierarchies While Creating Joins
10.2.3
Using Normalized Dimension Tables to Create Dimensions
10.3
Viewing Dimensions
10.3.1
Viewing Dimensions With Oracle Enterprise Manager
10.3.2
Viewing Dimensions With the DESCRIBE_DIMENSION Procedure
10.4
Using Dimensions with Constraints
10.5
Validating Dimensions
10.6
Altering Dimensions
10.7
Deleting Dimensions
11
Basic Query Rewrite for Materialized Views
11.1
Overview of Query Rewrite
11.1.1
Query Rewrite and the Optimizer
11.1.2
When Does Oracle Rewrite a Query?
11.2
Ensuring that Query Rewrite Takes Effect
11.2.1
Enabling Query Rewrite for Materialized Views
11.2.2
Initialization Parameters for Query Rewrite
11.2.3
Controlling Query Rewrite
11.2.4
About Accuracy of Query Rewrite
11.2.5
About Privileges for Enabling Query Rewrite
11.2.6
Sample Schema and Materialized Views
11.2.7
How to Verify if Query Rewrite Occurred
11.3
Example of Query Rewrite
12
Advanced Query Rewrite for Materialized Views
12.1
How Oracle Rewrites Queries
12.1.1
About Cost-Based Optimization and Query Rewrite
12.1.2
General Query Rewrite Methods
12.1.2.1
When are Constraints and Dimensions Needed for Query Rewrite?
12.1.3
About Checks Made by Query Rewrite
12.1.3.1
Join Compatibility Check for Query Rewrite
12.1.3.1.1
Common Joins
12.1.3.1.2
Query Delta Joins
12.1.3.1.3
Materialized View Delta Joins
12.1.3.1.4
Join Equivalence Recognition
12.1.3.2
Data Sufficiency Check for Query Rewrite
12.1.3.3
Grouping Compatibility Check for Query Rewrite
12.1.3.4
Aggregate Computability Check for Query Rewrite
12.1.4
About Query Rewrite Using Dimensions
12.1.4.1
Benefits of Using Dimensions in a Query Rewrite Environment
12.1.4.2
How to Define Dimensions for Query Rewrite
12.1.4.2.1
Example SQL Statement to Create Time Dimensions
12.2
Types of Query Rewrite
12.2.1
Query Rewrite Method 1: Text Match Rewrite
12.2.2
Query Rewrite Method 2: Join Back
12.2.3
Query Rewrite Method 3: Aggregate Computability
12.2.4
Query Rewrite Method 4: Aggregate Rollup
12.2.5
Query Rewrite Method 5: Rollup Using a Dimension
12.2.6
Query Rewrite Method 6: When Materialized Views Have Only a Subset of Data
12.2.6.1
Query Rewrite Definitions When Materialized Views Have Only a Subset of Data
12.2.6.2
Selection Categories When Materialized Views Have Only a Subset of Data
12.2.6.3
Examples of Query Rewrite Selection
12.2.6.4
About Handling of the HAVING Clause in Query Rewrite
12.2.6.5
About Query Rewrite When the Materialized View has an IN-List
12.2.7
Partition Change Tracking (PCT) Rewrite
12.2.7.1
PCT Rewrite Based on Range Partitioned Tables
12.2.7.2
PCT Rewrite Based on Range-List Partitioned Tables
12.2.7.3
PCT Rewrite Based on List Partitioned Tables
12.2.7.4
PCT Rewrite and PMARKER
12.2.7.5
PCT Rewrite Using Rowid as PMARKER
12.2.8
About Query Rewrite Using Multiple Materialized Views
12.3
Other Query Rewrite Considerations
12.3.1
About Query Rewrite Using Nested Materialized Views
12.3.2
About Query Rewrite in the Presence of Inline Views
12.3.3
About Query Rewrite Using Remote Tables
12.3.4
About Query Rewrite in the Presence of Duplicate Tables
12.3.5
About Query Rewrite Using Date Folding
12.3.6
About Query Rewrite Using View Constraints
12.3.6.1
Abut View Constraints Restrictions
12.3.7
Query Rewrite Using Set Operator Materialized Views
12.3.7.1
UNION ALL Marker and Query Rewrite
12.3.8
About Query Rewrite in the Presence of Grouping Sets
12.3.8.1
About Query Rewrite When Using GROUP BY Extensions
12.3.8.1.1
Materialized View has Simple GROUP BY and Query has Extended GROUP BY
12.3.8.1.2
Materialized View has Extended GROUP BY and Query has Simple GROUP BY
12.3.8.1.3
Both Materialized View and Query Have Extended GROUP BY
12.3.8.2
Hint for Rewriting Queries with Extended GROUP BY
12.3.9
Query Rewrite in the Presence of Window Functions
12.3.10
Query Rewrite and Expression Matching
12.3.10.1
Query Rewrite Using Partially Stale Materialized Views
12.3.11
Cursor Sharing and Bind Variables During Query Rewrite
12.3.12
Handling Expressions in Query Rewrite
12.4
Advanced Query Rewrite Using Equivalences
12.5
Creating Result Cache Materialized Views with Equivalences
12.6
Query Rewrite and Materialized Views Based on Approximate Queries
12.7
Verifying that Query Rewrite has Occurred
12.7.1
Using EXPLAIN PLAN with Query Rewrite
12.7.2
Using the EXPLAIN_REWRITE Procedure with Query Rewrite
12.7.2.1
DBMS_MVIEW.EXPLAIN_REWRITE Syntax
12.7.2.2
Using REWRITE_TABLE to View EXPLAIN_REWRITE Output
12.7.2.3
Using a Varray to View EXPLAIN_REWRITE Output
12.7.2.4
EXPLAIN_REWRITE Benefit Statistics
12.7.2.5
Support for Query Text Larger than 32KB in EXPLAIN_REWRITE
12.7.2.6
About EXPLAIN_REWRITE and Multiple Materialized Views
12.7.2.7
About EXPLAIN_REWRITE Output
12.8
Design Considerations for Improving Query Rewrite Capabilities
12.8.1
Query Rewrite Considerations: Constraints
12.8.2
Query Rewrite Considerations: Dimensions
12.8.3
Query Rewrite Considerations: Outer Joins
12.8.4
Query Rewrite Considerations: Text Match
12.8.5
Query Rewrite Considerations: Aggregates
12.8.6
Query Rewrite Considerations: Grouping Conditions
12.8.7
Query Rewrite Considerations: Expression Matching
12.8.8
Query Rewrite Considerations: Date Folding
12.8.9
Query Rewrite Considerations: Statistics
12.8.10
Query Rewrite Considerations: Hints
12.8.10.1
Query Rewrite: REWRITE and NOREWRITE Hints
12.8.10.2
Query Rewrite: REWRITE_OR_ERROR Hint
12.8.10.3
Query Rewrite: Multiple Materialized View Rewrite Hints
12.8.10.4
Query Rewrite: EXPAND_GSET_TO_UNION Hint
13
Attribute Clustering
13.1
About Attribute Clustering
13.1.1
Methods of Clustering Data
13.1.2
Types of Attribute Clustering
13.1.2.1
Attribute Clustering with Linear Ordering
13.1.2.2
Attribute Clustering with Interleaved Ordering
13.1.3
Example: Attribute Clustered Table
13.1.4
Guidelines for Using Attribute Clustering
13.1.5
Advantages of Attribute-Clustered Tables
13.1.6
About Defining Attribute Clustering for Tables
13.1.7
About Specifying When Attribute Clustering Must be Performed
13.2
Attribute Clustering Operations
13.2.1
Privileges for Attribute-Clustered Tables
13.2.2
Creating Attribute-Clustered Tables with Linear Ordering
13.2.2.1
Examples of Attribute Clustering with Linear Ordering
13.2.3
Creating Attribute-Clustered Tables with Interleaved Ordering
13.2.3.1
Examples of Attribute Clustering with Interleaved Ordering
13.2.4
Maintaining Attribute Clustering
13.2.4.1
Adding Attribute Clustering to an Existing Table
13.2.4.2
Modifying Attribute Clustering Definitions
13.2.4.3
Dropping Attribute Clustering for an Existing Table
13.2.4.4
Using Hints to Control Attribute Clustering for DML Operations
13.2.4.5
Overriding Table-level Settings for Attribute Clustering During DDL Operations
13.2.4.6
Clustering Table Data During Online Table Redefinition
13.3
Viewing Attribute Clustering Information
13.3.1
Determining if Attribute Clustering is Defined for Tables
13.3.2
Viewing Attribute-Clustering Information for Tables
13.3.3
Viewing Information About the Columns on Which Attribute Clustering is Performed
13.3.4
Viewing Information About Dimensions and Joins on Which Attribute Clustering is Performed
14
Using Zone Maps
14.1
About Zone Maps
14.1.1
Difference Between Zone Maps and Indexes
14.1.2
Zone Maps and Attribute Clustering
14.1.3
Types of Zone Maps
14.1.4
Benefits of Zone Maps
14.1.5
Scenarios Which Benefit from Zone Maps
14.1.6
About Maintaining Zone Maps
14.1.6.1
Operations that Require Zone Map Maintenance
14.1.6.2
Scenarios in Which Zone Maps are Automatically Refreshed
14.2
Zone Map Operations
14.2.1
Privileges Required for Zone Maps
14.2.2
Creating Zone Maps
14.2.2.1
Creating Zone Maps with Attribute Clustering
14.2.2.1.1
Creating a Basic Zone Map with Linear Attribute Clustering
14.2.2.1.2
Creating a Join Zone Map with Interleaved Attribute Clustering
14.2.2.1.3
Creating a Zone Map After Attribute Clustering
14.2.2.2
Creating Zone Maps Independent of Attribute Clustering
14.2.2.2.1
Creating a Basic Zone Map Independent of Attribute Clustering
14.2.2.2.2
Creating a Join Zone Map Independent of Attribute Clustering
14.2.3
Modifying Zone Maps
14.2.4
Dropping Zone Maps
14.2.5
Compiling Zone Maps
14.2.6
Controlling the Use of Zone Maps
14.2.6.1
Controlling Zone Map Usage for Entire SQL Workloads
14.2.6.2
Controlling Zone Map Usage for Specific SQL Statements
14.2.7
Maintaining Zone Maps
14.2.7.1
Zone Map Maintenance Considerations
14.3
Refresh and Staleness of Zone Maps
14.3.1
About Staleness of Zone Maps
14.3.2
About Refreshing Zone Maps
14.3.3
Refreshing Zone Maps
14.3.3.1
Refreshing Zone Maps Using the ALTER MATERIALIZED ZONEMAP Command
14.3.3.2
Refreshing Zone Maps Using the DBMS_MVIEW Package
14.4
Performing Pruning Using Zone Maps
14.4.1
How Oracle Database Performs Pruning Using Zone Maps
14.4.1.1
Pruning Tables Using Zone Maps
14.4.1.2
Pruning Partitioned Tables Using Zone Maps and Attribute Clustering
14.4.2
Examples: Performing Pruning with Zone Maps and Attribute Clustering
14.4.2.1
Example: Partitions and Table Scan Pruning
14.4.2.2
Example: Zone Map Join Pruning
14.5
Viewing Zone Map Information
14.5.1
Viewing Details of Zone Maps in the Database
14.5.2
Viewing the Measures of a Zone Map
Part III Data Movement/ETL
15
Data Movement/ETL Overview
15.1
Overview of ETL in Data Warehouses
15.1.1
ETL Basics in Data Warehousing
15.1.1.1
Extraction of Data in Data Warehouses
15.1.1.2
Transportation of Data in Data Warehouses
15.2
ETL Tools for Data Warehouses
15.2.1
Daily Operations in Data Warehouses
15.2.2
Evolution of the Data Warehouse
16
Extraction in Data Warehouses
16.1
Overview of Extraction in Data Warehouses
16.2
Introduction to Extraction Methods in Data Warehouses
16.2.1
Logical Extraction Methods
16.2.2
Physical Extraction Methods
16.2.3
Change Tracking Methods
16.3
Data Warehousing Extraction Examples
16.3.1
Extraction Using Data Files
16.3.1.1
Extracting into Flat Files Using SQL*Plus
16.3.1.2
Extracting into Flat Files Using OCI or Pro*C Programs
16.3.1.3
Exporting into Export Files Using the Export Utility
16.3.1.4
Extracting into Export Files Using External Tables
16.3.2
Extraction Through Distributed Operations
17
Transportation in Data Warehouses
17.1
Overview of Transportation in Data Warehouses
17.2
Introduction to Transportation Mechanisms in Data Warehouses
17.2.1
Transportation Using Flat Files
17.2.2
Transportation Through Distributed Operations
17.2.3
Transportation Using Transportable Tablespaces
17.2.3.1
Using Transportable Tablespaces to Transport Data into Data Warehouses: Example
17.2.3.2
Other Uses of Transportable Tablespaces
18
Loading and Transformation in Data Warehouses
18.1
Overview of Loading and Transformation in Data Warehouses
18.1.1
Data Warehouses: Transformation Flow
18.1.1.1
Multistage Data Transformation in Data Warehouses
18.1.1.2
Pipelined Data Transformation in Data Warehouses
18.1.1.3
Staging Area in Data Warehouses
18.1.2
About Batch Updates and Online Table Redefinition
18.1.3
Overview of Monitoring ETL Operations
18.2
Loading Mechanisms for Data Warehouses
18.2.1
Loading a Data Warehouse with SQL*Loader
18.2.2
Loading a Data Warehouse with External Tables
18.2.3
Loading a Data Warehouse with OCI and Direct-Path APIs
18.2.4
Loading a Data Warehouse with Export/Import
18.3
Transformation Mechanisms in Data Warehouses
18.3.1
Transforming Data Using SQL
18.3.1.1
CREATE TABLE ... AS SELECT And INSERT /*+APPEND*/ AS SELECT
18.3.1.2
Transforming Data Using UPDATE
18.3.1.3
Transforming Data Using MERGE
18.3.1.4
Transforming Data Using Multitable INSERT
18.3.2
Transforming Data Using PL/SQL
18.3.3
Transforming Data Using Table Functions
18.3.3.1
What is a Table Function?
18.4
Error Logging and Handling Mechanisms
18.4.1
Business Rule Violations
18.4.2
Data Rule Violations (Data Errors)
18.4.2.1
Handling Data Errors with SQL
18.4.2.2
Handling Data Errors in PL/SQL
18.4.2.3
Handling Data Errors with an Error Logging Table
18.5
Loading and Transformation Scenarios
18.5.1
Key Lookup Scenario
18.5.2
Business Rule Violation Scenario
18.5.3
Data Error Scenarios
18.5.4
Pivoting Scenarios
Part IV Relational Analytics
19
SQL for Analysis and Reporting
19.1
Overview of SQL for Analysis and Reporting
19.2
Ranking, Windowing, and Reporting Functions
19.2.1
Ranking Functions
19.2.1.1
RANK and DENSE_RANK Functions
19.2.1.1.1
Ranking Order in RANK and DENSE_RANK Functions
19.2.1.1.2
Ranking on Multiple Expressions
19.2.1.1.3
Example: Difference Between RANK and DENSE_RANK
19.2.1.1.4
Ranking Within Groups: Example
19.2.1.1.5
Example: Per Cube and Rollup Group Ranking
19.2.1.1.6
Examples: Treatment of NULLs in Ranking Functions
19.2.1.2
Bottom N Ranking Functions
19.2.1.3
CUME_DIST Function
19.2.1.4
PERCENT_RANK Function
19.2.1.5
NTILE Function
19.2.1.6
ROW_NUMBER Function
19.2.2
Windowing Functions
19.2.2.1
About Treatment of NULLs as Input to Window Functions
19.2.2.2
Windowing Functions with Logical Offset
19.2.2.3
Centered Aggregate Function
19.2.2.4
Windowing Aggregate Functions in the Presence of Duplicates
19.2.2.5
Varying Window Size for Each Row
19.2.2.6
Windowing Aggregate Functions with Physical Offsets
19.2.3
Reporting Functions
19.2.3.1
RATIO_TO_REPORT Function
19.2.4
LAG/LEAD Functions
19.2.4.1
LAG/LEAD Syntax
19.2.5
FIRST_VALUE, LAST_VALUE, and NTH_VALUE Functions
19.2.5.1
FIRST_VALUE and LAST_VALUE Functions
19.2.5.2
NTH_VALUE Function
19.3
Advanced Aggregates for Analysis
19.3.1
About Approximate Aggregates
19.3.2
LISTAGG Function
19.3.2.1
LISTAGG as Aggregate
19.3.2.2
LISTAGG as Reporting Aggregate
19.3.3
FIRST/LAST Functions
19.3.3.1
FIRST/LAST As Regular Aggregates
19.3.3.2
FIRST/LAST As Reporting Aggregates
19.3.4
Inverse Percentile Functions
19.3.4.1
Normal Aggregate Syntax
19.3.4.2
Inverse Percentile Example Basis
19.3.4.3
As Reporting Aggregates
19.3.4.4
Restrictions on Inverse Percentile Functions
19.3.4.5
Using Percentile Functions that Return Approximate Results
19.3.5
Hypothetical Rank Functions
19.3.6
Linear Regression Functions
19.3.6.1
REGR_COUNT Function
19.3.6.2
REGR_AVGY and REGR_AVGX Functions
19.3.6.3
REGR_SLOPE and REGR_INTERCEPT Functions
19.3.6.4
REGR_R2 Function
19.3.6.5
REGR_SXX, REGR_SYY, and REGR_SXY Functions
19.3.6.6
Linear Regression Statistics Examples
19.3.6.7
Sample Linear Regression Calculation
19.3.7
About Statistical Aggregates
19.3.7.1
Descriptive Statistics
19.3.7.2
Hypothesis Testing - Parametric Tests
19.3.7.3
Crosstab Statistics
19.3.7.4
Hypothesis Testing - Non-Parametric Tests
19.3.7.5
Non-Parametric Correlation
19.3.8
About User-Defined Aggregates
19.4
Pivoting Operations
19.4.1
Creating the View Used for Pivoting Examples
19.4.2
Pivoting Example
19.4.3
Pivoting on Multiple Columns
19.4.4
Pivoting: Multiple Aggregates
19.4.5
Distinguishing PIVOT-Generated Nulls from Nulls in Source Data
19.4.6
Wildcard and Subquery Pivoting with XML Operations
19.5
Unpivoting Operations
19.6
Data Densification for Reporting
19.6.1
About Partition Join Syntax
19.6.2
Sample of Sparse Data
19.6.3
Filling Gaps in Data
19.6.4
Filling Gaps in Two Dimensions
19.6.5
Filling Gaps in an Inventory Table
19.6.6
Computing Data Values to Fill Gaps
19.7
Time Series Calculations on Densified Data
19.7.1
Period-to-Period Comparison for One Time Level: Example
19.7.2
Period-to-Period Comparison for Multiple Time Levels: Example
19.7.3
Creating a Custom Member in a Dimension: Example
19.8
Miscellaneous Analysis and Reporting Capabilities
19.8.1
WIDTH_BUCKET Function
19.8.1.1
WIDTH_BUCKET Syntax
19.8.2
Linear Algebra
19.8.3
CASE Expressions
19.8.3.1
Creating Histograms Using CASE Statement
19.8.4
Frequent Itemsets in SQL Analytics
19.9
Limiting SQL Rows
19.9.1
SQL Row Limiting Restrictions and Considerations
20
SQL for Aggregation in Data Warehouses
20.1
Overview of SQL for Aggregation in Data Warehouses
20.1.1
About Analyzing Across Multiple Dimensions
20.1.2
About Optimized Aggregation Performance
20.1.3
Data Warehousing: An Aggregate Scenario
20.2
ROLLUP Extension to GROUP BY
20.2.1
When to Use ROLLUP
20.2.2
ROLLUP Syntax
20.2.3
Partial Rollup
20.3
CUBE Extension to GROUP BY
20.3.1
When to Use CUBE
20.3.2
CUBE Syntax
20.3.3
Partial CUBE
20.3.4
Calculating Subtotals Without CUBE
20.4
GROUPING Functions
20.4.1
GROUPING Function
20.4.2
When to Use GROUPING
20.4.3
GROUPING_ID Function
20.4.4
GROUP_ID Function
20.5
GROUPING SETS Expression
20.5.1
GROUPING SETS Syntax
20.6
About Composite Columns and Grouping
20.7
Concatenated Groupings and Data Aggregation
20.7.1
Concatenated Groupings and Hierarchical Data Cubes
20.8
Considerations when Using Aggregation in Data Warehouses
20.8.1
Hierarchy Handling in ROLLUP and CUBE
20.8.2
Column Capacity in ROLLUP and CUBE
20.8.3
HAVING Clause Used with GROUP BY Extensions
20.8.4
ORDER BY Clause Used with GROUP BY Extensions
20.8.5
Using Other Aggregate Functions with ROLLUP and CUBE
20.8.6
Using In-Memory Aggregation
20.9
Computation Using the WITH Clause
20.10
Working with Hierarchical Cubes in SQL
20.10.1
Specifying Hierarchical Cubes in SQL
20.10.2
Querying Hierarchical Cubes in SQL
20.10.2.1
SQL for Creating Materialized Views to Store Hierarchical Cubes
20.10.2.2
Examples of Hierarchical Cube Materialized Views
21
SQL for Pattern Matching
21.1
Overview of Pattern Matching in Data Warehouses
21.1.1
Why Use Pattern Matching?
21.1.2
How Data is Processed in Pattern Matching
21.1.3
About Pattern Matching Special Capabilities
21.2
Basic Topics in Pattern Matching
21.2.1
Basic Examples of Pattern Matching
21.2.2
Tasks and Keywords in Pattern Matching
21.2.3
Pattern Matching Syntax
21.3
Pattern Matching Details
21.3.1
PARTITION BY: Logically Dividing the Rows into Groups
21.3.2
ORDER BY: Logically Ordering the Rows in a Partition
21.3.3
[ONE ROW | ALL ROWS] PER MATCH: Choosing Summaries or Details for Each Match
21.3.4
MEASURES: Defining Calculations for Use in the Query
21.3.5
PATTERN: Defining the Row Pattern to Be Matched
21.3.5.1
Reluctant Versus Greedy Quantifier
21.3.5.2
Operator Precedence
21.3.6
SUBSET: Defining Union Row Pattern Variables
21.3.7
DEFINE: Defining Primary Pattern Variables
21.3.8
AFTER MATCH SKIP: Defining Where to Restart the Matching Process After a Match Is Found
21.3.9
Expressions in MEASURES and DEFINE
21.3.9.1
MATCH_NUMBER: Finding Which Rows Are in Which Match
21.3.9.2
CLASSIFIER: Finding Which Pattern Variable Applies to Which Rows
21.3.9.3
Row Pattern Column References
21.3.9.4
Aggregates
21.3.9.5
Row Pattern Navigation Operations
21.3.9.5.1
PREV and NEXT
21.3.9.6
Running Versus Final Semantics and Keywords
21.3.9.6.1
RUNNING Versus FINAL Semantics
21.3.9.6.2
RUNNING Versus FINAL Keywords
21.3.9.6.3
Ordinary Row Pattern Column References
21.3.10
Row Pattern Output
21.3.10.1
Correlation Name and Row Pattern Output
21.4
Advanced Topics in Pattern Matching
21.4.1
Nesting FIRST and LAST Within PREV and NEXT in Pattern Matching
21.4.2
Handling Empty Matches or Unmatched Rows in Pattern Matching
21.4.2.1
Handling Empty Matches in Pattern Matching
21.4.2.2
Handling Unmatched Rows in Pattern Matching
21.4.3
How to Exclude Portions of the Pattern from the Output
21.4.4
How to Express All Permutations
21.5
Rules and Restrictions in Pattern Matching
21.5.1
Input Table Requirements in Pattern Matching
21.5.2
Prohibited Nesting in the MATCH_RECOGNIZE Clause
21.5.3
Concatenated MATCH_RECOGNIZE Clause
21.5.4
Aggregate Restrictions
21.6
Examples of Pattern Matching
21.6.1
Pattern Matching Examples: Stock Market
21.6.2
Pattern Matching Examples: Security Log Analysis
21.6.3
Pattern Matching Examples: Sessionization
21.6.4
Pattern Matching Example: Financial Tracking
22
SQL for Modeling
22.1
Overview of SQL Modeling in Data Warehouses
22.1.1
How Data is Processed in a SQL Model
22.1.2
Why Use SQL Modeling in Data Warehouses?
22.1.3
About SQL Modeling Capabilities
22.2
Basic Topics in SQL Modeling
22.2.1
Base Schema for SQL Modeling Examples
22.2.2
MODEL Clause Syntax
22.2.3
Keywords in SQL Modeling
22.2.3.1
Assigning Values and Null Handling
22.2.3.2
Calculation Definition
22.2.4
About Cell Referencing in SQL Modeling
22.2.4.1
Symbolic Dimension References
22.2.4.2
Positional Dimension References
22.2.5
About Rules for SQL Modeling
22.2.6
Order of Evaluation of SQL Modeling Rules
22.2.7
Global and Local Keywords for SQL Modeling Rules
22.2.8
UPDATE, UPSERT, and UPSERT ALL Behavior
22.2.8.1
UPDATE Behavior
22.2.8.2
UPSERT Behavior
22.2.8.3
UPSERT ALL Behavior
22.2.8.3.1
Example: UPSERT ALL Behavior
22.2.9
Treatment of NULLs and Missing Cells in SQL Modeling
22.2.9.1
Distinguishing Missing Cells from NULLs
22.2.9.2
Use Defaults for Missing Cells and NULLs
22.2.9.3
Using NULLs in a Cell Reference
22.2.10
About Reference Models in SQL Modeling
22.3
Advanced Topics in SQL Modeling
22.3.1
FOR Loops in SQL Modeling
22.3.1.1
Evaluation of Formulas with FOR Loops
22.3.1.1.1
Unfolding For UPDATE and UPSERT Rules
22.3.1.1.2
Unfolding For UPSERT ALL: Rules
22.3.1.1.3
Restrictions on Using FOR Loop Expressions on the Left Side of Formulas
22.3.2
Iterative Models in SQL Modeling
22.3.3
Rule Dependency in AUTOMATIC ORDER Models
22.3.4
Ordered Rules in SQL Modeling
22.3.5
Analytic Functions in SQL Modeling
22.3.6
Unique Dimensions Versus Unique Single References in SQL Modeling
22.3.7
Rules and Restrictions when Using SQL for Modeling
22.4
Performance Considerations with SQL Modeling
22.4.1
Parallel Execution and SQL Modeling
22.4.2
Aggregate Computation and SQL Modeling
22.4.3
Using EXPLAIN PLAN to Understand Model Queries
22.5
Examples of SQL Modeling
22.5.1
SQL Modeling Example 1: Calculating Sales Differences
22.5.2
SQL Modeling Example 2: Calculating Percentage Change
22.5.3
SQL Modeling Example 3: Calculating Net Present Value
22.5.4
SQL Modeling Example 4: Calculating Using Simultaneous Equations
22.5.5
SQL Modeling Example 5: Calculating Using Regression
22.5.6
SQL Modeling Example 6: Calculating Mortgage Amortization
23
Advanced Analytical SQL
23.1
Examples of Business Intelligence Queries
23.1.1
Business Intelligence Query Example 1: Percent Change in Market Share of Products in a Calculated Set
23.1.2
Business Intelligence Query Example 2: Sales Projection that Fills in Missing Data
23.1.3
Business Intelligence Query Example 3: Customer Analysis by Grouping Customers into Buckets
23.1.4
Business Intelligence Query Example 4: Frequent Itemsets
Part V Analytic Views
24
Overview of Analytic Views
24.1
What Are Analytic Views?
24.2
Privileges for Analytic Views
24.3
Application Programming Interfaces for Analytic Views
24.4
Compilation States of Analytic Views
24.5
Validation of Data
24.6
Classifications for Analytic Views
24.7
Share Analytic Views with Application Containers
24.8
Alter or Drop an Analytic View Object
24.9
Data and Scripts for Examples
24.9.1
About the Data and Scripts for Examples
24.9.2
Create Attribute Dimension Statements
24.9.3
Create Hierarchy Statements
24.9.4
Create Analytic View Statements
25
Attribute Dimension and Hierarchy Objects
25.1
About Attribute Dimensions and Hierarchies
25.2
Attributes and Hierarchical Attributes
25.3
Order Levels
25.4
Level Keys
25.5
Determine Attribute Relationships
26
Analytic View Objects
26.1
About Analytic Views
26.2
Attribute Dimensions and Analytic Views
26.3
Hierarchies of Analytic Views
26.4
Measures of Analytic Views
26.5
Create Analytic Views
26.6
Examples of Calculated Measures
26.7
Attribute Reporting
Glossary
Index
Scripting on this page enhances content navigation, but does not change the content in any way.