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 [...]