Oracle Physical Data Warehouse Design and Performance

4 days


This is an intermediate to advanced level course. After discussing the components of the data warehouse and data warehousing, the course elaborates on the essential physical design and phases of an Oracle data warehouse (DW). Particular emphasis is placed on the following:

  • DW partitioning
  • Index options (e.g., bitmaps)
  • Integrity and constraints
  • Materialized views (summary tables)
  • DW objects (dimensions, hierarchies, etc...)
  • The ETL process
  • Performance and good practices

Delegates will:

  • Create and load their own DW
  • Create and implement indexes, bitmap and partitioned
  • Create and assess Materialized Views (MV)
  • Create dimensions to optimise MV rewrite
  • Incorporate various integrity (RI) options

Upon completion of this course, attendees should be able to translate logical DW design specifications into an Oracle physical design model; and in turn, create the necessary scripts (e.g., DDL) to install a large scale partitioned DW, using, amongst other things, bitmap indexes.


This course is intended for database administrators and system administrators.


Delegates should have a working knowledge of the Oracle database, its architecture, objects and SQL.

This course is available on site only. Please call for details.

Course Outline

  • Data Warehousing (DW) Terms And Concepts (Optional)
  • The DW environment
  • What is a data warehouse?
  • What is a data mart?
  • What is Business Intelligence (BI)?
  • How do OLTP & OLAP differ?
  • What is data mining?
  • Operational vs. historical data
  • What is a star schema?
  • What is a snowflake schema?
  • Normalization vs. denormalization
  • What are hierarchies?
  • What is dimensional modelling?
  • What is the Data Warehouse Bus Architecture (DWB)?
  • What are surrogate keys?
  • What is Extract, Transform, Load (ETL)?
  • What are Slowly Changing Dimensions (SCD)?
  • What is Metadata?
  • What Materialized Views (MV)?
  • How does logical design differ from physical design?
  • NOTE:
  • Oracle Database Architecture – A Summary
  • Memory structure
  • Logical storage structure
  • Physical storage structure
  • Processes
  • SQL tools and extensions
  • Admin tools
  • Real Application Cluster
  • Logical to Physical Design Process
  • DW database objects (e.g., partitions, dimensions, materialized views)
  • Hardware and I/O considerations
  • DW Partitioning Design
  • Parallelism options
  • Partitioning options
  • Compression options
  • Partitioning and join considerations
  • NULL considerations
  • Date data type considerations
  • Composite (multi-key) considerations
  • Local vs. global indexes
  • Backup and recovery considerations
  • DW Index Design Options
  • B*tree index – how they work
  • Bitmapped index – how they work
  • Bitmapped join indexes – how they work
  • NULL value considerations
  • Partitioning indexes
  • Index-organised tables
  • Join Options
  • Hash
  • Lossless
  • Nested loop
  • Partition-wise
  • Sort merge
  • Star transformation
  • DW Constraint Design
  • Constraint types (unique, data cleanliness, optimisation, etc...)
  • Constraints and partitioning
  • Views and constraints
  • Materialialized Views (i.e., Aggregate/Summary Tables)
  • When to consider
  • Oracle's summary managment feature
  • How to create materialized views
  • Physical storage considerations
  • Populating (loading) options
  • Refresh options
  • Query rewrite design
  • Query rewrite and integrity (e.g., ENFORCED, TRUSTED)
  • How to use the Oracle Summary Advisor
  • EXPLAIN MVIEW utility usage
  • Dimensions
  • Definition and role
  • Hierarchies – types
  • When to create dimensions
  • How to create dimension
  • Dimension with attributes
  • Normalized dimensions
  • Validating dimensions
  • Altering dimensions
  • Dimensions and constraints
  • Deleting dimensions
  • The ETL Process
  • Overview
  • Extract
  • Extraction Types
  • Data Extraction Techniques
  • Data Cleansing
  • Data Cleansing Techniques
  • Data Transformation
  • Delivery
  • Data Load Options
  • Surrogate Keys
  • ETL Sub-task Summary
  • ETL Vendor Considerations
  • ETL in the Database
  • Oracle's ETL Offerings
  • Extraction from operational systems
  • Changed data capture
  • Data transformation (e.g., data integration & cleansing)
  • Generating keys
  • Loading the DW – options
  • Oracle DW Tools
  • Warehouse Builder
  • Discover
  • Reports
  • OLAP and data mining
  • Oracle DW Performance Considerations
  • ETL and Table Functions
  • How to optimise bitmap and bitmap join indexes
  • How to optimise bitmap star join transformations
  • Placing Oracle objects (e.g., indexes) into separate buffers, Oracle 9i enhancement of multiple block sizes
  • How to use Oracle 9i's dynamic SGA feature
  • How to use dynamic sampling
  • Materialized views and FAST REFRESH
  • Materialized view 'size' control
  • Indexes
  • Partitioning
  • Don't forget: dbms_stats, OPTIMIZER_MODE
  • Star vs. snowflake schemas
  • Parallelism and Performance
  • Introduction
  • Parallelisable operation
  • How parallelism works
  • Parallelising SQL (DDL)
  • Parallelising SQL (DML)
  • Degree of parallelism
  • Prameter setting for parallelism
  • Oracle DW Changes and Enhancements – A Glance
  • Optimiser hints
  • Multi-CPU exploitation (e.g., table scans, REORGs, etc...)
  • Materialized views and auto-rewrite
  • Materialized views and dbms_advisor
  • Automated Workload Repository (AWR)
  • Multiple blocksizes/buffer management
  • Star join query optimisation
  • Oracle streams (i.e., near real-time data warehousing)
  • Read-only table spaces
  • Automatic Storage Management (ASM)
  • Oracle Warehouse Builder vs. scripts
  • Asynchronous Change Data Capture (CDC)
  • Oracle Data Pump (e.g., high speed bulk data and metadata movement)

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

Upcoming Dates

  • GREEN This class is Guaranteed To Run.
  • SPVC - Self-Paced Virtual Class.
  • Click a Date to Enroll.
Course Location Days Cost Date
Onsite4 2800 2800 2019-07-18