Table of Contents
- Title and Copyright Information
- Preface
- Part I Data Warehouse - Fundamentals
- Introduction to Data Warehousing Concepts
- Data Warehousing Logical Design
- Data Warehousing Physical Design
- Moving from Logical to Physical Design
- About Physical Design
- Data Warehousing Optimizations and Techniques
- Using Indexes in Data Warehouses
- About Using Bitmap Indexes in Data Warehouses
- 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
- 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
- About Optimizing Storage Requirements in Data Warehouses
- Optimizing Star Queries and 3NF Schemas
- About Approximate Query Processing
- About Approximate Top-N Query Processing
- Using Indexes in Data Warehouses
- Part II Optimizing Data Warehouses
- 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
- About Loading Data into Data Warehouses
- Overview of Materialized View Management Tasks
- Types of Materialized Views
- Creating Materialized Views
- Creating Materialized Views with Column Alias Lists
- Creating Materialized Views Based on Hybird Partitioned Tables
- 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 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 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
- Creating Materialized Views Based on Approximate Queries
- Creating a Materialized View Containing Bitmap-based COUNT(DISTINCT) Functions
- Registering Existing Materialized Views
- Choosing Indexes for Materialized Views
- Dropping Materialized Views
- Analyzing Materialized View Capabilities
- Overview of Data Warehousing with Materialized Views
- Advanced Materialized Views
- About Partitioning and Materialized Views
- About Materialized Views in Analytic Processing Environments
- About Materialized Views and Models
- About Security Issues with Materialized Views
- Invalidating Materialized Views
- Altering Materialized Views
- Using Real-time Materialized Views
- Overview of Real-time Materialized Views
- Creating Real-time Materialized Views
- Converting an Existing Materialized View into a Real-time Materialized View
- Enabling Query Rewrite to Use Real-time Materialized Views
- Using Real-time Materialized Views During Query Rewrite
- Using Real-time Materialized Views for Direct Query Access
- Listing Real-time Materialized Views
- Improving Real-time Materialized Views Performance
- 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 Logical Partition Change Tracking (LPCT) Refresh for Materialized Views
- About the Out-of-Place Refresh Option
- About ON COMMIT Refresh for Materialized Views
- About ON STATEMENT 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
- Refreshing Materialized Views Based on Approximate Queries
- About Concurrent Refresh of On-Commit Materialized Views
- About Refreshing Dependent Materialized Views During Online Table Redefinition
- Recommended Initialization Parameters for Parallelism
- Monitoring a Refresh
- Checking the Status of a Materialized View
- 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
- Refreshing Materialized Views Based on Hybrid Partitioned Tables
- Using Partitioning to Improve Data Warehouse Refresh
- Optimizing DML Operations During Refresh
- About Refreshing Materialized Views
- 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
- Using Synchronous Refresh Groups
- Specifying and Preparing Change Data for Synchronous Refresh
- 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
- Overview of Synchronous Refresh Security Considerations
- About Synchronous Refresh for Materialized Views
- Monitoring Materialized View Refresh Operations
- About Materialized View Refresh Statistics
- Overview of Managing Materialized View Refresh Statistics
- About Data Dictionary Views that Store Materialized View Refresh Statistics
- Collecting Materialized View Refresh Statistics
- Retaining Materialized View Refresh Statistics
- Viewing Materialized View Refresh Statistics Settings
- Purging Materialized View Refresh Statistics
- Viewing Materialized View Refresh Statistics
- Analyzing Materialized View Refresh Performance Using Refresh Statistics
- Dimensions
- Basic Query Rewrite for Materialized Views
- Advanced Query Rewrite for Materialized Views
- How Oracle Rewrites Queries
- 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
- Partition Change Tracking (PCT) Rewrite
- 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
- About Query Rewrite in the Presence of Hybrid Partitioned Tables
- Query Rewrite Using Set Operator Materialized Views
- About Query Rewrite in the Presence of Grouping Sets
- Query Rewrite in the Presence of Window Functions
- Query Rewrite and Expression Matching
- Bind Variables During Query Rewrite
- Handling Expressions in Query Rewrite
- Advanced Query Rewrite Using Equivalences
- Creating Result Cache Materialized Views with Equivalences
- Query Rewrite and Materialized Views Based on Approximate Queries
- Query Rewrite and Materialized Views Based on Bitmap-based COUNT(DISTINCT) Functions
- 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
- About Semi-Join Materialized View Rewrite
- Working With Automatic Materialized Views
- Attribute Clustering
- About Attribute Clustering
- Attribute Clustering Operations
- Privileges for Attribute-Clustered Tables
- Creating Attribute-Clustered Tables with Linear Ordering
- Creating Attribute-Clustered Tables 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
- About Automatic Data Clustering
- Using Zone Maps
- About Zone Maps
- Zone Map Operations
- Privileges Required for Zone Maps
- Creating Zone Maps
- About Automatic Zone Maps
- About the DBMS_AUTO_ZONEMAP Package
- Modifying Zone Maps
- Dropping Zone Maps
- Compiling Zone Maps
- Controlling the Use of Zone Maps
- Maintaining Zone Maps
- Refresh and Staleness of Zone Maps
- Performing Pruning Using Zone Maps
- Viewing Zone Map Information
- Basic Materialized Views
- Part III Data Movement/ETL
- Data Movement/ETL Overview
- Extraction in Data Warehouses
- Transportation in Data Warehouses
- Loading and Transformation in Data Warehouses
- Part IV Relational Analytics
- SQL for Analysis and Reporting
- Overview of SQL for Analysis and Reporting
- Ranking, Windowing, and Reporting Functions
- Ranking Functions
- Windowing Functions
- Examples of Window Clauses
- Examples of Windowing Clause Extensions
- 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
- Parallel Partition-Wise Operations with Windowing Functions
- QUALIFY Clause
- Reporting Functions
- LAG/LEAD Functions
- FIRST_VALUE, LAST_VALUE, and NTH_VALUE Functions
- Advanced Aggregates for Analysis
- Pivoting Operations
- Unpivoting Operations
- Data Densification for Reporting
- Time Series Calculations on Densified Data
- Miscellaneous Analysis and Reporting Capabilities
- Limiting SQL Rows
- SQL for Aggregation in Data Warehouses
- Overview of SQL for Aggregation in Data Warehouses
- ROLLUP Extension to GROUP BY
- CUBE Extension to GROUP BY
- GROUPING Functions
- GROUPING SETS Expression
- About Composite Columns and Grouping
- Concatenated Groupings and Data Aggregation
- Considerations when Using Aggregation in Data Warehouses
- Computation Using the WITH Clause
- Working with Hierarchical Cubes in SQL
- SQL for Pattern Matching
- Overview of Pattern Matching in Data Warehouses
- Basic Topics in Pattern Matching
- 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
- 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
- Row Pattern Output
- Advanced Topics in Pattern Matching
- Rules and Restrictions in Pattern Matching
- Examples of Pattern Matching
- Fuzzy String Matching
- SQL for Modeling
- Overview of SQL Modeling in Data Warehouses
- Basic Topics in SQL Modeling
- Base Schema for SQL Modeling Examples
- MODEL Clause Syntax
- Keywords in SQL Modeling
- About Cell Referencing in SQL Modeling
- 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
- Treatment of NULLs and Missing Cells in SQL Modeling
- About Reference Models in SQL Modeling
- Advanced Topics in SQL Modeling
- Performance Considerations with SQL Modeling
- 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
- 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
- Examples of Business Intelligence Queries
- SQL for Analysis and Reporting
- Part V Analytic Views
- Overview of Analytic Views
- What Are Analytic Views?
- New Features for Analytic Views
- Privileges for Analytic Views
- Application Programming Interfaces for Analytic Views
- Compilation States of Analytic Views
- Validation of Data
- Classifications for Analytic Views
- Share Analytic Views with Application Containers
- Alter or Drop an Analytic View Object
- Data and Scripts for Examples
- Attribute Dimension and Hierarchy Objects
- Analytic View Objects
- Overview of Analytic Views
- Glossary