This course will help you understand the basic concepts of administering a data warehouse. You'll learn to use various Oracle Database features to improve performance and manageability in a data warehouse.
Learn To:
- Implement partitioning.
- Use parallel operations to reduce response time.
- Extract, transform and load data.
- Create, use and refresh materialized views to improve data warehouse performance.
- Use query rewrite to quickly answer business queries using materialized views.
- Use SQL access advisor and PL/SQL procedures to tune materialized views for fast refresh and query rewrites.
- Identify the benefits of partitioning, in addition to using parallel operations to reduce response time for data-intensive operations.
Course Topics:
Introduction
- Development Tools
- Oracle SQL Developer
- Enterprise Manager
- Sample Schemas used
Data Warehouse Design: Overview
- Characteristics of a Data Warehouse
- Comparing OLTP and Data Warehouses
- Data Warehouse Architectures
- Data Warehouse Design
- Data Warehouse objects
- Data Warehouse Schemas
Data Warehouse Tuning Considerations
- Optimizing Star Queries
- Introducing Bitmap Join Indexes
- Understanding Star Query Optimization and Bitmap Joined Index Optimization
Partitioning Basics
- Partitioned Tables and Indexes
- Partitioning Methods
- Partitioning Types
- Partition Pruning and Star queries
Parallelism Concepts
- Operations That Can Be Parallelized
- How Parallel Execution Works
- Degree of Parallelism
- Parallel execution plan
- Automatice Parallelism
Parallel Operations in Data Warehouses
- Parallel Query
- Parallel DDL
- Parallel DML
- Tuning Parameters for Parallel Execution
- Balancing the Workload
ETL: Extraction and Transportation
- Extraction Methods
- Capturing Data With Change Data Capture
- Sources and Modes of Change Data Capture
- Publish and Subscribe Model: The Publisher and the Subscriber
- Synchronous and Asynchronous CDC
- Asynchronous AutoLog Mode and Asynchronous HotLog Mode
- Transportation in a Data Warehouse
- Transportable Tablespaces
ETL: Loading
- Loading Mechanisms
- Applications of External Tables
- Defining external tables with SQL*Loader
- Populating external tables with Data Pump
- Other Loading Methods
ETL: Transformation
- Data transformation
- Transformation Mechanisms
- Transformation Using SQL
- Table Functions
- DML error logging
Materialized Views
- The Need for Summary Management
- Types of Materialized Views
- Using Materialized Views for Summary Management
- Materialized View Dictionary views
Refreshing Materialized Views
- Refresh Options
- Refresh Modes
- Conditions That Effect Possibility of Fast Refresh
- Materialized View Logs
- Partition Change Tracking (PCT) Refresh
- Refresh Performance Improvements
Working With Dimensions
- What Are Dimensions
- Creating Dimensions and Hierarchies
- Dimensions and Privileges
- Dimension Restrictions
- Verifying Relationships in a Dimension
- Dimension Invalidation
Query Rewrite
- Query Rewrite: Overview
- What Can be Rewritten
- Conditions Required for Oracle to Rewrite a Query
- Query Rewrite guidelines
- Setting Initialization Parameters for Query Rewrite
- Query Rewrite Methods
- Partition Change Tracking (PCT) and Query Rewrite
- Query Rewrite Enhancement to Support Queries Containing Inline Views
Using the SQL Access Advisor, Compression, and Resumable Sessions
- SQL Access Advisor: Usage Model
- Setting Initial Options
- Specifying the Workload Source
- Recommendation Options
- Schedule and Review
- PL/SQL Procedure Flow
- Tuning Materialized Views for Fast Refresh and Query Rewrite
- Table Compression and Resumable Sessions
