Oracle左关联可能导致分区用不上

  今天有一条SQL没有使用上分区，导致了严重的性能问题，下面来模拟一下：

drop table test1;

drop table test2;
create table test1
(
  ID                    VARCHAR2(32),
  name                  VARCHAR2(50),
  BUREAU_CODE           VARCHAR2(4)
)
partition by list (BUREAU_CODE)
(
  partition p1 values ('0302'), 
  partition p2 values ('0303'),  
  partition p3 values ('0304')
);  
insert into test1 values(1,'a','0302');
insert into test1 values(2,'b','0302');
insert into test1 values(3,'c','0303');
insert into test1 values(4,'d','0303');
insert into test1 values(5,'e','0304');
insert into test1 values(6,'f','0304');
commit;
create table test2
(
  ID                    VARCHAR2(32),
  name                  VARCHAR2(50),
  BUREAU_CODE           VARCHAR2(4)
)
partition by list (BUREAU_CODE)
(
  partition p1 values ('0302'), 
  partition p2 values ('0303'),  
  partition p3 values ('0304')
);  
insert into test2 values(1,'a','0302');
insert into test2 values(3,'c','0303');
insert into test2 values(6,'f','0304');
commit;

SQL> set autotrace trace exp
SQL> select * from test1 t1,test2 t2
    where t1.id= t2.id
    and  t1.bureau_code = t2.bureau_code
    and t1.bureau_code='0302';
执行计划
----------------------------------------------------------
Plan hash value: 3614704095
----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |     1 |    98 |     7  (15)| 00:00:01 |       |       |
|*  1 |  HASH JOIN               |         |     1 |    98 |     7  (15)| 00:00:01 |       |       |
|   2 |   PART JOIN FILTER CREATE| :BF0000 |     1 |    49 |     3   (0)| 00:00:01 |       |       |
|   3 |    PARTITION LIST SINGLE |         |     1 |    49 |     3   (0)| 00:00:01 |     1 |     1 |
|   4 |     TABLE ACCESS FULL    | TEST2   |     1 |    49 |     3   (0)| 00:00:01 |     1 |     1 |
|   5 |   PARTITION LIST SINGLE  |         |     2 |    98 |     3   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|   6 |    TABLE ACCESS FULL     | TEST1   |     2 |    98 |     3   (0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."BUREAU_CODE"="T2"."BUREAU_CODE" AND "T1"."ID"="T2"."ID")

用不上分区的情况：
SQL> select * from test1 t1,test2 t2
    where t1.id= t2.id(+)
    and  t1.bureau_code = t2.bureau_code(+)
    and t2.bureau_code(+)='0302';
执行计划
----------------------------------------------------------
Plan hash value: 311524636
------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     6 |   588 |    10  (10)| 00:00:01 |       |       |
|*  1 |  HASH JOIN OUTER       |       |     6 |   588 |    10  (10)| 00:00:01 |       |       |
|   2 |   PARTITION LIST ALL   |       |     6 |   294 |     6   (0)| 00:00:01 |     1 |     3 |
|   3 |    TABLE ACCESS FULL   | TEST1 |     6 |   294 |     6   (0)| 00:00:01 |     1 |     3 |
|   4 |   PARTITION LIST SINGLE|       |     1 |    49 |     3   (0)| 00:00:01 |     1 |     1 |
|   5 |    TABLE ACCESS FULL   | TEST2 |     1 |    49 |     3   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID"(+) AND "T1"."BUREAU_CODE"="T2"."BUREAU_CODE"(+))