1/32

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 12c Release 1 (12.1.0.2)
  • New Features
• Changes in Oracle Database 12c Release 1 (12.1.0.1)
  • New Features
  • Desupported Features

Part I Data Warehouse - Fundamentals

1 Introduction to Data Warehousing Concepts

• What Is a Data Warehouse?
  • Key Characteristics of a Data Warehouse
• Contrasting OLTP and Data Warehousing Environments
• Common Data Warehouse Tasks
• Data Warehouse Architectures
  • Data Warehouse Architecture: Basic
  • Data Warehouse Architecture: with a Staging Area
  • Data Warehouse Architecture: with a Staging Area and Data Marts

2 Data Warehousing Logical Design

• Logical Versus Physical Design in Data Warehouses
• Creating a Logical Design
  • What is a Schema?
• About Third Normal Form Schemas
  • About Normalization
  • Design Concepts for 3NF Schemas
    • Identifying Candidate Primary Keys
    • Foreign Key Relationships and Referential Integrity Constraints
    • Denormalization
• About Star Schemas
  • About Facts and Dimensions in Star Schemas
    • About Fact Tables in Data Warehouses
    • About Dimension Tables in Data Warehouses
  • Design Concepts in Star Schemas
  • About Snowflake Schemas
• About the Oracle In-Memory Column Store
  • Benefits of Using the Oracle In-Memory Column Store
  • Using the Oracle In-Memory Column Store
  • Using Vector Joins to Enhance Join Performance
• Automatic Big Table Caching to Improve the Performance of In-Memory Parallel Queries
• About In-Memory Aggregation
  • VECTOR GROUP BY Aggregation and the Oracle In-Memory Column Store
  • When to Use VECTOR GROUP BY Aggregation
  • When Is VECTOR GROUP BY Aggregation Used to Process Analytic Queries?

3 Data Warehousing Physical Design

• Moving from Logical to Physical Design
• About Physical Design
  • Physical Design Structures
    • About Tablespaces in Data Warehouses
    • About Partitioning in Data Warehouses
      • Basic Partitioning Strategies Used in Data Warehouses
    • Index Partitioning in Data Warehouses
    • About Partitioning for Manageability
    • About Partitioning for Performance
    • About Partitioning for Availability
  • About Views in Data Warehouses
  • About Integrity Constraints in Data Warehouses
  • About Indexes and Partitioned Indexes in Data Warehouses
  • About Materialized Views in Data Warehouses
  • About Dimensions in Data Warehouses
    • About Dimension Hierarchies
      • About Levels
      • About Level Relationships
    • Typical Dimension Hierarchy

4 Data Warehousing Optimizations and Techniques

• Using Indexes in Data Warehouses
  • About Using Bitmap Indexes in Data Warehouses
    • About Bitmap Indexes and Nulls
    • About Bitmap Indexes on Partitioned Tables
  • Benefits of Indexes for Data Warehousing Applications
  • About Cardinality and Bitmap Indexes
  • How to Determine Candidates for Using a Bitmap Index
  • Using Bitmap Join Indexes in Data Warehouses
    • Four Join Models for Bitmap Join Indexes in Data Warehouses
    • Bitmap Join Index Restrictions and Requirements
  • Using B-Tree Indexes in Data Warehouses
  • Using Index Compression
  • Choosing Between Local Indexes and Global Indexes
• Using Integrity Constraints 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 in a Data Warehouse
    • NOT NULL Constraints in a Data Warehouse
    • Integrity Constraints and Parallelism in a Data Warehouse
    • Integrity Constraints and Partitioning in a Data Warehouse
    • View Constraints in a Data Warehouse
• About Parallel Execution in Data Warehouses
  • Why Use Parallel Execution?
    • When to Implement Parallel Execution
    • When Not to Implement Parallel Execution
  • Automatic Degree of Parallelism and Statement Queuing
  • About In-Memory Parallel Execution in Data Warehouses
• About Optimizing Storage Requirements in Data Warehouses
  • Using Data Compression to Improve Storage in Data Warehouses
• Optimizing Star Queries and 3NF 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
  • Optimizing Third Normal Form Schemas
    • 3NF Schemas: Partitioning
      • Partitioning for Manageability
      • Partitioning for Easier Data Access
      • Partitioning for Join Performance
    • 3NF Schemas: Parallel Query Execution
      • Whether or Not to Use Cross Instance Parallel Execution in Oracle RAC
  • Optimizing Star Queries Using VECTOR GROUP BY Aggregation

Part II Optimizing Data Warehouses

5 Basic Materialized Views

• Overview of Data Warehousing with Materialized Views
  • About Materialized Views for Data Warehouses
  • About Materialized Views for Distributed Computing
  • About Materialized Views for Mobile Computing
  • The Need for Materialized Views
  • Components of Summary Management
  • Data Warehousing Terminology
  • About Materialized View Schema Design
    • Schemas and Dimension Tables
    • Guidelines for Materialized View Schema Design
  • About Loading Data into Data Warehouses
  • Overview of Materialized View Management Tasks
• Types of Materialized Views
  • About Materialized Views with Aggregates
    • Requirements for Using Materialized Views with Aggregates
  • About Materialized Views Containing Only Joins
    • Materialized Join Views FROM Clause Considerations
  • About Nested Materialized Views
    • Why Use Nested Materialized Views?
    • About 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
  • About Materialized Views Names
  • About Storage And Table Compression for Materialized Views
  • About Build Methods for Materialized Views
  • About Enabling Query Rewrite for Materialized Views
  • About Query Rewrite Restrictions
    • About Materialized View Restrictions for Query Rewrite
    • General Query Rewrite Restrictions
  • About Refresh Options for Materialized Views
    • About Refresh Modes for Materialized Views
    • About Types of Materialized View Refresh
    • About Using Trusted Constraints and Materialized View Refresh
    • 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
    • About Achieving Refresh Goals
      • Refreshing Materialized Views on Prebuilt Tables
    • Refreshing Nested Materialized Views
  • ORDER BY Clause in Materialized Views
  • Using Oracle Enterprise Manager to Create Materialized Views
  • Using Materialized Views with NLS Parameters
  • Adding Comments to Materialized Views
• Creating Materialized View Logs
  • Using the FORCE Option With Materialized View Logs
  • Purging Materialized View Logs
• 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

6 Advanced Materialized Views

• About Partitioning and Materialized Views
  • About Partition Change Tracking
    • About Partition Key and Partition Change Tracking
    • About Join Dependent Expression and Partition Change Tracking
    • About Partition Markers and Partition Change Tracking
    • About Partial Rewrite in Partition Change Tracking
  • Partitioning a Materialized View
  • Partitioning a Prebuilt Table
    • Benefits of Partitioning a Materialized View
  • Rolling Materialized Views
• About Materialized Views in Analytic Processing Environments
  • About Materialized Views and Hierarchical Cubes
  • Benefits of Partitioning Materialized Views
  • About Compressing Materialized Views
  • About Materialized Views with Set Operators
    • Examples of Materialized Views Using UNION ALL
• About Materialized Views and Models
• Invalidating Materialized Views
• About 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

7 Refreshing Materialized Views

• About Refreshing Materialized Views
  • About Complete Refresh for Materialized Views
  • About Fast Refresh for Materialized Views
  • About Partition Change Tracking (PCT) Refresh for Materialized Views
  • About the Out-of-Place Refresh Option
    • Types of Out-of-Place Refresh
    • Restrictions and Considerations with Out-of-Place Refresh
  • About ON COMMIT Refresh for Materialized Views
  • About Manual Refresh Using the DBMS_MVIEW Package
  • Refreshing Specific Materialized Views with REFRESH
  • Refreshing All Materialized Views with REFRESH_ALL_MVIEWS
  • Refreshing Dependent Materialized Views with REFRESH_DEPENDENT
  • About 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
    • Viewing Partition Freshness
      • Examples of Using Views to Determine Freshness
  • Scheduling Refresh of Materialized Views
• Tips for Refreshing Materialized Views
  • 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 for Fast Refresh with Commit SCN-Based Materialized View Logs
  • Tips After Refreshing Materialized Views
