咱们常常应用 join 相干语句做关联查问,那么在 join 连贯形式后边,on 联合 and 和 where 后果会产生什么变动呢?
在应用 join on 时 留神 and where 区别和如何应用
join on and
join on and 形式 相似于 on 条件 1 and on 条件 2,都是 基于 join 关联两个表后果,取出关联后数据。举例如下
select t2.object_id t2_id from t1 right join t2 on t1.object_id=t2.object_id and t1.object_id=1989;
– 输入后果太多,省略,看 下边 access 局部
92937 rows selected.
Elapsed: 00:00:05.33
Execution Plan
(1)| 00:00:05 |
|* 1 | HASH JOIN RIGHT OUTER| | 102K| 2609K| 372 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 1 | 13 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 102K| 1304K| 371 (1)| 00:00:05 |
Predicate Information (identified by operation id):
1 – access(“T1″.”OBJECT_ID”(+)=”T2″.”OBJECT_ID”) — t1 右连贯 t2 的 object_id,以 t2 输入为主,也就是输入 t2 所有内容
2 – access(“T1”.”OBJECT_ID”http://www.coubai.com(+)=1989) – 同上,输入 1989 所有内容(这个条件在此能够疏忽)
Note
- dynamic sampling used for this statement (level=2)
Statistics
16 recursive calls
0 db block gets
7580 consistent gets
1 physical reads
0 redo size
1699670 bytes sent via SQL*Net to client
68668 bytes received via SQL*Net from client
6197 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
92937 rows processed1
join on where
join on where,是 join on 连贯之后对后果再进页游进行筛选 (为达到执行效率最有,是先进性 where 条件筛选,再 join 关联),举例如下
SQL> select t2.object_id t2_id from t1 right join t2 on t1.object_id=t2.object_id where t1.object_id=1989;
T2_ID
1989
Elapsed: 00:00:00.06
Execution Plan
2 – access(“T2”.”OBJECT_ID”=1989)
4 – access(“T1”.”OBJECT_ID”=1989)
Note
- dynamic sampling used for this statement (level=2)
Statistics
0 db block gets
147 consistent gets
3 physical reads
0 redo size
524 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed