Oracle复合范围-哈希分区：ID作为WHERE条件的性能相关疑问

Let’s break down your questions one by one based on how Oracle handles partitioning and query execution:

1. Will query performance improve when using the ID column in the WHERE clause? #

Yes, but the extent depends on whether you also include the datecreated column in your query. Here’s why:

• Your table uses range partitioning on datecreated with daily intervals, paired with hash subpartitioning on ID (4 subpartitions per range partition).
• When querying only by ID, Oracle calculates the hash value of the ID and scans only the matching hash subpartition within every range partition. This is better than scanning entire range partitions (in a range-only partitioned table) or the full table (non-partitioned), since each hash subpartition is a smaller, more focused segment.
• If you add the datecreated condition to the WHERE clause, Oracle can prune out irrelevant range partitions entirely, then scan only the matching hash subpartition in the remaining range partition(s)—this delivers the best performance, as it minimizes the data scanned to just a tiny subset of your table.

2. Will performance be the same if we only partition by date (range-only partitioning)? #

No, performance will differ based on your query patterns and data volume:

• When queries only filter by datecreated: Performance will be similar, since both partitioning schemes let Oracle prune to the correct date range(s).
• When queries filter by ID (alone or with datecreated): The composite range-hash partitioned table will perform better. Hash subpartitions split each large date partition into 4 smaller segments, so scanning for a specific ID only touches one small subpartition per date range (or just one subpartition if datecreated is included), instead of scanning an entire large date partition.
• Write performance: If you have high-volume writes to a single date range, hash subpartitions spread the IO load across multiple subpartitions, reducing contention and improving write throughput compared to a single large range partition.

3. Since ID is the primary key, will the performance of the two partitioning methods be identical? #

Absolutely not—being a primary key doesn’t eliminate the performance differences between the two partitioning schemes. Here’s why:

• The primary key creates an index (either global or local), but partitioning affects how data and indexes are physically stored:
  • For a range-only partitioned table, a local primary key index is partitioned the same way as the table (by datecreated), so querying by ID would require scanning all index partitions (unless you include datecreated in the query). A global primary key index is a single, non-partitioned structure, which can be fast for point queries but becomes a maintenance bottleneck when you drop/truncate old date partitions (global indexes go invalid and need rebuilding).
  • For the composite range-hash table, a local primary key index is partitioned by range + hash, so querying by ID lets Oracle prune to the correct hash subpartition within each range partition (or just one subpartition if datecreated is included), leading to more efficient index scans.
• Primary key constraints are logical rules—they don’t override Oracle’s partition pruning logic or physical data distribution. The way data is split across partitions/subpartitions still directly impacts how much data needs to be scanned during queries and how IO is handled during writes.

内容的提问来源于stack exchange，提问作者Arjun