Oracle的表如何連接

147次閱讀

共計 3042 個字符，預(yù)計需要花費 8 分鐘才能閱讀完成。

這篇文章主要介紹 Oracle 的表如何連接，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

Oracle 表連接方法有四種：
● 排序合并連接（Sort Merge Join）
● 嵌套循環(huán)連接（Nested Loops Join）
● 哈希連接（Hash Join）
● 笛卡爾積（Cartesian Product）

如果 where 條件有 exists、in 或 =any 操作符 + 子查詢，則 Oracle 會將其處理為半連接，執(zhí)行計劃中對應(yīng)的關(guān)鍵字為 SEMI。

SQL select department_id,department_name from departments d where exists (select 1 from employees e where d.department_id = e.department_id and e.salary 2500);

11 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2188966913

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 10 | 230 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 10 | 230 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
——————————————————————————————–

SQL select department_id,department_name from departments d where department_id in (select department_id from employees e where d.department_id = e.department_id and e.salary 2500);

11 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2188966913

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 10 | 230 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 10 | 230 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
——————————————————————————————–

SQL select department_id,department_name from departments d where department_id=any(select department_id from employees e where d.department_id = e.department_id and e.salary 2500);

11 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2188966913

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 10 | 230 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 10 | 230 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
——————————————————————————————–

以上是“Oracle 的表如何連接”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

正文完