Fragmentation Enhancement to PDQ

The complex queries that are typical of DSS applications benefit from PDQ and fragmentation. DSS applications perform complex tasks that often include scans of entire tables, manipulation of large amounts of data, multiple joins, and the creation of temporary tables. Such operations can involve many I/O operations, many calculations, and large amounts of memory.

The performance of decision-support queries increases almost linearly with the number of fragments added. With Extended Parallel Server, you can add many more fragments based on the number of parallel nodes with attached disks on separate I/O ports. It is recommended that the number of fragments be a multiple of the number of coservers.

When you fragment your data across multiple coservers, the database server can start parallel SQL operators (scans, sorts, inserts, and so forth) on all available CPU virtual processors on the different coservers.

In Extended Parallel Server, you can increase the degree of parallelism by fragmenting tables across multiple coservers. Cross-coserver fragmentation ensures that table fragments are processed in parallel by threads running on each coserver.

Fragmenting tables across coservers provides these advantages:

• More efficient use of shared memory

  The database server uses the resources on each coserver when fragments are processed in parallel.

• More efficient hash algorithm

  The system-defined hash fragmentation rule lets the database server eliminate fragments immediately for queries that use the hashed column as a join key.

• Higher degree of parallelism for scans

  The separate disks and CPU VPs on each coserver can process I/O in parallel.

• More efficient join operations

  Co-located joins generate less traffic between coservers.

• Higher degree of parallelism for sorts

  If the data involved in the sort operation resides on different coservers, parallel sorts can occur even when the PSORT_NPROCS environment variable is not set.

Fragmentation further allows the server, through fragment elimination, to intelligently avoid the need to examine and process some of the fragments if those fragments do not affect the outcome of the query. This dramatically reduces the resources and time needed to process a query. XPS is enhanced to allow complex expressions to describe a multidimensional fragmentation scheme and is able to analyze complex query predicates for fragment elimination.

For more information on fragmentation strategy guidelines to improve performance, refer to your IBM Informix: Performance Guide.