• Using Materialized Views with Partitioned Tables
  • Materialized View Fast Refresh with Partition Change Tracking
    • PCT Fast Refresh for Materialized Views: Scenario 1
    • PCT Fast Refresh for Materialized Views: Scenario 2
    • PCT Fast Refresh for Materialized Views: Scenario 3
• Using Partitioning to Improve Data Warehouse Refresh
  • Data Warehouse Refresh Scenarios
  • Scenarios for Using Partitioning for Refreshing Data Warehouses
    • Partitioning for Refreshing Data Warehouses: Scenario 1
    • Partitioning for Refreshing Data Warehouses: Scenario 2
• Optimizing DML Operations During Refresh
  • Implementing an Efficient MERGE Operation
  • Maintaining Referential Integrity in Data Warehouses
  • Purging Data from Data Warehouses

8 Synchronous Refresh

• About Synchronous Refresh for Materialized Views
  • What Is Synchronous Refresh?
  • Why Use Synchronous Refresh?
  • Registering Tables and Materialized Views for Synchronous Refresh
  • Specifying Change Data for Refresh
  • Synchronous Refresh Preparation and Execution
  • Materialized View Eligibility Rules and Restrictions for Synchronous Refresh
    • Synchronous Refresh Restrictions: Partitioning
    • Synchronous Refresh Restrictions: Refresh Options
    • Synchronous Refresh Restrictions: Constraints
    • Synchronous Refresh Restrictions: Tables
    • Synchronous Refresh Restrictions: Materialized Views
    • Synchronous Refresh Restrictions: Materialized Views with Aggregates
• Using Synchronous Refresh for Materialized Views
  • Synchronous Refresh Step 1: Registration Phase
  • Synchronous Refresh Step 2: Synchronous Refresh Phase
  • Synchronous Refresh Step 3: The Unregistration Phase
• Using Synchronous Refresh Groups
  • Examples of Common Actions with Synchronous Refresh Groups
  • Examples of Working with Multiple Synchronous Refresh Groups
• Specifying and Preparing Change Data for Synchronous Refresh
  • Working with Partition Operations While Capturing Change Data for Synchronous Refresh
  • Working with Staging Logs While Capturing Change Data for Synchronous Refresh
    • About the Staging Log Key
    • About Staging Log Rules
    • About Columns Being Updated to NULL
    • Examples of Working with Staging Logs
    • Error Handling in Preparing Staging Logs
• Troubleshooting Synchronous Refresh Operations
  • Overview of the Status of Refresh Operations
  • How PREPARE_REFRESH Sets the STATUS Fields
  • Examples of Preparing for Synchronous Refresh Using PREPARE_REFRESH
  • How EXECUTE_REFRESH Sets the Status Fields During Synchronous Refresh
  • Examples of Executing Synchronous Refresh Using EXECUTE_REFRESH
  • Example of EXECUTE_REFRESH with Constraint Violations
• Performing Synchronous Refresh Eligibility Analysis
  • Using SYNCREF_TABLE to Store the Results of Synchronous Refresh Eligibility Analysis
  • Using a VARRAY to Store the Results of Synchronous Refresh Eligibility Analysis
  • Demo Scripts
• Overview of Synchronous Refresh Security Considerations

9 Dimensions

• What are Dimensions?
  • Requirements for Dimensions in Data Warehouses
• Creating Dimensions
  • Dropping and Creating Attributes with Columns
  • Using Multiple Hierarchies While Creating Joins
  • Using Normalized Dimension Tables to Create Dimensions
• Viewing Dimensions
  • Viewing Dimensions With Oracle Enterprise Manager
  • Viewing Dimensions With the DESCRIBE_DIMENSION Procedure
• Using Dimensions with Constraints
• Validating Dimensions
• Altering Dimensions
• Deleting Dimensions

10 Basic Query Rewrite for Materialized Views

• Overview of Query Rewrite
  • Query Rewrite and the Optimizer
  • When Does Oracle Rewrite a Query?
• Ensuring that Query Rewrite Takes Effect
  • Enabling Query Rewrite for Materialized Views
  • Initialization Parameters for Query Rewrite
  • Controlling Query Rewrite
  • About Accuracy of Query Rewrite
  • About Privileges for Enabling Query Rewrite
  • Sample Schema and Materialized Views
  • How to Verify if Query Rewrite Occurred
