Lesson 7

This module examined Oracle's partitioning facility from first principles through practical maintenance — covering the foundational concepts, the data dictionary views used to monitor partitioned objects, the two index types available for partitioned tables, and the procedures for keeping those indexes healthy over time.

Partitioning addresses a class of performance and manageability problems that no amount of index tuning, SQL rewriting, or buffer cache sizing can solve: the problems that arise when a table or index becomes too large to operate on as a single unit. The divide-and-conquer approach that partitioning enables — independently manageable segments that appear as one logical object to SQL — is the foundation for scaling Oracle databases to enterprise data volumes.

The main points of this module are:

1. Large Oracle tables and indexes can be partitioned by a range of column values, a hash function, a discrete list of values, or a combination of strategies as composite partitions.
2. Oracle provides several data dictionary views for monitoring the data characteristics of table and index partitions, most notably DBA_TAB_PARTITIONS and DBA_IND_PARTITIONS.
3. The Oracle optimizer recognizes queries that filter on the partition key and reads only the affected partitions to service the query — this is called partition pruning.
4. Disk load balancing is easier with partitioned tables because each partition can reside in a separate tablespace with separate data files, distributing I/O across storage resources.
5. Oracle offers two types of index for partitioned tables: global and local.
6. Global indexes are best for OLTP applications that need to minimize index partition probes. A single B-tree traversal resolves any lookup regardless of which table partition holds the target row.
7. Local indexes are fastest for data warehouse and DSS applications. Each local index partition is scoped to exactly the rows in its corresponding table partition, enabling partition-wise index scans and efficient partition pruning.
8. With local partitioned indexes, each partition can be rebuilt independently while the other partitions continue to service SQL queries — a significant availability advantage over global index maintenance.

Lesson 1 — Tuning with Oracle Partitioning: Partitioning is the Oracle feature that addresses the performance and manageability challenges of very large databases. Four capabilities drive the performance benefit: partition pruning, parallel partition operations, independent partition management, and partition-level index management. Partitioning is transparent to the application — the same SQL works against partitioned and non-partitioned tables without modification. Oracle 23ai extends partitioning with interval partitioning, In-Memory Column Store integration at the partition level, and Automatic Indexing recommendations for large tables.

Lesson 2 — Oracle Partitioned Objects: Partitioning allows tables, indexes, and index-organized tables to be subdivided into partitions, each with its own name and optionally its own storage characteristics. Three primary partitioning strategies exist: range (for sequential or date-based data), hash (for even distribution without a natural range key), and composite (range primary with hash subpartitions). Interval partitioning automatically creates new partitions as data arrives. Partitioning is the foundational technology for Information Lifecycle Management — dropping a partition is an instantaneous metadata operation that resets the high water mark, compared to a DELETE that leaves the HWM elevated until a full reorganization.

Lesson 3 — DBA Views for Partitioned Objects: The primary data dictionary views for partitioned tables are DBA_TAB_PARTITIONS, ALL_TAB_PARTITIONS, and USER_TAB_PARTITIONS. Three columns in these views are the primary indicators for deciding when to reorganize a partition: NUM_ROWS (to detect HWM elevation after large DELETEs), AVG_ROW_LEN (to detect row migration from insufficient PCTFREE), and CHAIN_CNT (to detect chained rows). Individual partitions can be reorganized with ALTER TABLE ... MOVE PARTITION and their local indexes rebuilt with ALTER INDEX ... REBUILD PARTITION — targeting only the affected partition without taking the rest of the table offline.

Lesson 4 — Global Partitioned Indexes: A global partitioned index maintains a single B-tree structure that indexes all rows across all table partitions. Because there is only one index tree, index partition probes are minimized — making global indexes the preferred choice for OLTP applications where single-row lookups dominate and queries do not always filter on the partition key. The maintenance tradeoff is significant: when partition DDL is performed on the underlying table (DROP, SPLIT, MERGE, MOVE), all partitions of the global index are potentially affected. Use UPDATE GLOBAL INDEXES in the ALTER TABLE statement to keep the index valid without a full rebuild. Without this clause, the global index is marked UNUSABLE and must be rebuilt before it can serve queries.

Lesson 5 — Local Partitioned Indexes: A local partitioned index scopes each index partition to exactly the rows in its corresponding table partition. Creating a local index with the same partition key as the table — called equipartitioning — allows the optimizer to use partition-wise index access and eliminates cross-partition index probes for queries filtered on the partition key. Local indexes are the preferred choice for data warehouse and DSS workloads. Each local index partition can be rebuilt, moved, or recovered independently of the rest of the index. Partition maintenance operations that would require rebuilding an entire global index (DROP PARTITION, for example) are transparent to local indexes — the local index partition drops with its table partition automatically.

