1) Where did you see that recommendation ? Because its not a good one. As I said before - choose a partition strategy that best suits *all* of your needs, eg, query performance, administration benefit etc.
2) Here's some examples of partition-wise joins - you can see the partitions need to *align*
SQL> --
SQL> -- Example 1
SQL> --
SQL>
SQL> drop table t1 purge;
Table dropped.
SQL> drop table t2 purge;
drop table t2 purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> create table t1 ( x int, y int )
2 partition by range ( x )
3 (
4 partition p1 values less than (10000),
5 partition p2 values less than (20000),
6 partition p3 values less than (30000)
7 );
Table created.
SQL>
SQL>
SQL> create table t2 ( x int, y int )
2 partition by range ( x )
3 (
4 partition p1 values less than (10000),
5 partition p2 values less than (20000),
6 partition p3 values less than (30000)
7 );
Table created.
SQL>
SQL>
SQL> insert into t1 select rownum, rownum from dual connect by level < 30000;
29999 rows created.
SQL> insert into t2 select * from t1;
29999 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','t1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','t2')
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- all partitions align, so we see partition-wise join
SQL> -- (ie, hash join "under" the partition iterator
SQL> --
SQL> set autotrace traceonly explain
SQL> select count(t1.y), count(t2.y)
2 from t1,t2
3 where t1.x = t2.x;
Execution Plan
----------------------------------------------------------
Plan hash value: 3155849676
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1050 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 20 | | | | |
| 2 | PARTITION RANGE ALL| | 29999 | 585K| 1050 (1)| 00:00:01 | 1 | 3 |
|* 3 | HASH JOIN | | 29999 | 585K| 1050 (1)| 00:00:01 | | |
| 4 | TABLE ACCESS FULL| T1 | 29999 | 292K| 525 (1)| 00:00:01 | 1 | 3 |
| 5 | TABLE ACCESS FULL| T2 | 29999 | 292K| 525 (1)| 00:00:01 | 1 | 3 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."X"="T2"."X")
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Example 2
SQL> --
SQL>
SQL>
SQL> drop table t2 purge;
Table dropped.
SQL> create table t2 ( x int, y int )
2 partition by range ( x )
3 (
4 partition p1 values less than (15000),
5 partition p3 values less than (30000)
6 );
Table created.
SQL>
SQL> --
SQL> -- all partitions do NOT align, so we do NOT see partition-wise join
SQL> --
SQL>
SQL> insert into t2 select * from t1;
29999 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','t2')
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> select count(t1.y), count(t2.y)
2 from t1,t2
3 where t1.x = t2.x;
Execution Plan
----------------------------------------------------------
Plan hash value: 666786458
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 875 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 20 | | | | |
|* 2 | HASH JOIN | | 29999 | 585K| 875 (1)| 00:00:01 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 29999 | 585K| 875 (1)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 29999 | 292K| 525 (1)| 00:00:01 | 1 | 3 |
| 5 | TABLE ACCESS FULL | T1 | 29999 | 292K| 525 (1)| 00:00:01 | 1 | 3 |
| 6 | PARTITION RANGE JOIN-FILTER| | 29999 | 292K| 351 (1)| 00:00:01 |:BF0000|:BF0000|
| 7 | TABLE ACCESS FULL | T2 | 29999 | 292K| 351 (1)| 00:00:01 |:BF0000|:BF0000|
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."X"="T2"."X")
Note
-----
- this is an adaptive plan
SQL> set autotrace off
SQL>
SQL>
SQL> --
SQL> -- Example 3
SQL> --
SQL> drop table t2 purge;
Table dropped.
SQL> create table t2 ( x int, y int )
2 partition by range ( x )
3 (
4 partition p1 values less than (10000),
5 partition p2 values less than (20000),
6 partition p3 values less than (30000),
7 partition p4 values less than (40000)
8 );
Table created.
SQL>
SQL> --
SQL> -- some partitions do align, but still a mismatch so we do NOT see partition-wise join
SQL> --
SQL>
SQL> insert into t2 select rownum, rownum from dual connect by level < 40000;
39999 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','t2')
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> select count(t1.y), count(t2.y)
2 from t1,t2
3 where t1.x = t2.x;
Execution Plan
----------------------------------------------------------
Plan hash value: 666786458
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1224 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 20 | | | | |
|* 2 | HASH JOIN | | 29999 | 585K| 1224 (1)| 00:00:01 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 29999 | 585K| 1224 (1)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 29999 | 292K| 525 (1)| 00:00:01 | 1 | 3 |
| 5 | TABLE ACCESS FULL | T1 | 29999 | 292K| 525 (1)| 00:00:01 | 1 | 3 |
| 6 | PARTITION RANGE JOIN-FILTER| | 39999 | 390K| 699 (1)| 00:00:01 |:BF0000|:BF0000|
| 7 | TABLE ACCESS FULL | T2 | 39999 | 390K| 699 (1)| 00:00:01 |:BF0000|:BF0000|
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."X"="T2"."X")
Note
-----
- this is an adaptive plan
SQL> set autotrace off
SQL>
SQL>