Oracle's self-JOIN operation
For Oracle databases, there are two sets of grammars currently supported, one for Oracle's own SQL syntax and one for the standard SQL99 syntax, and two sets of syntax for Oracle's connection operations can be implemented separately. Of course, in terms of efficiency, there is no difference between the two. But from my point of view, Oracle's syntax is more concise.
For example, we have a list of EMP, the table data is as follows
[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
Rows selected.
The observation found that the database in the table has dependencies, such as 7369smith Manager is 7902,7902 also an employee, the name is Ford, his manager is 7566jones, and so on, we can eventually find the company's boss 7839 King.
Now my demand is to find the company's subordinate relationship, we use Oracle syntax and SQL99 syntax to achieve, we can get the same result
Oracle Syntax: We notice that the From Back table EMP appears two times and uses a different alias, that is, the table itself is associated with itself, so we call such a connection as a self-connect operation. Of course, the following example uses an outer JOIN operation, which I will describe separately later.
[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
Rows selected.
SQL99 syntax: implementation of join on syntax
[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
Rows selected.
In fact, Oracle provides a query method for working with records that have dependencies on tables, which we call hierarchical queries, to see an example
[email protected]> Select Ename | | Priorename
2 from EMP
3 Start withempno=7839---Specify the starting point for traversing the dependency tree
4 Connect by Priorempno=mgr; ----Specifies the direction of the Traverse
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
Rows selected.
To make the results of the query more readable, let's take a look at the result with a function 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
Rows selected.
From the above results, do you find that the subordinate relationship is clearer and more intuitive.
Of course, for us, good-looking formatted output is just icing on the cake, and more importantly, the speed of the execution of the statement, that is, performance considerations
With the help of the Autotrace tool, we look at the execution plan of the statement
[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 "(+))
From the above example, we can find that the level of query speed is faster than the self-join operation, of course, we are here to discuss, and did not consider the two statements on the impact of other resources.
Oracle SQL Chapter (ii) Oracle self-connect operation
