標籤：
oracle 的自串連（self join）操作
對於oracle資料庫來說，目前支援兩套文法，一套是oracle自己的sql文法，一套是通行標準的SQL99文法，那麼對於oracle的串連操作來說，也完全可以使用這樣的兩套文法來分別的實現。當然從效率上來說，兩者是沒有差別的。只不過從我的角度來講，oracle的文法更加簡潔而已。
比如說我們有一張表emp，表裡資料如下
[email protected]> conn scott/tiger
Connected.
[email protected]>
set linesize 120
[email protected]> set pagesize 100
[email protected]> select * from emp;
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ -------------------- ----------
7369SMITH CLERK 790217-DEC-80 800 20
7499ALLEN SALESMAN 769820-FEB-81 1600 300 30
7521WARD SALESMAN 769822-FEB-81 1250 500 30
7566JONES MANAGER 783902-APR-81 2975 20
7654MARTIN SALESMAN 769828-SEP-81 1250 1400 30
7698BLAKE MANAGER 783901-MAY-81 2850 30
7782CLARK MANAGER 783909-JUN-81 2450 10
7788SCOTT ANALYST 756619-APR-87 3000 20
7839KING PRESIDENT 17-NOV-81 5000 10
7844TURNER SALESMAN 769808-SEP-81 1500 0 30
7876ADAMS CLERK 778823-MAY-87 1100 20
7900JAMES CLERK 769803-DEC-81 950 30
7902FORD ANALYST 756603-DEC-81 3000 20
7934MILLER CLERK 778223-JAN-82 1300 10
14 rows selected.
通過觀察發現，在表中資料庫有從屬關係，比如7369smith的經理是7902，7902也是一個員工，名稱是ford，他的經理是7566jones，以此類推，我們最終可以找到公司的boss 7839 king。
現在我的需求是，找到公司裡的上下級對應關係，我們分別用oracle的文法和sql99的文法來實現，我們可以得到同樣的結果
oracle文法：我們注意到from後面表emp出現兩次，並且使用了不同的別名，也就是表自己和自己來關聯，所以我們稱這樣的串連為自串連操作。當然了，以下的例子中使用了外串連操作，我以後會單獨的說明。
[email protected]> select w.ename||‘ report for ‘ ||m.ename"Relations"
2 from emp w,emp m
3 where w.empno=m.empno(+);
Relations
--------------------------------
SMITH report for SMITH
ALLEN report for ALLEN
WARD report for WARD
JONES report for JONES
MARTIN report for MARTIN
BLAKE report for BLAKE
CLARK report for CLARK
SCOTT report for SCOTT
KING report for KING
TURNER report for TURNER
ADAMS report for ADAMS
JAMES report for JAMES
FORD report for FORD
MILLER report for MILLER
14 rows selected.
sql99文法：join on 文法的實現
[email protected]> select w.ename||‘ report for ‘ ||m.ename"Relations"
2 from emp w left outer join emp m
3 on (w.mgr=m.empno);
Relations
--------------------------------
SMITH report for FORD
ALLEN report for BLAKE
WARD report for BLAKE
JONES report for KING
MARTIN report for BLAKE
BLAKE report for KING
CLARK report for KING
SCOTT report for JONES
KING report for
TURNER report for BLAKE
ADAMS report for SCOTT
JAMES report for BLAKE
FORD report for JONES
MILLER report for CLARK
14 rows selected.
其實，oracle對於處理表中有從屬關係的記錄，提供了一種查詢方法，我們稱之為層次查詢，來看一個例子
[email protected]> select ename ||‘ report for ‘|| priorename
2 from emp
3 start withempno=7839 ---規定遍曆從屬關係樹的起點
4 connect by priorempno=mgr; ----規定了遍曆的方向
ENAME||‘REPORTFOR‘||PRIORENAME
--------------------------------
KING report for
JONES report for KING
SCOTT report for JONES
ADAMS report for SCOTT
FORD report for JONES
SMITH report for FORD
BLAKE report for KING
ALLEN report for BLAKE
WARD report for BLAKE
MARTIN report for BLAKE
TURNER report for BLAKE
JAMES report for BLAKE
CLARK report for KING
MILLER report for CLARK
14 rows selected.
為了使查詢的結果更具有可讀性，我們藉助一個函數SYS_CONNECT_BY_PATH來做一下處理，來看結果：
[email protected]> SELECT LPAD(‘ ‘,2*level-1)||SYS_CONNECT_BY_PATH(ename, ‘/‘) "Path"
2 FROM emp
3 START WITH empno =7839
4 CONNECT BY PRIORempno=mgr;
Path
-------------------------------------------------------------------------------
/KING
/KING/JONES
/KING/JONES/SCOTT
/KING/JONES/SCOTT/ADAMS
/KING/JONES/FORD
/KING/JONES/FORD/SMITH
/KING/BLAKE
/KING/BLAKE/ALLEN
/KING/BLAKE/WARD
/KING/BLAKE/MARTIN
/KING/BLAKE/TURNER
/KING/BLAKE/JAMES
/KING/CLARK
/KING/CLARK/MILLER
14 rows selected.
通過上面的結果，你是否發現，從屬關係更加清晰，也更加直觀呢。
當然了對於我們來講，好看的格式化的輸出只是錦上添花，更重要的是語句的執行的速度，也就是效能的考慮
藉助於autotrace工具，我們來看語句的執行計畫
[email protected]> set autotrace trace exp
[email protected]>
[email protected]> SELECT LPAD(‘ ‘,2*level-1)||SYS_CONNECT_BY_PATH(ename, ‘/‘) "Path"
2 FROM emp
3 START WITH empno =7839
4 CONNECT BY PRIORempno=mgr;
Execution Plan
----------------------------------------------------------
Plan hash value: 3613731379
---------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
---------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 14| 196| 3 (0)| 00:00:01 |
|* 1 | CONNECT BY WITHFILTERING | | | | | |
| 2| TABLE ACCESS BY INDEX ROWID|EMP | | | | |
|* 3| INDEXUNIQUESCAN | PK_EMP| 1| 4| 0 (0)| 00:00:01 |
|* 4 | HASHJOIN | | | | | |
| 5| CONNECT BYPUMP | | | | | |
| 6| TABLEACCESSFULL | EMP | 14| 196| 3 (0)| 00:00:01 |
| 7| TABLE ACCESSFULL | EMP | 14| 196| 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7839)
3 - access("EMPNO"=7839)
4 - access("MGR"=NULL)
[email protected]> select w.ename||‘ report for ‘ ||m.ename"Relations"
2 from emp w,emp m
3 wherew.empno=m.empno(+);
Execution Plan
----------------------------------------------------------
Plan hash value: 2199491010
---------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
---------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 14| 280| 4 (0)| 00:00:01 |
| 1 | NESTEDLOOPSOUTER | | 14| 280| 4 (0)| 00:00:01 |
| 2| TABLE ACCESSFULL | EMP | 14| 140| 3 (0)| 00:00:01 |
| 3| TABLE ACCESS BY INDEX ROWID|EMP | 1 | 10| 1 (0)| 00:00:01 |
|* 4| INDEXUNIQUESCAN | PK_EMP| 1| | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 -access("W"."EMPNO"="M"."EMPNO"(+))
通過上面的例子我們可以發現，層次查詢的速度也要快於自串連操作，當然了，我們在這裡的討論，並沒有考慮兩種語句對其他資源的影響。
Oracle SQL篇（二）oracle自串連操作
