1. What is Oracle hash partitioning, and when is it used?

Hash partitioning distributes data evenly across a fixed number of partitions using an internal hashing algorithm. It’s ideal when:

• You have no natural ranges or groups
• You want to avoid data skew
• Uniform data distribution is more important than logical partitioning

Common Use Cases:

• User IDs, invoice numbers, transaction IDs
• Large tables accessed frequently in parallel

 

  2. How does Oracle determine which hash partition a row goes into?

Oracle applies a hashing function to the partition key (e.g., user_id) and maps it to a partition number internally. You cannot influence which exact partition a specific value lands in.

 

  3. Can I specify which values go into which partitions?

❌ No. Unlike list or range partitioning, you cannot control value-to-partition mapping. Oracle handles this automatically to balance data.

 

  4. What are the benefits of hash partitioning?

• Ensures even data distribution
• Prevents hotspots (i.e., some partitions having more data than others)
• Optimizes parallel processing
• Reduces contention in high-concurrency systems

 

  5. Can I use multiple columns as a hash key?

  Yes. You can hash on one or more columns:

PARTITION BY HASH (customer_id, product_id)

PARTITIONS 8

This is called composite hashing.

 

  6. How many hash partitions can I create?

Oracle allows up to 1024 partitions per table, but practical limits depend on performance and manageability. Most real-world systems use 2, 4, 8, 16, or 32 partitions.

 

  7. How do I define a hash-partitioned table?

CREATE TABLE users (

  user_id NUMBER,

  name    VARCHAR2(100)

)

PARTITION BY HASH (user_id)

PARTITIONS 4;

Oracle automatically names the partitions (e.g., SYS_P0, SYS_P1...).

 

  8. Can I give custom names to hash partitions?

  Yes:

CREATE TABLE users (

  user_id NUMBER

)

PARTITION BY HASH (user_id) (

  PARTITION p1,

  PARTITION p2,

  PARTITION p3,

  PARTITION p4

);

This improves clarity and management.

 

  9. How does Oracle handle NULL values in hash partitioning?

Oracle treats NULL as a valid value and hashes it just like any other value. Rows with NULL in the partition key will go into one of the hash partitions.

 

  10. Can I query specific partitions in a hash-partitioned table?

You can use partition-level access by specifying partition name, but Oracle doesn't prune hash partitions based on typical WHERE clauses — unless you're explicitly filtering for partition number or using partition-wise joins.

 

  11. Can I update the hash key column?

You can update the partition key, but if the new value hashes to a different partition, Oracle will delete and re-insert the row into the new partition. This may impact performance.

 

  12. Is partition pruning effective with hash partitioning?

❌ Not usually. Oracle cannot prune partitions easily based on key values unless specific partition access is used. This is a trade-off for uniform distribution.

 

  13. Can I drop a specific hash partition?

No. You cannot drop a single partition in a hash-partitioned table. You must recreate the table if the number of partitions needs to change.

 

  14. How do I increase the number of partitions in a hash-partitioned table?

You cannot directly increase partitions (no ALTER TABLE ... ADD PARTITION). You must:

• Create a new table with more partitions
• Load data via INSERT INTO new_table SELECT * FROM old_table
• Rename and drop the old table

 

  15. Can I use hash partitioning with subpartitioning?

  Yes. Hash partitioning is often used as a subpartitioning method under list or range partitioning (composite partitioning). For example:

PARTITION BY LIST (region)

SUBPARTITION BY HASH (user_id)

This enables both logical and uniform distribution.

 

  16. Can I gather stats on each hash partition?

Yes. Use DBMS_STATS:

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR', tabname => 'USERS', partname => 'P1');

You can also collect global and partition-level stats to help the optimizer.

 

  17. How do I check row distribution across hash partitions?

Use:

SELECT partition_name, num_rows

FROM user_tab_partitions

WHERE table_name = 'USERS';

Helps verify whether the hash function has evenly distributed the rows.

 

  18. Can I compress specific hash partitions?

  Yes:

ALTER TABLE users MODIFY PARTITION p1 COMPRESS FOR QUERY LOW;

Useful for space savings, especially for older or read-only partitions.

 

  19. What are the indexing options for hash partitioned tables?

• Local indexes: partition-aligned, easier to manage
• Global indexes: supported, but need attention during maintenance
• Oracle recommends local indexes for performance and manageability.

 

  20. What are some best practices for hash partitioning?

  Best practices:

• Use hash partitioning when no logical grouping is possible
• Always test row distribution
• Keep partition count manageable (4–32 is typical)
• Avoid using it alone for time-based data
• Use composite partitioning (e.g., list + hash) when applicable

 

🧠 Summary – Oracle Hash Partitioning at a Glance

Feature	Supported?
Manual value control	❌ No
Even row distribution	Yes
Supports NULL values	Yes
Partition pruning	❌ Limited
Dropping individual partitions	❌ No
Compression per partition	Yes
Partition-wise joins	Yes
Changing partition count	❌ Requires redefinition
Multi-column hash key	Yes
Suitable for time-series data	❌ No

 

🛠 Example: Hash Partitioned Table

CREATE TABLE transactions (

  txn_id     NUMBER,

  customer_id NUMBER,

  amount     NUMBER

)

PARTITION BY HASH (customer_id)

PARTITIONS 4;

Oracle will balance rows based on customer_id across 4 partitions.

 

Would you like:

• A PL/SQL script to analyze partition distribution?
• A template to recreate a hash-partitioned table with more partitions?
• Guidance on combining hash + list or range for better performance?

Let me know what you'd like to dive deeper into.