SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Installation and Management Guide—523723-004
4-10
Determining a Database Layout
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.
For information about determining an SQL/MP database layout, see the SQL/MP
Installation and Management Guide.
Using SQL/MX Tables
You can use SQL/MX tables to externalize all the data to the user. Using tables is the
most direct method because views, constraints, and indexes ultimately depend on their
underlying tables.
Advantages to using only tables as the external database scheme include:
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.