SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Understanding and Planning SQL/MX Tables

HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001

4-9

When to Use Hash Partitioning

Use hash partitioning if your data is not easily distributed among ranges or to evenly

distribute data across a specified number of partitions. Creating and using hash

partitions gives you a highly tunable method for data placement because you can

influence availability and performance by spreading these evenly sized partitions

across I/O devices (striping).

Hash partitioning achieves many of the scan reduction benefits of range partitioning

without requiring you to know the distribution of the partitioning key in advance. In

addition, use hash partitioning in a “decoupled” fashion to cluster data on a key other

than the partitioning key. This approach is useful when you want to co-locate hash

partitions of related tables and indexes.

Advantages of Hash Partitioning



Hash partitioning automatically provides balanced and even distribution of data

across available disks, helping to prevent skewing. Hash partitioning is equally

efficient as range partitioning for queries involving exact matches on clustering key

values because the search can be confined to one disk volume.



Hash partitioning is well suited for sequential access and point queries on

partitioning attributes.

Disadvantages of Hash Partitioning



It can be more difficult to remove specific data from a hash-partitioned database

than a range-partitioned database because it is randomly distributed across the

entire database.



Range queries are inefficient when compared to a table that is range partitioned on

the query columns.



Partition management operations (ADD and DROP) can be expensive for very

large hash-partitioned tables because the existing data in the table has to be

redistributed evenly to all the partitions of the table.

Determining a Database Layout

Users and applications can access your SQL/MX database with:



SQL/MX tables only



SQL/MX views only



A combination of SQL/MX tables and views

In addition, indexes can be an efficient underlying mechanism for data access.

Note. If your SQL/MX database contains SQL/MX and SQL/MP tables, users and applications

can access both SQL/MX or SQL/MP tables.