Impact of Hybrid Partitioning on Query Scan Efficiency in Oracle Data Warehouses

Abstract

Hybrid RANGE + AUTO LIST partitioning offers a flexible approach for organizing large fact tables
in Oracle data warehouses where both temporal filtering and evolving business classification
attributes shape analytical workloads. By enabling automatic subpartition creation as new category
values emerge, this model reduces administrative overhead while preserving subpartition-level
pruning efficiency. The results indicate that when analytical queries consistently apply both date and
category predicates, hybrid partitioning significantly reduces physical I/O and improves scan
throughput, particularly in Exadata environments that benefit from Smart Scan offload. However, the
performance advantages depend on stable category domains and governed query patterns; unmanaged
category proliferation or inconsistent filtering can reduce pruning effectiveness over time. The study
shows that hybrid partitioning integrates naturally with lifecycle-aware compression and storage
tiering strategies, enabling data warehouses to balance efficient query execution, scalable growth, and
adaptable schema evolution without sacrificing performance.