SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

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.
• Hash 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
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.
In addition, indexes can be an efficient underlying mechanism for data access.
For more information, 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:
• All other SQL/MX objects (views, indexes, constraints, and so on) are directly or indirectly
dependent on the tables.
• Recovery and management methods are simplified.
Disadvantages include:
• Although you can restrict access to specified table columns when you use GRANT to provide
a user with UPDATE or REFERENCES privileges to a table, the GRANT SELECT, DELETE,
Determining a Database Layout 35