借助sys_connect_by_path函数的SQL改写

近期在网上看到一个这样的SQL需求,需求源自scott.emp表中的数据:

create or replace view v1 as select * from scott.emp;
create or replace view v2 as select * from scott.emp;

SQL> select * from v1;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10

14 rows selected.


SQL的需求是,得到员工编号、员工姓名、工作类别、主管编号、主管姓名信息。网上的网友是这么实现的:

SQL> select a.empno,a.ename,a.job,a.mgr as v1_mgr,b.empno as v2_mgr,b.ename from v1 a left join v2 b on a.MGR=b.empno order by 1;

     EMPNO ENAME      JOB	    V1_MGR     V2_MGR ENAME
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902	 7902 FORD
      7499 ALLEN      SALESMAN	      7698	 7698 BLAKE
      7521 WARD       SALESMAN	      7698	 7698 BLAKE
      7566 JONES      MANAGER	      7839	 7839 KING
      7654 MARTIN     SALESMAN	      7698	 7698 BLAKE
      7698 BLAKE      MANAGER	      7839	 7839 KING
      7782 CLARK      MANAGER	      7839	 7839 KING
      7788 SCOTT      ANALYST	      7566	 7566 JONES
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN	      7698	 7698 BLAKE
      7876 ADAMS      CLERK	      7788	 7788 SCOTT
      7900 JAMES      CLERK	      7698	 7698 BLAKE
      7902 FORD       ANALYST	      7566	 7566 JONES
      7934 MILLER     CLERK	      7782	 7782 CLARK

14 rows selected.

Plan hash value: 2322654302

--------------------------------------------------------------------------------------------------------------
| Id  | Operation	    | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	  1 |	     |	   7 (100)|	     |	   14 |00:00:00.01 |	  12 |
|   1 |  SORT ORDER BY	    |	   |	  1 |	  14 |	   7  (15)| 00:00:01 |	   14 |00:00:00.01 |	  12 |
|*  2 |   HASH JOIN OUTER   |	   |	  1 |	  14 |	   6   (0)| 00:00:01 |	   14 |00:00:00.01 |	  12 |
|   3 |    TABLE ACCESS FULL| EMP  |	  1 |	  14 |	   3   (0)| 00:00:01 |	   14 |00:00:00.01 |	   6 |
|   4 |    TABLE ACCESS FULL| EMP  |	  1 |	  14 |	   3   (0)| 00:00:01 |	   14 |00:00:00.01 |	   6 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MGR"="EMPNO")

此方法最终实现了目标需求,利用了自关联的方式,也就是emp表需要扫描2次,小表是OK的,emp表中主管与员工是存在层级关系的,我们能否利用上层级查询消除自关联?

SQL> set line 1000 pagesize 200
col new_name for a20
col new_mgr for a20
col mgr_name for a60
select empno,ename,job,mgr,mgr_name from (
select empno,ename,job,mgr,path,regexp_substr(path,'[^,]+',1,decode((lv-1),0,null,(lv-1))) as mgr_name from (
SELECT empno,
       ename,
       job,
       mgr,
       LEVEL lv,
       sys_connect_by_path(ename,',') AS path
FROM emp connect by prior empno=mgr start with mgr is null));SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9

     EMPNO ENAME      JOB	       MGR MGR_NAME
---------- ---------- --------- ---------- ------------------------------------------------------------
      7839 KING       PRESIDENT
      7566 JONES      MANAGER	      7839 KING
      7788 SCOTT      ANALYST	      7566 JONES
      7876 ADAMS      CLERK	      7788 SCOTT
      7902 FORD       ANALYST	      7566 JONES
      7369 SMITH      CLERK	      7902 FORD
      7698 BLAKE      MANAGER	      7839 KING
      7499 ALLEN      SALESMAN	      7698 BLAKE
      7521 WARD       SALESMAN	      7698 BLAKE
      7654 MARTIN     SALESMAN	      7698 BLAKE
      7844 TURNER     SALESMAN	      7698 BLAKE
      7900 JAMES      CLERK	      7698 BLAKE
      7782 CLARK      MANAGER	      7839 KING
      7934 MILLER     CLERK	      7782 CLARK

14 rows selected.


Plan hash value: 422387769

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				 | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time	| Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			 |	|      1 |	  |	3 (100)|	  |	14 |00:00:00.01 |	2 |
|   1 |  VIEW					 |	|      1 |	3 |	3  (34)| 00:00:01 |	14 |00:00:00.01 |	2 |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|	|      1 |	  |	       |	  |	14 |00:00:00.01 |	2 |
|   3 |    TABLE ACCESS FULL			 | EMP	|      1 |     14 |	2   (0)| 00:00:01 |	14 |00:00:00.01 |	2 |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MGR"=PRIOR NULL)
       filter("MGR" IS NULL)

利用sys_connect_by_path与regexp_substr利用层级关系截取,消除了自关联,并且emp表只访问了1次。