• Example of Query Rewrite

11 Advanced Query Rewrite for Materialized Views

• How Oracle Rewrites Queries
  • About Cost-Based Optimization and Query Rewrite
  • General Query Rewrite Methods
    • When are Constraints and Dimensions Needed for Query Rewrite?
  • About Checks Made by Query Rewrite
    • Join Compatibility Check for Query Rewrite
      • Common Joins
      • Query Delta Joins
      • Materialized View Delta Joins
      • Join Equivalence Recognition
    • Data Sufficiency Check for Query Rewrite
    • Grouping Compatibility Check for Query Rewrite
    • Aggregate Computability Check for Query Rewrite
  • About Query Rewrite Using Dimensions
    • Benefits of Using Dimensions in a Query Rewrite Environment
    • How to Define Dimensions for Query Rewrite
      • Example SQL Statement to Create Time Dimensions
• Types of Query Rewrite
  • Query Rewrite Method 1: Text Match Rewrite
  • Query Rewrite Method 2: Join Back
  • Query Rewrite Method 3: Aggregate Computability
  • Query Rewrite Method 4: Aggregate Rollup
  • Query Rewrite Method 5: Rollup Using a Dimension
  • Query Rewrite Method 6: When Materialized Views Have Only a Subset of Data
    • Query Rewrite Definitions When Materialized Views Have Only a Subset of Data
    • Selection Categories When Materialized Views Have Only a Subset of Data
    • Examples of Query Rewrite Selection
    • About Handling of the HAVING Clause in Query Rewrite
    • About 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
  • About Query Rewrite Using Multiple Materialized Views
• Other Query Rewrite Considerations
  • About Query Rewrite Using Nested Materialized Views
  • About Query Rewrite in the Presence of Inline Views
  • About Query Rewrite Using Remote Tables
  • About Query Rewrite in the Presence of Duplicate Tables
  • About Query Rewrite Using Date Folding
  • About Query Rewrite Using View Constraints
    • Abut View Constraints Restrictions
  • Query Rewrite Using Set Operator Materialized Views
    • UNION ALL Marker and Query Rewrite
  • About Query Rewrite in the Presence of Grouping Sets
    • About Query Rewrite When Using GROUP BY Extensions
      • Materialized View has Simple GROUP BY and Query has Extended GROUP BY
      • Materialized View has Extended GROUP BY and Query has Simple GROUP BY
      • Both Materialized View and Query Have Extended GROUP BY
    • Hint for Rewriting 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 During Query Rewrite
  • Handling Expressions in Query Rewrite
• Advanced Query Rewrite Using Equivalences
• Creating Result Cache Materialized Views with 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 to View EXPLAIN_REWRITE Output
    • Using a Varray to View EXPLAIN_REWRITE Output
    • EXPLAIN_REWRITE Benefit Statistics
    • Support for Query Text Larger than 32KB in EXPLAIN_REWRITE
    • About EXPLAIN_REWRITE and Multiple Materialized Views
    • About 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
    • Query Rewrite: REWRITE and NOREWRITE Hints
    • Query Rewrite: REWRITE_OR_ERROR Hint
    • Query Rewrite: Multiple Materialized View Rewrite Hints
    • Query Rewrite: EXPAND_GSET_TO_UNION Hint

12 Attribute Clustering

• About Attribute Clustering
  • Methods of Clustering Data
  • Types of Attribute Clustering
    • Attribute Clustering with Linear Ordering
    • Attribute Clustering with Interleaved Ordering
  • Example: Attribute Clustered Table
  • Guidelines for Using Attribute Clustering
  • Advantages of Attribute-Clustered Tables
  • About Defining Attribute Clustering for Tables
  • About Specifying When Attribute Clustering Must be Performed
• Attribute Clustering Operations
  • Privileges for Attribute-Clustered Tables
  • Creating Attribute-Clustered Tables with Linear Ordering
    • Examples of Attribute Clustering with Linear Ordering
  • Creating Attribute-Clustered Tables with Interleaved Ordering
    • Examples of Attribute Clustering with Interleaved Ordering
  • Maintaining Attribute Clustering
    • Adding Attribute Clustering to an Existing Table
    • Modifying Attribute Clustering Definitions
    • Dropping Attribute Clustering for an Existing Table
    • Using Hints to Control Attribute Clustering for DML Operations
    • Overriding Table-level Settings for Attribute Clustering During DDL Operations
    • Clustering Table Data During Online Table Redefinition
