Oracle Partitioning in Practice

A table with 50 million rows and queries that scan all of them. Partitioning slices the table into smaller pieces, and Oracle searches only the relevant partitions. On our projects, it’s one of the most effective optimizations we apply.

Range Partitioning¶

The most common type for time series. An orders table partitioned by month — a query for January orders searches only one partition out of twelve. Partition pruning eliminates 11/12 of the data automatically.

Partition Management¶

Monthly partitions: automatic creation of new ones (INTERVAL partitioning in 11g). Archiving old ones: ALTER TABLE DROP PARTITION — instant, without DELETE FROM. Moving old partitions to cheaper storage (ILM).

Local vs. Global Indexes¶

Local indexes — one index per partition. Operations on a partition (DROP, MOVE) don’t affect indexes on other partitions. Global indexes — across all partitions. Better for queries without a partition key, but DROP PARTITION invalidates the global index. For our use: local indexes.

Performance Impact¶

Query on monthly data: from 45 seconds to 0.8 seconds. Full table scan eliminated. Nightly batch on new data: runs only over the latest partition. Statistics: DBMS_STATS per partition instead of the whole table — faster and more accurate.

When to Use Partitioning¶

Tables over 10M rows with a time dimension. Queries filtering by partition key. Need to archive old data. Note: partitioning is a paid Oracle option — verify your license.