Evaluating Query Scan Efficiency Under Hybrid Partitioning in Oracle Data Warehouse Systems

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.