• Viewing Attribute Clustering Information
  • Determining if Attribute Clustering is Defined for Tables
  • Viewing Attribute-Clustering Information for Tables
  • Viewing Information About the Columns on Which Attribute Clustering is Performed
  • Viewing Information About Dimensions and Joins on Which Attribute Clustering is Performed

13 Using Zone Maps

• About Zone Maps
  • Difference Between Zone Maps and Indexes
  • Zone Maps and Attribute Clustering
  • Types of Zone Maps
  • Benefits of Zone Maps
  • Scenarios Which Benefit from Zone Maps
  • About Maintaining Zone Maps
    • Operations that Require Zone Map Maintenance
    • Scenarios in Which Zone Maps are Automatically Refreshed
• Zone Map Operations
  • Privileges Required for Zone Maps
  • Creating Zone Maps
    • Creating Zone Maps with Attribute Clustering
      • Creating a Basic Zone Map with Linear Attribute Clustering
      • Creating a Join Zone Map with Interleaved Attribute Clustering
      • Creating a Zone Map After Attribute Clustering
    • Creating Zone Maps Independent of Attribute Clustering
      • Creating a Basic Zone Map Independent of Attribute Clustering
      • Creating a Join Zone Map Independent of Attribute Clustering
  • Modifying Zone Maps
  • Dropping Zone Maps
  • Compiling Zone Maps
  • Controlling the Use of Zone Maps
    • Controlling Zone Map Usage for Entire SQL Workloads
    • Controlling Zone Map Usage for Specific SQL Statements
  • Maintaining Zone Maps
    • Zone Map Maintenance Considerations
• Refresh and Staleness of Zone Maps
  • About Staleness of Zone Maps
  • About Refreshing Zone Maps
  • Refreshing Zone Maps
    • Refreshing Zone Maps Using the ALTER MATERIALIZED ZONEMAP Command
    • Refreshing Zone Maps Using the DBMS_MVIEW Package
• Performing Pruning Using Zone Maps
  • How Oracle Database Performs Pruning Using Zone Maps
    • Pruning Tables Using Zone Maps
    • Pruning Partitioned Tables Using Zone Maps and Attribute Clustering
  • Examples: Performing Pruning with Zone Maps and Attribute Clustering
    • Example: Partitions and Table Scan Pruning
    • Example: Zone Map Join Pruning
• Viewing Zone Map Information
  • Viewing Details of Zone Maps in the Database
  • Viewing the Measures of a Zone Map

Part III Data Movement/ETL

14 Data Movement/ETL Overview

• Overview of ETL in Data Warehouses
  • ETL Basics in Data Warehousing
    • Extraction of Data in Data Warehouses
    • Transportation of Data in Data Warehouses
• ETL Tools for Data Warehouses
  • Daily Operations in Data Warehouses
  • Evolution of the Data Warehouse

15 Extraction in Data Warehouses

• Overview of Extraction in Data Warehouses
• Introduction to Extraction Methods in Data Warehouses
  • Logical Extraction Methods
  • Physical Extraction Methods
  • Change Tracking Methods
• 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

16 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
    • Using Transportable Tablespaces to Transport Data into Data Warehouses: Example
    • Other Uses of Transportable Tablespaces

17 Loading and Transformation in Data Warehouses

• Overview of Loading and Transformation in Data Warehouses
  • Data Warehouses: Transformation Flow
    • Multistage Data Transformation in Data Warehouses
    • Pipelined Data Transformation in Data Warehouses
    • Staging Area in Data Warehouses
• Loading Mechanisms for Data Warehouses
  • 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 in Data Warehouses
  • 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

Part IV Relational Analytics

18 SQL for Analysis and Reporting

