4/10/2018 · In the previous two table examples , we actually used PQ_DISTRIBUTE(T1 BROADCAST NONE) – with much better results. This is conceptually wrong as the hint actually says “take the current result set and broadcast it to all processes”, but it works for two table joins.
There are six acceptable combinations for table distribution with the pq_distribute hint. We use the emp table in these examples. Remember that the order of the parameters is outer distribution followed by inner distribution. pq_distribute(emp, hash, hash) This maps the rows of each table to consumer parallel query servers using a hash function on the join keys. When mapping is complete, each query server.
10/19/2018 · The distribution hint for the above plan is: PQ_DISTRIBUTE(T2 PARTITION NONE) This part can be confusing, but what the hint instructs the query optimizer to do is: When you join T2 table, take the current result set (which in this case is T1 table) and send it to the parallel workers according to the partition boundaries of T2.
A new hint is added: USE_PARTITION_WISE_WIF. But enough background. Here’s what I’ve discovered about the PQ_DISTRIBUTE_WINDOW functions. Note that as of the writing of this blog, the PQ_DISTRIBUTE_WINDOW hint is still undocumented and there are no references in MOS (my oracle support). Reference for PQ_DISTRIBUTE_WINDOW:, The PQ_DISTRIBUTE hint controls the distribution method for a specified join operation. The basic syntax is as follows, where distribution is the distribution method to use between the producer and the consumer slaves for the left and the right side of the join: /*+ PQ_DISTRIBUTE(tablespec, distribution) */
