1/36

Contents

Title and Copyright Information

Preface

• Intended Audience
• Documentation Accessibility
• Organization
• Related Documentation
• Conventions

What's New in Oracle Database?

• Oracle Database 10g Release 2 (10.2) New Features in Data Warehousing
• Oracle Database 10g Release 1 (10.1) New Features 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
  • • Requirements of Fact Tables
  • Dimension Tables
  • • Hierarchies
    • Typical Dimension Hierarchy
  • 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
  • • Table Compression
  • 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
• • Configure I/O for Bandwidth not Capacity
  • Stripe Far and Wide
  • Use Redundancy
  • Test the I/O System Before Building the Database
  • Plan for Growth
• Storage Management

5 Partitioning in Data Warehouses

• Types of Partitioning
• • Partitioning Methods
  • • Range Partitioning
    • Hash Partitioning
    • List Partitioning
    • Composite Partitioning
  • Index Partitioning
  • Performance Issues for Range, List, Hash, and Composite Partitioning
  • • When to Use Range Partitioning
    • When to Use Hash Partitioning
    • When to Use List Partitioning
    • When to Use Composite Range-Hash Partitioning
    • Using Composite Range-Hash Partitioning
    • When to Use Composite Range-List Partitioning
    • Using Composite Range-List Partitioning
  • Partitioning and Table Compression
  • • Table Compression and Bitmap Indexes
    • Example of Table Compression and Partitioning
  • Partition Pruning
  • • Information that can be Used for Partition Pruning
    • How to Identify Whether Partition Pruning has been Used
    • Static Partition Pruning
    • Dynamic Partition Pruning
    • Basic Partition Pruning Techniques
    • Advanced Partition Pruning Techniques
    • Partition Pruning Tips
