OS & Oracle

Another relationship between DOP and number of hash partitions is that when doing partition wise sort, the number of partitions should be twice greater than DOP.
Similarly, firstly we create the table for testing. then insert data, then gather statistics.

SQL> CREATE TABLE fact_a(
2 date_key date,
3 part_col1 NUMBER
4 [...]

In 10gR2 or 11gR1, when doing partition wise join, the number of hash partitions should be equal to DOP. Otherwise, the execution plan must become worse.
First, I will create two tables, fact_a and fact_b, both are partitioned by hash, and have hash partitions 4.

SQL> CREATE TABLE fact_a(
2 date_key date,
3 [...]

The PQ_DISTRIBUTE hint instructs the optimizer how to distribute rows of joined tables among producer and consumer query servers. Such distribution can improve the performance of parallel join operations.
 
tablespec is the name or alias of a table to be used as the inner table of a join. [...]