是先filter再join的,做了一个小例子
create table a
(id number,
name varchar2(20))
create table b
(id number,
name varchar2(20))
insert into a values (1,'a');
insert into a values (2,'b');
insert into a values (3,'c');
insert into b values (1,'e');
insert into b values (2,'r');
insert into b values (3,'g');
commit;
gather statistics
SQL> select * from a,b where a.id=b.id and (a.name='a' or b.name='g');
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 a 1 e
3 c 3 g
Execution Plan
----------------------------------------------------------
Plan hash value: 652036164
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 20 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 20 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| A | 3 | 15 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| B | 3 | 15 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
filter("A"."NAME"='a' OR "B"."NAME"='g')
注意看
1
TABLE ACCESS FULL| A | 3
TABLE ACCESS FULL| B | 3
都是3行,所以未过滤
2
1 - access("A"."ID"="B"."ID")
filter("A"."NAME"='a' OR "B"."NAME"='g')
是在1处过滤,先join再过滤
对比下面的
SQL> select * from a,b where a.id=b.id and a.name='a'
2 ;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 a 1 e
Execution Plan
----------------------------------------------------------
Plan hash value: 652036164
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 10 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| A | 1 | 5 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| B | 3 | 15 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("A"."NAME"='a')
注意
|* 2 | TABLE ACCESS FULL| A | 1 |
和
2 - filter("A"."NAME"='a')
在TABLE ACCESS FULL已经过滤,只有1行