• Partition-Wise Joins
• • Full Partition-Wise Joins
  • • Full Partition-Wise Joins: Hash-Hash
    • Full Partition-Wise Joins: Hash-Hash
    • Full Partition-Wise Joins: (Composite-Hash)-Hash
    • Full Partition-Wise Joins: (Composite-List)-List
    • Full Partition-Wise Joins: (Composite-Composite (Hash/List Dimension)
    • Full Partition-Wise Joins: Range-Range and List-List
    • Full Partition-Wise Joins: Range-Composite, Composite-Composite (Range Dimension)
  • Partial Partition-Wise Joins
  • • Partial Partition-Wise Joins: Hash-List
    • Partial Partition-Wise Joins: Composite
    • Partial Partition-Wise Joins: Range
  • Benefits of Partition-Wise Joins
  • • Reduction of Communications Overhead
    • Reduction of Memory Requirements
  • Performance Considerations for Parallel Partition-Wise Joins
• Partition Maintenance
• Partitioning and Subpartitioning Columns and Keys
• Partition Bounds for Range Partitioning
• • Comparing Partitioning Keys with Partition Bounds
  • • MAXVALUE
    • Nulls
    • DATE Datatypes
    • Multicolumn Partitioning Keys
    • Implicit Constraints Imposed by Partition Bounds
• Index Partitioning
• • Local Partitioned Indexes
  • • Local Prefixed Indexes
    • Local Nonprefixed Indexes
  • Global Partitioned Indexes
  • • Prefixed and Nonprefixed Global Partitioned Indexes
    • Management of Global Partitioned Indexes
  • Summary of Partitioned Index Types
  • The Importance of Nonprefixed Indexes
  • Performance Implications of Prefixed and Nonprefixed Indexes
  • Guidelines for Partitioning Indexes
  • Physical Attributes of Index Partitions

6 Indexes

• Using Bitmap Indexes in Data Warehouses
• • Benefits for Data Warehousing Applications
  • Cardinality
  • • How to Determine Candidates for Using a Bitmap Index
  • Bitmap Indexes and Nulls
  • Bitmap Indexes on Partitioned Tables
  • Using Bitmap Join Indexes in Data Warehouses
  • • Four Join Models for Bitmap Join Indexes
    • Bitmap Join Index Restrictions and Requirements
• Using B-Tree Indexes in Data Warehouses
• Using Index Compression
• Choosing Between Local Indexes and 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
  • NOT NULL Constraints
  • Integrity Constraints and Parallelism
  • Integrity Constraints and Partitioning
  • View Constraints

8 Basic 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
  • • Schemas and Dimension Tables
    • Materialized View Schema Design Guidelines
  • Loading Data into Data Warehouses
  • Overview of Materialized View Management Tasks
• Types of Materialized Views
• • Materialized Views with Aggregates
  • • Requirements for Using Materialized Views with Aggregates
  • Materialized Views Containing Only Joins
  • • Materialized Join Views FROM Clause Considerations
  • Nested Materialized Views
  • • Why Use Nested Materialized Views?
    • 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
  • Naming Materialized Views
  • Storage And Table Compression
  • Build Methods
  • Enabling Query Rewrite
  • Query Rewrite Restrictions
  • • Materialized View Restrictions
    • General Query Rewrite Restrictions
  • Refresh Options
  • • 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
    • Achieving Refresh Goals
    • Refreshing Nested Materialized Views
  • ORDER BY Clause
  • Materialized View Logs
  • • Using the FORCE Option with Materialized View Logs
  • Using Oracle Enterprise Manager
  • Using Materialized Views with NLS Parameters
  • Adding Comments to Materialized Views
• 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

9 Advanced Materialized Views

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

10 Dimensions

• What are Dimensions?
• Creating Dimensions
• • Dropping and Creating Attributes with Columns
  • Multiple Hierarchies
  • Using Normalized Dimension Tables
• Viewing Dimensions
• • Using Oracle Enterprise Manager
  • Using the DESCRIBE_DIMENSION Procedure
• Using Dimensions with Constraints
• Validating Dimensions
• Altering Dimensions
• Deleting Dimensions

Part IV Managing the Data Warehouse Environment

11 Overview of Extraction, Transformation, and Loading

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

12 Extraction in Data Warehouses

• Overview of Extraction in Data Warehouses
• Introduction to Extraction Methods in Data Warehouses
• • Logical Extraction Methods
  • • Full Extraction
    • Incremental Extraction
  • Physical Extraction Methods
  • • Online Extraction
    • Offline Extraction
  • Change Data Capture
  • • Timestamps
    • Partitioning
    • Triggers
• 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

13 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
  • • Transportable Tablespaces Example
    • Other Uses of Transportable Tablespaces

14 Loading and Transformation

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

15 Maintaining the Data Warehouse

• Using Partitioning to Improve Data Warehouse Refresh
• • Refresh Scenarios
  • Scenarios for Using Partitioning for Refreshing Data Warehouses
  • • Refresh Scenario 1
    • Refresh Scenario 2
• Optimizing DML Operations During Refresh
• • Implementing an Efficient MERGE Operation
  • Maintaining Referential Integrity
  • Purging Data
• Refreshing Materialized Views
• • Complete Refresh
  • Fast Refresh
  • Partition Change Tracking (PCT) 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 Fast Refresh is Possible
  • Recommended Initialization Parameters for Parallelism
  • Monitoring a Refresh
  • Checking the Status of a Materialized View
  • Scheduling Refresh
  • 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
  • • PCT Fast Refresh Scenario 1
    • PCT Fast Refresh Scenario 2
    • PCT Fast Refresh Scenario 3
  • Fast Refresh with CONSIDER FRESH

16 Change Data Capture

• Overview of Change Data Capture
• • Capturing Change Data Without Change Data Capture
  • Capturing Change Data with Change Data Capture
  • Publish and Subscribe Model
  • • Publisher
    • Subscribers
• Change Sources and Modes of Change Data Capture
• • Synchronous Change Data Capture
  • Asynchronous Change Data Capture
  • • Asynchronous HotLog Mode
    • Asynchronous Distributed HotLog Mode
    • Asynchronous AutoLog Mode
• Change Sets
• • Valid Combinations of Change Sources and Change Sets
• Change Tables
• Getting Information About the Change Data Capture Environment
• Preparing to Publish Change Data
• • Creating a User to Serve As a Publisher
  • • Granting Privileges and Roles to the Publisher
    • Creating a Default Tablespace for the Publisher
    • Password Files and Setting the REMOTE_LOGIN_PASSWORDFILE Parameter
  • Determining the Mode in Which to Capture Data
  • Setting Initialization Parameters for Change Data Capture Publishing
  • • Initialization Parameters for Synchronous Publishing
    • Initialization Parameters for Asynchronous HotLog Publishing
    • Initialization Parameters for Asynchronous Distributed HotLog Publishing
    • Initialization Parameters for Asynchronous AutoLog Publishing
  • Adjusting Initialization Parameter Values When Oracle Streams Values Change
  • • Adjustments for HotLog and AutoLog Change Data Capture
    • Adjustments for Distributed HotLog Change Data Capture
• Publishing Change Data
• • Performing Synchronous Publishing
  • Performing Asynchronous HotLog Publishing
  • Performing Asynchronous Distributed HotLog Publishing
  • Performing Asynchronous AutoLog Publishing
• Subscribing to Change Data
• Managing Published Data
• • Managing Asynchronous Change Sources
  • • Enabling And Disabling Asynchronous Distributed HotLog Change Sources
  • Managing Asynchronous Change Sets
  • • Creating Asynchronous Change Sets with Starting and Ending Dates
    • Enabling and Disabling Asynchronous Change Sets
    • Stopping Capture on DDL for Asynchronous Change Sets
    • Recovering from Errors Returned on Asynchronous Change Sets
  • Managing Change Tables
  • • Creating Change Tables
    • Understanding Change Table Control Columns
    • Understanding TARGET_COLMAP$ and SOURCE_COLMAP$ Values
    • Controlling Subscriber Access to Change Tables
    • Purging Change Tables of Unneeded Data
    • Dropping Change Tables
  • Exporting and Importing Change Data Capture Objects Using Oracle Data Pump
  • • Restrictions on Using Oracle Data Pump with Change Data Capture
    • Examples of Oracle Data Pump Export and Import Commands
    • Publisher Considerations for Exporting and Importing Change Tables
    • Re-Creating AutoLog Change Data Capture Objects After an Import Operation
  • Impact on Subscriptions When the Publisher Makes Changes
• Considerations for Synchronous Change Data Capture
• • Restriction on Direct-Path INSERT
  • Datatypes and Table Structures Supported for Synchronous Change Data Capture
  • Limitation on Restoring Source Tables from the Recycle Bin
• Considerations for Asynchronous Change Data Capture
• • Asynchronous Change Data Capture and Redo Log Files
  • Asynchronous Change Data Capture and Supplemental Logging
  • Asynchronous Change Data Capture and Oracle Streams Components
  • Datatypes and Table Structures Supported for Asynchronous Change Data Capture
  • Restrictions for NOLOGGING and UNRECOVERABLE Operations
• Implementation and System Configuration
• • Database Configuration Assistant Considerations
  • Summary of Supported Distributed HotLog Configurations and Restrictions
  • • Oracle Database Releases for Source and Staging Databases
    • Upgrading a Distributed HotLog Change Source to Oracle Release 10.2
    • Hardware Platforms and Operating Systems
    • Requirements for Multiple Publishers on the Staging Database
    • Requirements for Database Links

Part V Data Warehouse Performance

17 Basic Query Rewrite

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

18 Advanced Query Rewrite

• How Oracle Rewrites Queries
• • Cost-Based Optimization
  • General Query Rewrite Methods
  • • When are Constraints and Dimensions Needed?
  • Checks Made by Query Rewrite
  • • Join Compatibility Check
    • Data Sufficiency Check
    • Grouping Compatibility Check
    • Aggregate Computability Check
  • Rewrite Using Dimensions
  • • Benefits of Using Dimensions
    • How to Define Dimensions
• Types of Query Rewrite
• • Text Match Rewrite
  • Join Back
  • Aggregate Computability
  • Aggregate Rollup
  • Rollup Using a Dimension
  • When Materialized Views Have Only a Subset of Data
  • • Query Rewrite Definitions
    • Selection Categories
    • Examples of Query Rewrite Selection
    • Handling of the HAVING Clause in Query Rewrite
    • 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
  • Multiple Materialized Views
• Other Query Rewrite Considerations
• • Query Rewrite Using Nested Materialized Views
  • Query Rewrite in the Presence of Inline Views
  • Query Rewrite in the Presence of Duplicate Tables
  • Query Rewrite Using Date Folding
  • Query Rewrite Using View Constraints
  • • View Constraints Restrictions
  • Query Rewrite Using Set Operator Materialized Views
  • • UNION ALL Marker
  • Query Rewrite in the Presence of Grouping Sets
  • • Query Rewrite When Using GROUP BY Extensions
    • Hint for 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
  • Handling Expressions in Query Rewrite
• Advanced Query Rewrite Using 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
    • Using a Varray
    • EXPLAIN_REWRITE Benefit Statistics
    • Support for Query Text Larger than 32KB in EXPLAIN_REWRITE
    • EXPLAIN_REWRITE and Multiple Materialized Views
    • 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
  • • REWRITE and NOREWRITE Hints
    • REWRITE_OR_ERROR Hint
    • Multiple Materialized View Rewrite Hints
    • EXPAND_GSET_TO_UNION Hint

19 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
  • • 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

20 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
• • GROUPING SETS Syntax
• 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
• 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

21 SQL for Analysis and Reporting

• Overview of SQL for Analysis and Reporting
• Ranking Functions
• • RANK and DENSE_RANK Functions
  • • Ranking Order
    • Ranking on Multiple Expressions
    • RANK and DENSE_RANK Difference
    • Per Group Ranking
    • Per Cube and Rollup Group Ranking
    • Treatment of NULLs
  • Bottom N Ranking
  • CUME_DIST Function
  • PERCENT_RANK Function
  • NTILE Function
  • ROW_NUMBER Function
• Windowing Aggregate Functions
• • 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
  • FIRST_VALUE and LAST_VALUE Functions
• Reporting Aggregate Functions
• • RATIO_TO_REPORT Function
• LAG/LEAD Functions
• • LAG/LEAD Syntax
• FIRST/LAST Functions
• • FIRST/LAST Syntax
  • FIRST/LAST As Regular Aggregates
  • FIRST/LAST As Reporting Aggregates
• Inverse Percentile Functions
• • Normal Aggregate Syntax
  • • Inverse Percentile Example Basis
    • As Reporting Aggregates
  • Inverse Percentile Restrictions
• Hypothetical Rank and Distribution Functions
• • Hypothetical Rank and Distribution Syntax
• 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
• Linear Algebra
• Frequent Itemsets
• Other Statistical Functions
• • Descriptive Statistics
  • Hypothesis Testing - Parametric Tests
  • Crosstab Statistics
  • Hypothesis Testing - Non-Parametric Tests
  • Non-Parametric Correlation
• WIDTH_BUCKET Function
• • WIDTH_BUCKET Syntax
• User-Defined Aggregate Functions
• CASE Expressions
• • Creating Histograms With User-Defined Buckets
• Data Densification for Reporting
• • 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

22 SQL for Modeling

• Overview of SQL Modeling
• • How Data is Processed in a SQL Model
  • Why Use SQL Modeling?
  • SQL Modeling Capabilities
• Basic Topics in SQL Modeling
• • Base Schema
  • MODEL Clause Syntax
  • Keywords in SQL Modeling
  • • Assigning Values and Null Handling
    • Calculation Definition
  • Cell Referencing
  • • Symbolic Dimension References
    • Positional Dimension References
  • Rules
  • • Single Cell References
    • Multi-Cell References on the Right Side
    • Multi-Cell References on the Left Side
    • Use of the CV Function
    • Use of the ANY Wildcard
    • Nested Cell References
  • Order of Evaluation of Rules
  • Global and Local Keywords for Rules
  • UPDATE, UPSERT, and UPSERT ALL Behavior
  • • UPDATE Behavior
    • UPSERT Behavior
    • UPSERT ALL Behavior
  • Treatment of NULLs and Missing Cells
  • • Distinguishing Missing Cells from NULLs
    • Use Defaults for Missing Cells and NULLs
    • Using NULLs in a Cell Reference
  • Reference Models
• Advanced Topics in SQL Modeling
• • FOR Loops
  • • Evaluation of Formulas with FOR Loops
  • Iterative Models
  • Rule Dependency in AUTOMATIC ORDER Models
  • Ordered Rules
  • Analytic Functions
  • Unique Dimensions Versus Unique Single References
  • Rules and Restrictions when Using SQL for Modeling
• Performance Considerations with SQL Modeling
• • Parallel Execution
  • Aggregate Computation
  • Using EXPLAIN PLAN to Understand Model Queries
  • • Using ORDERED FAST: Example
    • Using ORDERED: Example
    • Using ACYCLIC FAST: Example
    • Using ACYCLIC: Example
    • Using CYCLIC: Example
• Examples of SQL Modeling

23 OLAP and Data Mining

• OLAP and Data Mining Comparison
• OLAP Overview
• • Benefits of OLAP and RDBMS Integration
  • • Scalability
    • Availability
    • Manageability
    • Backup and Recovery
    • Security
  • The OLAP Option
  • Tools for Querying OLAP Data Stores
  • Tools for Developing OLAP Applications
  • Tools for Administration
• Oracle Data Mining Overview
• • Benefits of Data Mining in the Database
  • Data Mining Functionality
  • Oracle Data Mining Interfaces
  • • Programmatic Interfaces
    • Graphical User Interface
  • Data Mining in the Life Sciences
  • Oracle Data Mining Documentation

24 Advanced Business Intelligence Queries

• Examples of Business Intelligence Queries

25 Using Parallel Execution

• Introduction to Parallel Execution Tuning
• • When to Implement Parallel Execution
  • When Not to Implement Parallel Execution
  • Operations That Can Be Parallelized
• How Parallel Execution Works
• • Degree of Parallelism
  • The Parallel Execution Server Pool
  • • Variations in the Number of Parallel Execution Servers
    • Processing Without Enough Parallel Execution Servers
  • How Parallel Execution Servers Communicate
  • Parallelizing SQL Statements
  • • Dividing Work Among Parallel Execution Servers
    • Parallelism Between Operations
    • Producer Operations
  • Granules of Parallelism
  • • Block Range Granules
    • Partition Granules
• Types of Parallelism
• • Parallel Query
  • • Parallel Queries on Index-Organized Tables
    • Nonpartitioned Index-Organized Tables
    • Partitioned Index-Organized Tables
    • Parallel Queries on Object Types
  • Parallel DDL
  • • DDL Statements That Can Be Parallelized
    • CREATE TABLE ... AS SELECT in Parallel
    • Recoverability and Parallel DDL
    • Space Management for Parallel DDL
    • Storage Space When Using Dictionary-Managed Tablespaces
    • Free Space and Parallel DDL
  • Parallel DML
  • • Advantages of Parallel DML over Manual Parallelism
    • When to Use Parallel DML
    • Enabling Parallel DML
    • Transaction Restrictions for Parallel DML
    • Rollback Segments
    • Recovery for Parallel DML
    • Space Considerations for Parallel DML
    • Lock and Enqueue Resources for Parallel DML
    • Restrictions on Parallel DML
    • Data Integrity Restrictions
    • Trigger Restrictions
    • Distributed Transaction Restrictions
    • Examples of Distributed Transaction Parallelization
  • Parallel Execution of Functions
  • • Functions in Parallel Queries
    • Functions in Parallel DML and DDL Statements
  • Other Types of Parallelism
• Initializing and Tuning Parameters for Parallel Execution
• • Using Default Parameter Settings
  • Setting the Degree of Parallelism for Parallel Execution
  • How Oracle Determines the Degree of Parallelism for Operations
  • • Hints and Degree of Parallelism
    • Table and Index Definitions
    • Default Degree of Parallelism
    • Adaptive Multiuser Algorithm
    • Minimum Number of Parallel Execution Servers
    • Limiting the Number of Available Instances
  • Balancing the Workload
  • Parallelization Rules for SQL Statements
  • • Rules for Parallelizing Queries
    • Rules for UPDATE, MERGE, and DELETE
    • Rules for INSERT ... SELECT
    • Rules for DDL Statements
    • Rules for [CREATE | REBUILD] INDEX or [MOVE | SPLIT] PARTITION
    • Rules for CREATE TABLE AS SELECT
    • Summary of Parallelization Rules
  • Enabling Parallelism for Tables and Queries
  • Degree of Parallelism and Adaptive Multiuser: How They Interact
  • • How the Adaptive Multiuser Algorithm Works
  • 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
  • • PARALLEL_MAX_SERVERS
    • Increasing the Number of Concurrent Users
    • Limiting the Number of Resources for a User
    • PARALLEL_MIN_SERVERS
    • SHARED_POOL_SIZE
    • Computing Additional Memory Requirements for Message Buffers
    • Adjusting Memory After Processing Begins
    • PARALLEL_MIN_PERCENT
  • Parameters Affecting Resource Consumption
  • • PGA_AGGREGATE_TARGET
    • PARALLEL_EXECUTION_MESSAGE_SIZE
    • Parameters Affecting Resource Consumption for Parallel DML and Parallel DDL
  • Parameters Related to I/O
  • • DB_CACHE_SIZE
    • DB_BLOCK_SIZE
    • DB_FILE_MULTIBLOCK_READ_COUNT
    • DISK_ASYNCH_IO and TAPE_ASYNCH_IO
• 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
  • • V$PX_BUFFER_ADVICE
    • V$PX_SESSION
    • V$PX_SESSTAT
    • V$PX_PROCESS
    • V$PX_PROCESS_SYSSTAT
    • V$PQ_SESSTAT
    • V$FILESTAT
    • V$PARAMETER
    • V$PQ_TQSTAT
    • V$SESSTAT and V$SYSSTAT
  • 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
  • • Size of Temporary Extents
  • Executing Parallel SQL Statements
  • Using EXPLAIN PLAN to Show Parallel Operations Plans
  • Additional Considerations for Parallel DML
  • • PDML and Direct-Path Restrictions
    • Limitation on the Degree of Parallelism
    • Using Local and Global Striping
    • Increasing INITRANS
    • Limitation on Available Number of Transaction Free Lists for Segments
    • Using Multiple Archivers
    • Database Writer Process (DBWn) Workload
    • [NO]LOGGING Clause
  • Creating Indexes in Parallel
  • Parallel DML Tips
  • • Parallel DML Tip 1: INSERT
    • Parallel DML Tip 2: Direct-Path INSERT
    • Parallel DML Tip 3: Parallelizing INSERT, MERGE, UPDATE, and DELETE
  • Incremental Data Loading in Parallel
  • • Updating the Table in Parallel
    • Inserting the New Rows into the Table in Parallel
    • Merging in Parallel
  • Using Hints with Query Optimization
  • FIRST_ROWS(n) Hint
  • Enabling Dynamic Sampling

Glossary

Index