Oracle Database 11g: Implement Partitioning Release 2

Course DateLocationDaysPriceRegister
Call for Classroom Training Dates2 $1,930Register

Course DateLocationDaysPriceRegister
Call for Live Virtual Class Dates2 $1,840Register

Oracle Database 11g: Implement Partitioning Release 2

Description: This course will discuss the Oracle Partitioning methods for tables, index, and materialized views that are available in Oracle Database 11g Release 2 with examples and explanations of appropriate use. Oracle Database 11g introduces several new partitioning methods: Reference partitioning, System partitioning, Interval partitioning, Virtual column partitioning, and several new composite partitioning methods: Range-Range, Hash-Hash, List-List, List-Hash, List-Range, along with Interval-Range, Interval-Hash, and Interval-List. When added to all the existing methods of partitioning, this large range of choices require that Database Administrators and Data Architects understand each method and appropriate uses.

Duration: 2 Days


  • Database Administrators
  • Data Warehouse Administrator
  • Architect
  • Database Designers

Required Prerequisites:

  • Basic Oracle Database administration, Basic SQL Skills
  • Oracle Database 11g: Administration Workshop I DBA Release 2
  • Oracle Database 11g: Administration Workshop II DBA Release 2

Course Objectives:

  • Understand partitioning options with other database features
  • Describe the partitioning architecture, uses, and advantages
  • Describe the partition types supported by the Oracle RDBMS
  • List all of the options for creating partition tables
  • Create partitioned tables
  • Describe the table and index partition relationships
  • List all the options of partitioned indexes
  • Create partitioned indexes
  • List all of the alterable partitioned table and index attributes
  • Describe the overhead associated with each maintenance command
  • Use the data dictionary to verify partitioning structure
  • Create Materialized Views that are partitioned
  • Explain the benefits of partitioning materialzied views
  • Show performance enhancements of partitioned materiized views
  • Choose appropriate partition attributes for various application requirements
  • Describe Oracle Enterprise Manager support of partitioned objects


Introduction to Partitioning

  • VLDB Manageability and Performance Constraints
  • Manual Partitions
  • Partitioned Tables, Indexes, Materialized Views
  • Benefits of Partitioning
  • Performance Consideration, Manageability & Partitioning Methods
  • Table Versus Index Partitioning, Partitioned Indexes & Verifying Partition Use
  • Proof of Pruning
  • SQL-Loader, Partitioned Objects and Partitioning History

Implementing Partitioned Tables

  • Table, Partition, and Segment Relations
  • Equipartitioning & General Restrictions
  • The CREATE TABLE Statement with Partitioning
  • Partition Key Value, Range Partitioning, Interval Partitioning, Multicolumn Partitioning
  • List Partitioning, Hash Partitioning, Named Partitions & Hash Partitioning: Quantity of Partitions
  • Composite Partitioning
  • Index Organized Table (IOT) Partitioning, LOB Partitioning
  • Partitioned Object Tables and Partitioned Tables with Object Types

Implementing Partitioned Indexes

  • Partitioned Indexes & Partitioned Index Attributes
  • Index Partitioning Types
  • Global Indexes, Local Prefixed Index
  • Local Prefix Index Examples, Local Nonprefixed Index
  • Index Partitioning and Type Matrix
  • Specifying Index with Table Creation
  • Graphic Comparison of Partitioned Index Types
  • Index Partition Status, Data Dictionary Views Indexes & Guidelines for Partitioning Indexes

Maintenance of Partitioned Tables and Indexes

  • Maintenance Overview
  • Table and Index Interaction During Partition Maintenance
  • Modifying a Table or Indexing Logical Properties
  • Modifying Partition Properties on the Table
  • Using the ALTER TABLE or INDEX Commands
  • Renaming a Partition
  • Partition Storage Changes
  • Moving a Partition, Adding a Partition, Dropping a Partition, Splitting and Merging a Partition, Coalescing a Partition, Rebuilding Indexes

Partitioning Administration and Usage

  • Using Partitioned Tables
  • Pruning Rules, Partition-wise Joins
  • ANALYZE and Partitioned Objects & Data Dictionary View Statistics
  • SQL*Loader and Partitioned Objects
  • SQL*Loader Conventional Path
  • SQL*Loader Direct Path Sequential Loads
  • SQL*Loader Direct Path Parallel Loads
  • Export and Import

Partitioning and Workload Types

  • Partitioning in Data Warehouses
  • Partitioning for Information Lifecycle Management
  • Partitioning in OLTP Environments

Course DateLocationDaysPriceRegister
Call for Classroom Training Dates2 $1,930Register

Course DateLocationDaysPriceRegister
Call for Live Virtual Class Dates2 $1,840Register