The four types of pipes managed by the exchange operator are distinguished by how they split and merge data streams. You can determine which type of pipe is being managed by the exchange operator by looking at its description in the showplan output, where the number of producers and consumers are shown. The four pipe types are described below.
In this case, the exchange operator spawns multiple producer threads and has one consumer task that reads the data from a pipe, to which multiple producer threads write. The exchange operator in the previous example implements a many-to-one exchange. A many-to-one exchange operator can be order-preserving and this technique is employed particularly when doing a parallel sort for an order by clause and the resultant data stream merged to generate the final ordering. The showplan output shows more than one producer process and one consumer process.
|EXCHANGE Operator (Merged) |Executed in parallel by 3 Producer and 1 Consumer processes
In this case, there is one producer and multiple consumer threads. The producer thread writes data to multiple pipes according to a partitioning scheme devised at query optimization, and then routes data to each of these pipes. Each consumer thread reads data from one of the assigned pipes. This kind of data split can preserve the ordering of the data. The showplan output shows one producer process and more than one consumer processes:
|EXCHANGE Operator (Repartitioned) |Executed in parallel by 1 Producer and 4 Consumer processes
Many-to-many means there are multiple producers and multiple consumers. Each producer writes to multiple pipes, and each pipe has multiple consumers. Each stream is written to a pipe. Each consumer thread reads data from one of the assigned pipes.
|EXCHANGE Operator (Repartitioned) |Executed in parallel by 3 Producer and 4 Consumer processes
In this case, the producer thread writes all of its data to each pipe that the exchange operator configures. The producer thread makes a number of copies of the source data (the number is specified by the query optimizer) equal to the number of pipes in the exchange operator. Each consumer thread reads data from one of the assigned pipes. The showplan output shows this as follows:
|EXCHANGE (Replicated) |Executed in parallel by 3 Producers and 4 Consumer processes
