Use of SQL tuning (SQL TUNING) parallel query hint (Hints) pq_distribute

Pq_distribute hints are often used to improve the performance of connection operations between partitioned tables in the Data Warehouse. The Pq_distribute hint allows you to determine how table data rows participating in a connection are allocated between production and consumption parallel query service processes. Pq_distribute prompt accepts three parameters: table name, outer allocation, and internal allocation.

When parallel query connections are performed, we always try to avoid parallel_to_parallel execution plans. Parallel_to_parallel operation means that the input and output data streams are parallel, resulting in poor connection performance. On the other hand, the parallel_combined_with_parent operation means that sorting and merging operations are combined into one operation.
Before using the Pq_distribute hint, some Oracle DBAs often spoof the SQL optimizer by removing the CBO statistics from the internal table to enforce the parallel_combined_with_parent operation. Because the SQL optimizer evaluates the size of the candidate broadcast tables based on these CBO statistics. When the table is larger than a certain threshold, the table will be connected through the Parallel_to_parallel execution mode, which results in very low performance.
Pq_distribute hints can accept six parameter combinations. Remember that the order of the parameters is outside the allocation before, within the allocation after.
1) Pq_distribute (Tab_name, Hash,hash): This combination assigns the table data rows to the consuming parallel query service process through a hash function on the connection key. After the mapping is complete, each query service process connects to a pair of result partitions. This hint is recommended when the table size is equivalent and the connection operation is implemented by hashing or sorting merge.
2) Pq_distribute (Tab_name,broadcast, none): This combination ensures that all data rows in the façade are broadcast to each consuming parallel service process, while the inner table data rows are randomly partitioned. This hint is recommended when the appearance is much smaller than the inner table. An important principle is to use broadcast/none if the inner table size is multiplied by the number of parallel service processes greater than the physical size.
3) Pq_distribute (Tab_name, none,broadcast): This combination forces all data rows within the table to be broadcast to each consuming parallel query service process, while the outer data rows are randomly partitioned. This hint is recommended when the inner table is more than the outside hour. An important principle is that the none/broadcase hint is recommended when the inner table size is multiplied by the number of parallel query service processes less than the physical size.
4) Pq_distribute (Tab_name,partition, none): This combination maps the outer data rows through the inner table partition, and the inner table must be partitioned by the connection key. This hint is recommended when the number of internal table partitions equals or approaches the number of parallel query service processes.
5) Pq_distribute (Tab_name, none,partition): This combination maps the inner table data rows by a surface partition, and the appearance must be partitioned by the partition key. This combination is recommended when the number of surface partitions equals or approaches the number of parallel query service processes.
6) Pq_distribute (Tab_name, None,none): In this combination, each parallel query server connects between a pair of matching partitions, each from a single table. Two tables must be partitioned equally on the connection key.

Use of SQL tuning (SQL TUNING) parallel query hint (Hints) pq_distribute