关于html5:Oracle-SQL-join-on都有什么方式表达

59次阅读

共计 1920 个字符，预计需要花费 5 分钟才能阅读完成。

咱们常常应用 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

Plan hash value: 2539735012

| 0 | SELECT STATEMENT | | 102K| 2609K| 372 (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”(+)=1989) – 同上，输入 1989 所有页游内容（这个条件在此能够疏忽）

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 www.sangpi.comright join t2 on t1.object_id=t2.object_id where t1.object_id=1989;

 T2_ID

Elapsed: 00:00:00.06
Execution Plan

Plan hash value: 2511910206

| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_IDX | 1 | 13 | 1 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_IDX | 1 | 13 | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 – access(“T2”.”OBJECT_ID”=1989)
4 – access(“T1”.”OBJECT_ID”=1989)
Note

dynamic sampling used for this statement (level=2)

Statistics

 21  recursive calls
  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

正文完