Lesson 6 — Partitioned Index Maintenance: Index partitions can become fragmented or be marked UNUSABLE by bulk data loads or partition DDL. The primary maintenance command is ALTER INDEX REBUILD PARTITION, which regenerates a single partition without affecting others. The ONLINE clause keeps the partition usable throughout the rebuild. For composite partitioned indexes, ALTER INDEX REBUILD SUBPARTITION extends this capability to the subpartition level. Always specify the TABLESPACE clause when rebuilding — the tablespace must have room for both the existing partition and the temporary segments for the new partition being built. When choosing between rebuild and coalesce: rebuild creates a new index tree and can move the index to a different tablespace; coalesce frees leaf blocks within the existing tree without requiring additional disk space.

Partitions can be used to isolate data physically and to tune data manipulation operations at a granularity that is not possible with non-partitioned tables. Practical applications include:

1. Truncating a partition and its indexes without affecting the rest of the table — a zero-redo operation regardless of the partition size.
2. Dropping a partition using ALTER TABLE ... DROP PARTITION — an instantaneous metadata operation that immediately reclaims space and resets the partition HWM.
3. Dropping a partition's local index independently, allowing the partition to remain in the table while its index is rebuilt or remains offline.
4. Setting a partition to NOLOGGING before a bulk load, then resetting to LOGGING after — reducing redo overhead for large data insertion operations.
5. Using EXCHANGE PARTITION to load data into a non-partitioned staging table, validate it, and then exchange the staging table into the partitioned table as a new partition — an instantaneous metadata swap with no data movement.

From a performance perspective, the chief advantage of partitions is their ability to be managed independently from the rest of the table. Truncating a partition avoids generating redo information for deleted rows and does not raise the HWM. Dropping a partition eliminates the need for DELETE statements on large data volumes and immediately reclaims the storage. The EXCHANGE PARTITION pattern reduces data-loading downtime to near zero for rolling window implementations in data warehouses.

Having completed this module, you should now be able to:

1. Describe how partitioning functions in Oracle, including partition pruning, parallel operations, and transparent SQL access.
2. List the differences between global and local partitioning — global indexes span all table partitions in a single B-tree; local indexes are scoped one-to-one with their corresponding table partition.
3. Describe the structure of a partitioned index, including the role of the master index node in making separate local index trees appear as a single logical index.
4. Use DBA_TAB_PARTITIONS, ALL_TAB_PARTITIONS, and USER_TAB_PARTITIONS to monitor partition health and determine when to reorganize.
5. Maintain partitioned indexes using ALTER INDEX REBUILD PARTITION, ALTER INDEX REBUILD SUBPARTITION, and the UPDATE GLOBAL INDEXES clause on partition DDL statements.

1. Partition: an independently manageable piece of a partitioned table, index, or index-organized table. Each partition has its own name and may have its own storage characteristics.
2. Partition pruning: the Oracle optimizer's automatic exclusion of partitions that cannot contain rows satisfying a query's WHERE clause predicate.
3. Range partitioning: divides data based on a range of partition key values; the most common strategy for date-based or time-series data.
4. Hash partitioning: distributes rows evenly across a fixed number of partitions using a hash function applied to the partition key. Pruning is limited to equality predicates.
5. Interval partitioning: an extension of range partitioning (Oracle 11g+) that automatically creates new partitions as data arrives for new intervals.
6. Global partitioned index: a single B-tree structure that indexes all rows across all table partitions. Preferred for OLTP. Requires special maintenance when partition DDL is performed on the underlying table.
7. Local partitioned index: an index where each partition is scoped to exactly the rows in its corresponding table partition. Preferred for DSS and data warehouse workloads. Partition maintenance is transparent — local index partitions drop with their table partitions.
8. Equipartitioning: the practice of partitioning a table and its index identically so that table partition and index partition map to the same set of rows. Implemented automatically with the LOCAL keyword.
9. DBA_TAB_PARTITIONS: the primary data dictionary view for monitoring partition-level statistics including NUM_ROWS, AVG_ROW_LEN, and CHAIN_CNT — the three columns that indicate when a partition needs reorganization.
10. UPDATE GLOBAL INDEXES: the ALTER TABLE clause that keeps global indexes valid and usable during partition DDL operations, avoiding the UNUSABLE state that would otherwise require a full index rebuild.

Before you go on, test your knowledge of Oracle partitioning with a short Quiz.
Oracle Partitioning - Quiz