Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use pq_distribute in SQL, the article is very detailed, has a certain reference value, interested friends must read it!

The pq_distribute hint is often used to improve join performance between partitioned tables in a data warehouse. The pq_distribute hint allows you to determine how the rows of tables participating in the join are distributed between the producing and consuming parallel query service processes. The pq_distribute prompt accepts three parameters: table name, outer assignment, and inner assignment.

When performing parallel query joins, we always want to avoid PARALLEL_TO_PARALLEL execution plans. PARALLEL_TO_PARALLEL operation means that input and output data streams are parallel, resulting in poor connection performance. PARALLEL_COMBINED_WITH_PARENT, on the other hand, implies combining sort and merge operations into one operation.

Before using the pq_distribute hint, some Oracle DBAs tricked the SQL optimizer into forcing the PARALLEL_COMBINED_WITH_PARENT operation, often by deleting CBO statistics for internal tables. Because SQL optimizer evaluates the size of candidate broadcast tables based on these CBO statistics. When the table is larger than a certain threshold, the table performs mode joins via PARALLEL_TO_PARALLEL, which results in poor performance.

The pq_distribute hint accepts six parameter combinations. Remember that the order of parameters is outer allocation first, inner allocation second.

1), pq_distribute(tab_name, hash,hash): This combination distributes table rows to consuming parallel query service processes through hash functions on join keys. After mapping, each query service process joins between a pair of result partitions. This hint is recommended when the tables are of comparable size and join operations are implemented by hashing or sort merging.

2)pq_distribute(tab_name,broadcast, none): This combination ensures that all rows of the outer table are broadcast to each consuming parallel service process, while rows of the inner table are randomly partitioned. This tip is recommended when the exterior is significantly smaller than the interior. An important rule is to use broadcast/none if the inner table size multiplied by the number of parallel service processes is greater than the outer table size.

3)pq_distribute(tab_name, none,broadcast): This combination forces all rows of the inner table to be broadcast to each consuming parallel query service process, while outer rows are randomly partitioned. This tip is recommended when the inside is smaller than the outside. An important rule is that when the inner table size multiplied by the number of parallel query service processes is less than the outer table size, the none/broadcase hint is recommended.

4)pq_distribute(tab_name,partition, none): This combination maps outer rows of data by partitioning the inner table, and the inner table must be partitioned by the join key. This hint is recommended when the number of internal table partitions is equal to or close to the number of parallel query service processes.

5), pq_distribute(tab_name, none,partition): This combination maps the inner table data rows through the outer table partition, and the outer table must be partitioned by the partition key. This combination is recommended when the number of external partitions is equal to or close to the number of parallel query service processes.

6)pq_distribute(tab_name, none,none): In this combination, each parallel query server joins between a pair of matching partitions, each partition from a table. The two tables must be partitioned equally on the join key.

The above is "SQL pq_distribute how to use" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.