• Overview of SQL for Analysis and Reporting
• Ranking, Windowing, and Reporting Functions
  • Ranking Functions
    • RANK and DENSE_RANK Functions
      • Ranking Order in RANK and DENSE_RANK Functions
      • Ranking on Multiple Expressions
      • Example: Difference Between RANK and DENSE_RANK
      • Ranking Within Groups: Example
      • Example: Per Cube and Rollup Group Ranking
      • Examples: Treatment of NULLs in Ranking Functions
    • Bottom N Ranking Functions
    • CUME_DIST Function
    • PERCENT_RANK Function
    • NTILE Function
    • ROW_NUMBER Function
  • Windowing Functions
    • About 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
  • Reporting Functions
    • RATIO_TO_REPORT Function
  • LAG/LEAD Functions
    • LAG/LEAD Syntax
  • FIRST_VALUE, LAST_VALUE, and NTH_VALUE Functions
    • FIRST_VALUE and LAST_VALUE Functions
    • NTH_VALUE Function
• Advanced Aggregates for Analysis
  • LISTAGG Function
    • LISTAGG as Aggregate
    • LISTAGG as Reporting Aggregate
  • FIRST/LAST Functions
    • FIRST/LAST As Regular Aggregates
    • FIRST/LAST As Reporting Aggregates
  • Inverse Percentile Functions
    • Normal Aggregate Syntax
    • Inverse Percentile Example Basis
    • As Reporting Aggregates
    • Restrictions on Inverse Percentile Functions
  • Hypothetical Rank Functions
  • 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
  • About Statistical Aggregates
    • Descriptive Statistics
    • Hypothesis Testing - Parametric Tests
    • Crosstab Statistics
    • Hypothesis Testing - Non-Parametric Tests
    • Non-Parametric Correlation
  • About User-Defined Aggregates
• Pivoting Operations
  • Creating the View Used for Pivoting Examples
  • Pivoting Example
  • Pivoting on Multiple Columns
  • Pivoting: Multiple Aggregates
  • Distinguishing PIVOT-Generated Nulls from Nulls in Source Data
  • Wildcard and Subquery Pivoting with XML Operations
• Unpivoting Operations
• Data Densification for Reporting
  • About 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
• Miscellaneous Analysis and Reporting Capabilities
  • WIDTH_BUCKET Function
    • WIDTH_BUCKET Syntax
  • Linear Algebra
  • CASE Expressions
    • Creating Histograms Using CASE Statement
  • Frequent Itemsets in SQL Analytics
• Limiting SQL Rows
  • SQL Row Limiting Restrictions and Considerations

19 SQL for Aggregation in Data Warehouses

• Overview of SQL for Aggregation in Data Warehouses
  • About Analyzing Across Multiple Dimensions
  • About Optimized Aggregation Performance
  • Data Warehousing: An Aggregate Scenario
• 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
• About Composite Columns and Grouping
• Concatenated Groupings and Data Aggregation
  • Concatenated Groupings and Hierarchical Data Cubes
• Considerations when Using Aggregation in Data Warehouses
  • 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
  • Using In-Memory Aggregation
• 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

20 SQL for Pattern Matching

• Overview of Pattern Matching in Data Warehouses
  • Why Use Pattern Matching?
  • How Data is Processed in Pattern Matching
  • About Pattern Matching Special Capabilities
• Basic Topics in Pattern Matching
  • Basic Examples of Pattern Matching
  • Tasks and Keywords in Pattern Matching
  • Pattern Matching Syntax
• Pattern Matching Details
  • PARTITION BY: Logically Dividing the Rows into Groups
  • ORDER BY: Logically Ordering the Rows in a Partition
  • [ONE ROW | ALL ROWS] PER MATCH: Choosing Summaries or Details for Each Match
  • MEASURES: Defining Calculations for Use in the Query
  • PATTERN: Defining the Row Pattern to Be Matched
    • Reluctant Versus Greedy Quantifier
    • Operator Precedence
  • SUBSET: Defining Union Row Pattern Variables
  • DEFINE: Defining Primary Pattern Variables
  • AFTER MATCH SKIP: Defining Where to Restart the Matching Process After a Match Is Found
  • Expressions in MEASURES and DEFINE
    • MATCH_NUMBER: Finding Which Rows Are in Which Match
    • CLASSIFIER: Finding Which Pattern Variable Applies to Which Rows
    • Row Pattern Column References
    • Aggregates
    • Row Pattern Navigation Operations
      • PREV and NEXT
    • Running Versus Final Semantics and Keywords
      • RUNNING Versus FINAL Semantics
      • RUNNING Versus FINAL Keywords
      • Ordinary Row Pattern Column References
  • Row Pattern Output
    • Correlation Name and Row Pattern Output
