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