About this Course

Code TPD71837GC10
Duration 2 Days

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.The course will also cover partitioning of Lob segments, nested tables, and object tables. Proper use of partitioning can greatly benefit many types of applications including Data Warehouses, Information Lifecycle management, and very large databases in general.This course is based on Oracle Database 11g Release 2 Patch Set 1.Learn To:Apply partitioning strategies to enhance application performanceUse partitioning techniques to reduce impact of table and index maintenanceUse partitioning to decrease the time to refresh materialized viewsA Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.

Delegates will learn how to

  • 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

2 Days


This is a QA approved partner course

Delivery Method

Delivery method


Face-to-face learning in the comfort of our quality nationwide centres, with free refreshments and Wi-Fi.

Find dates and prices

Online booking is currently not available for this course, to find out more please call us on 0113 220 7150 or email us at info@qa.com to discuss how we can help.

Trusted, awarded and accredited

Fully accredited to ensure we provide the highest possible standards in learning

All third party trademark rights acknowledged.