Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Database?

• Oracle Database 11g Release 1 (11.1) New Features in Data Warehousing
• Oracle Database 10g Release 2 (10.2) 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
• Extracting Information from a Data Warehouse
  • Data Mining
    • Oracle Data Mining Functionality
    • Oracle Data Mining Interfaces

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 Data Warehousing Schemas
• Data Warehousing Objects
  • Data Warehousing Objects: Fact Tables
    • Requirements of Fact Tables
  • Data Warehousing Objects: Dimension Tables
    • Hierarchies
    • Typical Dimension Hierarchy
  • Data Warehousing Objects: Unique Identifiers
  • Data Warehousing Objects: 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

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 Analytic Processing Environments
  • Cubes
  • Benefits of Partitioning Materialized Views
  • Compressing Materialized Views
  • 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
    • Viewing Partition Freshness
  • 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
  • Tracking Changes to the CDC Environment
• 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 Synchronous Change Sets
    • Enabling and Disabling Synchronous Change Sets
  • Managing Change Tables
    • Creating Change Tables
    • Understanding Change Table Control Columns
    • Understanding TARGET_COLMAP$ and SOURCE_COLMAP$ Values
    • Using Change Markers
    • 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 11.1
    • 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 Using Remote Tables
  • 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
• Pivoting Operations
  • Example: Pivoting
  • Pivoting on Multiple Columns
  • Pivoting: Multiple Aggregates
  • Distinguishing PIVOT-Generated Nulls from Nulls in Source Data
  • Unpivoting Operations
  • Wildcard and Subquery Pivoting with XML Operations
• Other Analytic Functionality
  • Linear Algebra
  • Frequent Itemsets
  • 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
  • OLAP Technology in the Oracle Database
    • Full Integration of Multidimensional Technology
    • Ease of Application Development
    • Ease of Administration
    • Security
    • Unmatched Performance and Scalability
    • Reduced Costs
  • Querying Dimensional Objects
  • Tools for Creating and Managing Dimensional Objects

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/Consumer 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
    • Locks 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 Database 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

Glossary

Index