Reference partitioning and partitionwise joins

Why do you sometimes not get partitionwise joins? Because the optimizer isn't clever enough. Reference partitioning has many benefits, one of which is that the optimizer understands it. You will always get a partitionwise join if your tables are reference partitioned.

Begin with a parent table. Two columns: one for the primary hey, and another as the partitioning key:

create table parent (c1 number , c2 date, constraint pk primary key(c1))
partition by range (c2)
(partition p1 values less than (to_date('01-01-2011','dd-mm-yyyy')),
partition p2 values less than (to_date('01-01-2012','dd-mm-yyyy')));

Now create a child table the old way. Three columns: one for the foreign key, another for the partitioning key, and a third to hold the measure:

create table goodchild (c1 number, c2 date, c3 varchar2(10), constraint fk foreign key (c1) references parent(c1))
partition by range (c2)
(partition p1 values less than (to_date('01-01-2011','dd-mm-yyyy')),
partition p2 values less than (to_date('01-01-2012','dd-mm-yyyy')));

Does this work? Do I get a partitionwise join?

orcl> select /*+ use_hash(parent goodchild */ * from parent natural join goodchild;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3523298319

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    51 |     5  (20)| 00:00:01 |       |    |
|   1 |  PARTITION RANGE ALL        |           |     1 |    51 |     5  (20)| 00:00:01 |     1 |     2 |
|*  2 |   HASH JOIN                 |           |     1 |    51 |     5  (20)| 00:00:01 |       |    |
|   3 |    TABLE ACCESS STORAGE FULL| PARENT    |     1 |    22 |     2   (0)| 00:00:01 |     1 |     2 |
|   4 |    TABLE ACCESS STORAGE FULL| GOODCHILD |     1 |    29 |     2   (0)| 00:00:01 |     1 |     2 |
---------------------------------------------------------------------------------------------------------

Yes, I do - but only because I broke third normal form by duplicating the partitioning key. Furthermore, any trivial change, such as a different column name, means that the optimizer doesn't recognize that a partitionwise join is possible:

orcl> create table badchild (c1 number, c4 date, c3 varchar2(10), constraint fkbad foreign key (c1) refer
  2  partition by range (c4)
  3  (partition p1 values less than (to_date('01-01-2011','dd-mm-yyyy')),
  4  partition p2 values less than (to_date('01-01-2012','dd-mm-yyyy')));

Table created.

orcl> select /*+ use_hash(parent badchild */ * from parent natural join badchild;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2827712515

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    51 |     5  (20)| 00:00:01 |       |       |
|*  1 |  HASH JOIN                  |          |     1 |    51 |     5  (20)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE ALL       |          |     1 |    22 |     2   (0)| 00:00:01 |     1 |     2 |
|   3 |    TABLE ACCESS STORAGE FULL| PARENT   |     1 |    22 |     2   (0)| 00:00:01 |     1 |     2 |
|   4 |   PARTITION RANGE ALL       |          |     1 |    29 |     2   (0)| 00:00:01 |     1 |     2 |
|   5 |    TABLE ACCESS STORAGE FULL| BADCHILD |     1 |    29 |     2   (0)| 00:00:01 |     1 |     2 |
--------------------------------------------------------------------------------------------------------

Not so good, is it. But now try it with the 11g new feature of reference partitioning:

orcl> create table bestchild (c1 number not null, c3 varchar2(10), constraint fkref foreign key (c1) references parent(c1))
  2  partition by reference (fkref);

Table created.

orcl> select /*+ use_hash(parent bestchild */ * from parent natural join bestchild;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2378174960

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    42 |     5  (20)| 00:00:01 |       |    |
|   1 |  PARTITION RANGE ALL        |           |     1 |    42 |     5  (20)| 00:00:01 |     1 |     2 |
|*  2 |   HASH JOIN                 |           |     1 |    42 |     5  (20)| 00:00:01 |       |    |
|   3 |    TABLE ACCESS STORAGE FULL| PARENT    |     1 |    22 |     2   (0)| 00:00:01 |     1 |     2 |
|   4 |    TABLE ACCESS STORAGE FULL| BESTCHILD |     1 |    20 |     2   (0)| 00:00:01 |     1 |     2 |
---------------------------------------------------------------------------------------------------------

Perfect! A partitionwise join guaranteed every time, and no non-key data duplication.