데이터베이스 질문과 답변
최근에 전 직원 직속 임원 조회 쿼리를 부탁을 받아 작성한 SQL 입니다.
직속 임원의 의미
- 조직도 상의 해당 사번의 가장 근접한 상위부서 혹은 동일부서의 임원
- 본인이 임원일 경우 본인
제가 작성한것과 다른 관점 혹은 성능상 더 좋은 방법이 있는지 궁금해서 글 올립니다.
테이블은 WITH절로 구현해 놨습니다.
제가 작성한 SQL은 아래와 같습니다.
WITH T_ORGAN_TREE AS (
/* 조직도 트리 테이블 (실데이터 : 약 2,000건)
SEQ_DEPT : 부서코드
UP_SEQ_DEPT : 상위부서코드
*/
SELECT 1 SEQ_DEPT, NULL UP_SEQ_DEPT FROM DUAL UNION ALL
SELECT 2 SEQ_DEPT, 1 UP_SEQ_DEPT FROM DUAL UNION ALL
SELECT 3 SEQ_DEPT, 2 UP_SEQ_DEPT FROM DUAL UNION ALL
SELECT 4 SEQ_DEPT, 3 UP_SEQ_DEPT FROM DUAL UNION ALL
SELECT 5 SEQ_DEPT, 2 UP_SEQ_DEPT FROM DUAL UNION ALL
SELECT 6 SEQ_DEPT, 1 UP_SEQ_DEPT FROM DUAL
)
, T_EMP_INFO AS (
/* 사원정보 테이블 (실데이터 : 약 2만건)*/
SELECT '사번01' EMP_CD, 1 SEQ_DEPT, '임원' POSITION FROM DUAL UNION ALL
SELECT '사번02' EMP_CD, 1 SEQ_DEPT, '임원' POSITION FROM DUAL UNION ALL
SELECT '사번03' EMP_CD, 2 SEQ_DEPT, '임원' POSITION FROM DUAL UNION ALL
SELECT '사번04' EMP_CD, 3 SEQ_DEPT, '임원' POSITION FROM DUAL UNION ALL
SELECT '사번05' EMP_CD, 3 SEQ_DEPT, '팀장' POSITION FROM DUAL UNION ALL
SELECT '사번06' EMP_CD, 3 SEQ_DEPT, '사원' POSITION FROM DUAL UNION ALL
SELECT '사번07' EMP_CD, 4 SEQ_DEPT, '사원' POSITION FROM DUAL UNION ALL
SELECT '사번08' EMP_CD, 4 SEQ_DEPT, '사원' POSITION FROM DUAL UNION ALL
SELECT '사번09' EMP_CD, 4 SEQ_DEPT, '사원' POSITION FROM DUAL UNION ALL
SELECT '사번10' EMP_CD, 5 SEQ_DEPT, '임원' POSITION FROM DUAL UNION ALL
SELECT '사번11' EMP_CD, 5 SEQ_DEPT, '팀장' POSITION FROM DUAL
)
, TEMP_TD AS (
/* 조직도 TREE 만들기 */
SELECT A.SEQ_DEPT, SYS_CONNECT_BY_PATH(SEQ_DEPT, '/') PATH, LEVEL LV
FROM T_ORGAN_TREE A
START WITH UP_SEQ_DEPT IS NULL
CONNECT BY PRIOR SEQ_DEPT = UP_SEQ_DEPT
)
, TEMP_ORGAN_EMP_MAP AS (
/* 조직도 - 인사정보 맵핑 */
SELECT TD.SEQ_DEPT, PATH, LV, EI.EMP_CD, EI.POSITION
FROM TEMP_TD TD
JOIN T_EMP_INFO EI ON TD.SEQ_DEPT = EI.SEQ_DEPT
ORDER BY PATH, EMP_CD
)
, TEMP_POSITION_EMP AS (
/* 조직도 - 인사정보 맵핑 테이블에서 임원정보 추출*/
SELECT *
FROM TEMP_ORGAN_EMP_MAP
WHERE POSITION = '임원'
)
SELECT A.EMP_CD "사번"
,MIN(CASE WHEN A.POSITION = '임원' THEN A.EMP_CD
ELSE B.EMP_CD
END) KEEP(DENSE_RANK LAST ORDER BY B.LV, B.EMP_CD) "직속 임원"
FROM TEMP_ORGAN_EMP_MAP A
JOIN TEMP_POSITION_EMP B ON A.PATH LIKE B.PATH || '%'
GROUP BY A.EMP_CD
by jkson
[2017.03.29 16:17:37]
생각 없이 만드는 게 제 주특기라 검토해보세요..
SELECT EMP_CD
, MAX(LEADER_CD)
KEEP (DENSE_RANK FIRST ORDER BY LV, DECODE(MY_DEPT,LEADER_DEPT,1,99)) LEADER_CD
FROM (SELECT CONNECT_BY_ROOT (EMP_CD) EMP_CD
,CONNECT_BY_ROOT (SEQ_DEPT) MY_DEPT
,EMP_CD LEADER_CD, SEQ_DEPT LEADER_DEPT
,LEVEL LV
FROM (SELECT EMP_CD
, POSITION
, B.SEQ_DEPT
, B.UP_SEQ_DEPT
FROM T_EMP_INFO A, T_ORGAN_TREE B
WHERE A.SEQ_DEPT = B.SEQ_DEPT)
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY NOCYCLE (PRIOR UP_SEQ_DEPT = SEQ_DEPT
AND PRIOR POSITION != '임원')
OR (PRIOR SEQ_DEPT = SEQ_DEPT
AND POSITION = '임원'))
GROUP BY EMP_CD
by 샤프하나
[2017.03.31 11:03:14]
답변 감사합니다.
답변해주신 쿼리는 조직도 테이블과 인사정보 테이블의 조인 이후에 CONNECT BY절을 이용한 쿼리인데 동일 부서에 여러명의 인원이 있을 경우 불필요한 부하가 많이 일어나는 쿼리가 되는 방식이라 쿼리의 성능적인 부분 이슈가 발생할거라 생각이 듭니다.
by jkson
[2017.03.31 16:23:33]
그렇네요. 성능상 더 좋은 방법이 있나 여쭤보신 건데.. 역시 생각 없이 만들었네요.
인원별로 상위 임원을 뽑는 방식이 아니라 부서별로 상위 임원을 뽑는 방식으로 처리해야 맞는 거네요^^
by 마농
[2017.03.29 16:42:49]
SELECT a.emp_cd
, DECODE(a.position, '임원', a.emp_cd, b.emp_cd) up_emp_cd
FROM t_emp_info a
INNER JOIN
(SELECT o.seq_dept
, REGEXP_SUBSTR(
RTRIM(SYS_CONNECT_BY_PATH(e.emp_cd, ' '))
, '[^ ]+$') emp_cd
FROM t_organ_tree o
LEFT OUTER JOIN
(SELECT seq_dept
, MIN(emp_cd) emp_cd -- 부서 대표 임원 1명만
FROM t_emp_info
WHERE position = '임원'
GROUP BY seq_dept
) e
ON o.seq_dept = e.seq_dept
START WITH o.up_seq_dept IS NULL
CONNECT BY PRIOR o.seq_dept = o.up_seq_dept
) b
ON a.seq_dept = b.seq_dept
ORDER BY emp_cd
;
by 샤프하나
[2017.03.31 15:22:05]
각 부서에 상위임원정보를 어떻게 맵핑할까라는 부분에서 고민을 많이 했었는데 해당하는 방법은 생각하지 못했었습니다.
실제 플랜으로 쿼리 비교 해봤습니다.
답변 감사드립니다.
실제 임원정보를 판단하기 위한 테이블이 공통코드 테이블을 참조해야 되는 부분이라 T_공통코드 라는 테이블이 추가된 내역입니다.
기존 쿼리 :
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2807 (100)| 5699 |00:00:00.32 | 878 | 52 | 52 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 5699 |00:00:00.32 | 878 | 52 | 52 | | | |
| 2 | LOAD AS SELECT | | 1 | | 0 |00:00:00.04 | 707 | 0 | 52 | 530K| 530K| 530K (0)|
| 3 | SORT ORDER BY | | 1 | 185 (2)| 5784 |00:00:00.03 | 652 | 0 | 0 | 594K| 594K| 527K (0)|
|* 4 | HASH JOIN | | 1 | 184 (1)| 5784 |00:00:00.02 | 652 | 0 | 0 | 2226K| 2226K| 4259K (0)|
| 5 | JOIN FILTER CREATE | :BF0000 | 1 | 9 (12)| 1962 |00:00:00.01 | 22 | 0 | 0 | | | |
| 6 | VIEW | | 1 | 9 (12)| 1962 |00:00:00.01 | 22 | 0 | 0 | | | |
|* 7 | CONNECT BY NO FILTERING WITH START-WITH| | 1 | | 1962 |00:00:00.01 | 22 | 0 | 0 | | | |
| 8 | TABLE ACCESS STORAGE FULL | T_ORGAN_TREE | 1 | 8 (0)| 1962 |00:00:00.01 | 22 | 0 | 0 | 1025K| 1025K| |
| 9 | JOIN FILTER USE | :BF0000 | 1 | 175 (0)| 6157 |00:00:00.01 | 630 | 0 | 0 | | | |
|* 10 | TABLE ACCESS STORAGE FULL | T_EMP_INFO | 1 | 175 (0)| 6157 |00:00:00.01 | 630 | 0 | 0 | 1025K| 1025K| |
| 11 | SORT GROUP BY | | 1 | 2622 (1)| 5699 |00:00:00.28 | 166 | 52 | 0 | 761K| 761K| 676K (0)|
|* 12 | HASH JOIN | | 1 | 208 (1)| 30463 |00:00:00.25 | 166 | 52 | 0 | 2244K| 2244K| 4971K (0)|
| 13 | VIEW | | 1 | 24 (0)| 5784 |00:00:00.01 | 56 | 52 | 0 | | | |
| 14 | TABLE ACCESS STORAGE FULL | SYS_TEMP_1FD9D86A9_C81691B6 | 1 | 24 (0)| 5784 |00:00:00.01 | 56 | 52 | 0 | 1025K| 1025K| |
| 15 | MERGE JOIN CARTESIAN | | 1 | 184 (1)| 69408 |00:00:00.02 | 110 | 0 | 0 | | | |
| 16 | SORT UNIQUE | | 1 | 39 (0)| 12 |00:00:00.01 | 54 | 0 | 0 | 2048 | 2048 | 2048 (0)|
|* 17 | TABLE ACCESS BY INDEX ROWID | T_공통코드 | 1 | 39 (0)| 12 |00:00:00.01 | 54 | 0 | 0 | | | |
|* 18 | INDEX RANGE SCAN | T_공통코드_PK | 1 | 6 (0)| 67 |00:00:00.01 | 4 | 0 | 0 | 1025K| 1025K| |
| 19 | BUFFER SORT | | 12 | 145 (1)| 69408 |00:00:00.01 | 56 | 0 | 0 | 478K| 478K| 424K (0)|
| 20 | VIEW | | 1 | 24 (0)| 5784 |00:00:00.01 | 56 | 0 | 0 | | | |
| 21 | TABLE ACCESS STORAGE FULL | SYS_TEMP_1FD9D86A9_C81691B6 | 1 | 24 (0)| 5784 |00:00:00.01 | 56 | 0 | 0 | 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
변경 쿼리 :
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 380 (100)| 5699 |00:00:00.06 | 1336 | | | |
| 1 | SORT ORDER BY | | 1 | 380 (1)| 5699 |00:00:00.06 | 1336 | 267K| 267K| 237K (0)|
|* 2 | HASH JOIN | | 1 | 379 (1)| 5699 |00:00:00.06 | 1336 | 3248K| 3248K| 4287K (0)|
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 205 (2)| 1771 |00:00:00.05 | 706 | | | |
|* 4 | VIEW | | 1 | 205 (2)| 1771 |00:00:00.05 | 706 | | | |
|* 5 | CONNECT BY NO FILTERING WITH START-WITH| | 1 | | 1962 |00:00:00.02 | 706 | | | |
|* 6 | HASH JOIN RIGHT OUTER | | 1 | 204 (1)| 1962 |00:00:00.01 | 706 | 3330K| 3330K| 5201K (0)|
| 7 | VIEW | | 1 | 196 (2)| 393 |00:00:00.01 | 684 | | | |
| 8 | HASH GROUP BY | | 1 | 196 (2)| 393 |00:00:00.01 | 684 | 3105K| 3105K| 4827K (0)|
|* 9 | HASH JOIN RIGHT SEMI | | 1 | 195 (1)| 489 |00:00:00.01 | 684 | 2091K| 2091K| 3694K (0)|
| 10 | JOIN FILTER CREATE | :BF0001 | 1 | 19 (0)| 12 |00:00:00.01 | 54 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | T_공통코드 | 1 | 19 (0)| 12 |00:00:00.01 | 54 | | | |
|* 12 | INDEX RANGE SCAN | T_공통코드_PK | 1 | 6 (0)| 67 |00:00:00.01 | 4 | 1025K| 1025K| |
| 13 | JOIN FILTER USE | :BF0001 | 1 | 175 (0)| 504 |00:00:00.01 | 630 | | | |
|* 14 | TABLE ACCESS STORAGE FULL | T_EMP_INFO | 1 | 175 (0)| 504 |00:00:00.01 | 630 | 1025K| 1025K| |
| 15 | TABLE ACCESS STORAGE FULL | T_ORGAN_TREE | 1 | 8 (0)| 1962 |00:00:00.01 | 22 | 1025K| 1025K| |
| 16 | JOIN FILTER USE | :BF0000 | 1 | 175 (0)| 6036 |00:00:00.01 | 630 | | | |
|* 17 | TABLE ACCESS STORAGE FULL | T_EMP_INFO | 1 | 175 (0)| 6036 |00:00:00.01 | 630 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------------
) 버튼을 클릭하여 작성 하시면 됩니다.