• Advanced Topics in Pattern Matching
  • Nesting FIRST and LAST Within PREV and NEXT in Pattern Matching
  • Handling Empty Matches or Unmatched Rows in Pattern Matching
    • Handling Empty Matches in Pattern Matching
    • Handling Unmatched Rows in Pattern Matching
  • How to Exclude Portions of the Pattern from the Output
  • How to Express All Permutations
• Rules and Restrictions in Pattern Matching
  • Input Table Requirements in Pattern Matching
  • Prohibited Nesting in the MATCH_RECOGNIZE Clause
  • Concatenated MATCH_RECOGNIZE Clause
  • Aggregate Restrictions
• Examples of Pattern Matching
  • Pattern Matching Examples: Stock Market
  • Pattern Matching Examples: Security Log Analysis
  • Pattern Matching Examples: Sessionization
  • Pattern Matching Example: Financial Tracking

21 SQL for Modeling

• Overview of SQL Modeling in Data Warehouses
  • How Data is Processed in a SQL Model
  • Why Use SQL Modeling in Data Warehouses?
  • About SQL Modeling Capabilities
• Basic Topics in SQL Modeling
  • Base Schema for SQL Modeling Examples
  • MODEL Clause Syntax
  • Keywords in SQL Modeling
    • Assigning Values and Null Handling
    • Calculation Definition
  • About Cell Referencing in SQL Modeling
    • Symbolic Dimension References
    • Positional Dimension References
  • About Rules for SQL Modeling
  • Order of Evaluation of SQL Modeling Rules
  • Global and Local Keywords for SQL Modeling Rules
  • UPDATE, UPSERT, and UPSERT ALL Behavior
    • UPDATE Behavior
    • UPSERT Behavior
    • UPSERT ALL Behavior
      • Example: UPSERT ALL Behavior
  • Treatment of NULLs and Missing Cells in SQL Modeling
    • Distinguishing Missing Cells from NULLs
    • Use Defaults for Missing Cells and NULLs
    • Using NULLs in a Cell Reference
  • About Reference Models in SQL Modeling
• Advanced Topics in SQL Modeling
  • FOR Loops in SQL Modeling
    • Evaluation of Formulas with FOR Loops
      • Unfolding For UPDATE and UPSERT Rules
      • Unfolding For UPSERT ALL: Rules
      • Restrictions on Using FOR Loop Expressions on the Left Side of Formulas
  • Iterative Models in SQL Modeling
  • Rule Dependency in AUTOMATIC ORDER Models
  • Ordered Rules in SQL Modeling
  • Analytic Functions in SQL Modeling
  • Unique Dimensions Versus Unique Single References in SQL Modeling
  • Rules and Restrictions when Using SQL for Modeling
• Performance Considerations with SQL Modeling
  • Parallel Execution and SQL Modeling
  • Aggregate Computation and SQL Modeling
  • Using EXPLAIN PLAN to Understand Model Queries
• Examples of SQL Modeling
  • SQL Modeling Example 1: Calculating Sales Differences
  • SQL Modeling Example 2: Calculating Percentage Change
  • SQL Modeling Example 3: Calculating Net Present Value
  • SQL Modeling Example 4: Calculating Using Simultaneous Equations
  • SQL Modeling Example 5: Calculating Using Regression
  • SQL Modeling Example 6: Calculating Mortgage Amortization

22 Advanced Analytical SQL

• Examples of Business Intelligence Queries
  • Business Intelligence Query Example 1: Percent Change in Market Share of Products in a Calculated Set
  • Business Intelligence Query Example 2: Sales Projection that Fills in Missing Data
  • Business Intelligence Query Example 3: Customer Analysis by Grouping Customers into Buckets
  • Business Intelligence Query Example 4: Frequent Itemsets

Glossary

Index