Oracle Database 19c: Partitioning in Data Warehouse Live Class

Course Overview

• Lesson Objectives
• Introduce Yourself
• Lesson Agenda
• Course Objectives
• Lesson Agenda
• Course Persona
• Lesson Agenda
• Course Environment
• Sample Database Schema Used in the Course
• The Human Resources (HR) Schema
• The Sales History (SH) Schema
• Class Account Information
• SQL Environments Available in the Course
• Lesson Agenda
• Oracle Useful Documentation
• Resources to Bookmark
• Summary
• Practice 1: Overview

Partitioning Concepts

• Objectives
• Lesson Agenda
• Examine This Graph: What Do You Observe?
• Need for Very Large Databases (VLDBs)
• Challenges with VLDBs: Need for Partitioning
• What Is Partitioning?
• Lesson Agenda
• Benefits of Partitioning: Table Availability
• Benefits of Partitioning: Large-Table Manageability
• Benefits of Partitioning: Performance Considerations
• How Does Partitioning Work?
• Table Versus Index Partitioning
• Quiz
• Lesson Agenda
• Partitioning Methods: Single-level Partitioning
• Composite Partitioning
• Types of Composite Partitioning
• Lesson Agenda
• Partition Keys
• Partition Extensions
• Summary

Creating Partitioned Tables and Indexes

• Objectives
• Lesson Agenda
• The CREATE TABLE Statement with Partitioning
• Logical and Physical Attributes
• Partition Strategy Declaration: Single-Level Partitioning
• Specifying Partition Attributes
• Observe This Scenario
• Range Partition: DDL Example
• Hash Partitioning
• Hash Partition: DDL Example
• Observe This Scenario
• List Partition: DDL Example
• Observe This Scenario
• Interval Partitioning
• Interval Partitioning: Example
• System Partitioning
• System Partitioning: Example
• System Partitioning Guidelines
• Lesson Agenda
• Partition Strategy Declaration: Composite Partitioning
• The CREATE TABLE Statement with Composite Partitioning
• Composite Partitioning Using a Template
• Composite Partition: Range-Hash Example
• Composite Partitioning and Template: Example
• Partitioning External Tables
• Hybrid Partitioning
• Hybrid Partitioned Table: Example
• Multicolumn Partitioning
• Multicolumn Partitioning: Example
• Quiz
• Lesson Agenda
• Scenario: John Tries to Partition a Table
• Reference Partitioning
• Reference Partitioning: Benefit
• Reference Partitioning: Example
• Observe and Answer
• Virtual Column-Based Partitioning
• Virtual Column-Based Partitioning: DDL Example 1
• Virtual Column-Based Partitioning: Example 2
• Auto-List Partitioning: Example
• Partition-Extended Table Names
• Data Dictionary Views: Tables
• Quiz
• Lesson Agenda
• Partitioned Indexes
• Partitioned Index Attributes: Global or Local
• Partitioned Index Attributes: Prefixed or Nonprefixed
• Global Partitioned Index
• Global Partitioned Index: Example
• Local Prefixed Indexes
• Local Prefixed Index: Examples
• Local Nonprefixed Index
• Local Nonprefixed Index: Example
• Specifying an Index with Table Creation
• Data Dictionary Views: Indexes
• Guidelines for Partitioning Indexes
• Quiz
• Summary
• Practice 3: Overview

Partitioning for Performance in a Data Warehouse

• John Learns About Partitioning Performance in a Data Warehouse
• Objectives
• Lesson Agenda
• Examine This Graph: What Do You Observe?
• Data Volume in a Data Warehouse Grows Multifold Over Years
• Examine this Scenario
• Partition Pruning
• How to Identify Partition Pruning in an Execution Plan?
• Partition Pruning Types
• Static Partition Pruning
• Static Partition Pruning: Example
• Static Partition Pruning Execution Plan: Example
• Dynamic Partition Pruning
• Dynamic Partition Pruning: Example
• Partition Pruning Rules
• Partition Pruning with Range Partition: Example 1
• Partition Pruning with Partition Range Iterator: Example 2
• Partition Pruning with INLIST Operator: Example 3
• Partition Pruning in Hash Partitioned Table: Example 4
• Partition Pruning in Hash Partitioned Table: Example 5
• Partition Pruning in Hash Partitioned Table: Example 6
• Partition Pruning in Hash Partitioned Table: Example 7
• Partition Pruning in Composite Partitioned Tables
• Partition Pruning in Range Hash Partitioned Table: Example 8
• Partition Pruning in Range Hash Partitioned Table: Example 9
• Partition Pruning in Range Hash Partitioned Table: Example 10
• Global Partitioned Index: Partition Pruning Example 11
• Partition Pruning with Local Index: Example 12
• Partition Pruning for DML Commands: Example 13
• Partition Pruning for DDL Commands: Example 14
• Partition Pruning Tips
• Lesson Agenda
• Equipartitioning
• Partition-Wise Joins
• Full Partition-Wise Joins
• Partial Partition-Wise Joins
• Quiz
• Summary
• Practice 4: Overview