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

╰+攻爆jí腚メ 2022-05-23 09:28 40阅读 0赞

今天有一条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"(+))