The following optimization methods affect grouping processing:

• Rapid grouping processing
• Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server

If the HiRDB system judges that sort and hash processing for grouping are unnecessary, it selects a method that can process the data faster. For details about grouping processing, see the HiRDB Version 9 Command Reference manual.

The following table describes the optimization features related to grouping processing methods.

Table 4-9 Optimization features related to grouping processing methods

Optimization method (type of grouping processing method)	Advantages	Disadvantages
Omitted (FLOATABLE SORT)	Data can be searched rapidly if the data count values are unevenly distributed among the back-end servers and grouping does not reduce the data count.	If the group count is small and the data count is high, the performance drops because the communication volume increases.
Rapid grouping processing (HASH)	Data can be searched rapidly when the group count is small.	If the group count is large, the performance drops because the data is grouped by hashing.
Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server (LIST SORT)	Data can be searched rapidly if grouping substantially decreases the data count. Data can also be searched rapidly if the data is grouped by partitioning keys.	If the data count values are unevenly distributed among the back-end servers because of sorting in each back-end server, the performance drops because processing takes a long time in servers that have a high data count.

(a) Grouping processing when the optimization method is omitted

The following figure shows grouping processing when the optimization method is omitted.

Figure 4-22 Grouping processing method when the optimization method is omitted

Explanation

1. The back-end servers perform only data fetching.
2. The floatable servers perform only sorting and grouping by grouping columns.
3. The front-end server collects grouping processing results and transfers the results to the client.

(b) Grouping processing when rapid grouping processing is applied

The following figure shows grouping processing when rapid grouping processing is applied.

Figure 4-23 Grouping processing when rapid grouping processing is applied

Explanation

1. The back-end servers fetch data, and then hash and group the data by grouping columns. (The floatable servers are not used.)
2. The front-end server collects grouping results from each back-end server, regroups the combined data, and transfers the results to the client.

(c) Grouping processing when group processing, ORDER BY processing, and DISTINCT set function processing are applied at the local back-end server

The following figure shows grouping processing when group processing, ORDER BY processing, and DISTINCT set function processing are applied at the local back-end server. However, this diagram shows the processing when one table is searched.

Figure 4-24 Grouping processing when group processing, ORDER BY processing, and DISTINCT set function processing are applied at the local back-end server

Explanation

1. The back-end servers fetch data, and then sort and group the data by grouping columns. (The floatable servers are not used.)
2. The front-end server collects grouping results from each back-end server, regroups the combined data, and transfers the results to the client.