Hash table partitioning distributes data to logical partitions for parallel execution, which can enhance join performance on large tables and distributed queries (PlexQ).
New join algorithms and aggregation algorithms can take advantage of hash partitioning by reducing the amount of intermediate storage and network transfer required as well as providing increased parallelism by leveraging the semantic division of the table rows into partitions. The improved cache behavior provided by data affinity and affinity based work allocation provide further scalability in the PlexQ environment.
To use the hash partitioned join or hash partitioned aggregation algorithms, it is critical that all the columns of the hash partitioning key for the table or tables, be used in the equi-join conditions or grouping expressions for those tables. Additional join conditions or grouping expressions may be used, but if a column that is a component of the hash partitioning key is not used in the query, the partitioned algorithms will not be eligible. In such a case, other non-partitioned algorithms remain eligible and would be chosen as for non-partitioned tables. Hash partitioning of a set of tables should cover the smallest common set of columns used by the application queries on those tables. Frequently a single column is a sufficient partitioning basis. It is essential that the data types of columns in joins between partitioned tables be identical.
Small tables benefit less from hash partitioning than large tables. Consider hash partitioning for tables of at least 1 billion rows.
Some load operations may require more large memory than the 2GB default provides. If the memory requirements exceed the default, use the - iqlm startup option to increase the memory that SAP Sybase IQ can dynamically request from the OS.
As a general rule, large memory requirements represent one third of the total available physical memory allocated to SAP Sybase IQ. To ensure adequate memory for the main and temporary IQ stores, set the –iqlm, –iqtc, and –iqmc startup parameters so that each parameter receives one third of all available physical memory allocated to SAP Sybase IQ.
In most cases, you should allocate 80% of total physical memory to SAP Sybase IQ to prevent SAP Sybase IQ processes from being swapped out. Adjust actual memory allocation to accommodate other processes running on the same system. For example, on a machine with 32 cores and 128GB of total available physical memory, you would allocate 100GB (approximately 80% of the 128GB total) to SAP Sybase IQ processes. Following the general rule, you would set the –iqlm, –iqtc, and –iqmc parameters to 33GB each.
Reference: Statements and Options > Database Options > Alphabetical List of Options > JOIN_PREFERENCE Option
Reference: Statements and Options > Database Options > Alphabetical List of Options > AGGREGATION_PREFERENCE Option
Utility Guide > start_iq Database Server Startup Utility > start_iq Server Options > –iqlm iqsrv16 Server Option
Utility Guide > start_iq Database Server Startup Utility > start_iq Server Options > –iqmc iqsrv16 Server Option
Utility Guide > start_iq Database Server Startup Utility > start_iq Server Options > –iqtc iqsrv16 Server Option