우선 순위에 따른 1건씩 조인 0 5 2,427

by 야신 [SQL Query] SQL 우선순위 튜닝 [2018.10.07 09:38:40]


example.jpg (306,903Bytes)

프로젝트 진행하다가 속도상 문제가 되는 부분이 있어 조언을 구해봅니다.

 

SELECT a.empno,a.ename,a.deptno,a.sal, b.*
FROM tgt_emp a
  ,
      ( 
       SELECT *
       FROM  ( 
                SELECT b.empno, b.ename, b.job, b.sal, b.deptno
                FROM   emp 
                WHERE a.empno = b.empno
                UNION ALL 
                SELECT b.empno, b.ename, b.job, b.sal, b.deptno
                FROM (
                      SELECT empno, ename, job, sal, deptno
                      FROM   emp
                      WHERE  a.deptno = b.deptno 
                      ORDER BY sal DESC          
                    )  b
             ) x
      WHERE ROWNUM = 1
      )  b

 

비문법적이긴 한데 원하는 쿼리가 위와 같습니다.

대상이 되는 직원의 현재 유효하면 unioin all 위의 쿼리를 타서 한건만 가져오고 ( rownum = 1 ) 이 작동

없으면 union all 아래 두번째 쿼리를 타서 같은 부서의 sal (연봉) 이 제일 높은 것을 한것 가져와 보이게 하고 싶네요.

실제쿼리에는 우선순위를 의미하는 unioin all 의 조건이 두개정도 더 있습니다 ㅡ_ㅡ;;

회사의 업무를 오라클 EMP 데모 테이블에 맞춰 바꿔 단순하게 설명해 보았는데 일단 b 의 데이타 데이타량이 많아 속도가 나지 않는 상황입니다.

회사 ERP화면에서는 union all 없이

where a.empno = b.empno(+)

이런 방식으로 처리하여 데이타가 없을 경우  후처리하여 채워넣는 방식을 사용하고 있습니다.

저 화면을 웹으로 전환하여 구현하다 보니 현재는 table function 을 사용한 pipeline 방식으로 후처리방식을 유사하게 처리중인데

속도가 나지 않고 TA 로부터 table function 은 튜닝이 힘들다는 답변을 들었습니다.

tgt_emp 도 다량의 데이타인데 그에 맞는 데이타를 우선순위에 따라 한건씩만(rownum=1) 가져올수 있게 하고 싶습니다.

 

예제를 첨부합니다.

ps) pipe function 패키지에는 b.empno is not null 일 경우에만 쿼리를 수행하도록 되어 있습니다.

 

by 우리집아찌 [2018.10.08 09:14:56]

출력되는 결과물이 작으면(웹에서는 전체다 보여주지 않으니까요.) 

sal이  null 일때만 subquery 쓰시는건 어떠신가요?


by 야신 [2018.10.09 07:19:50]

sal 이 기준이 아니라 b.empno 가 기준이라서요. 현재 pipe function 이 해당 방식을 이용한 것한 것입니다.


by 마농 [2018.10.08 10:03:33]
-- 1. test 용 emp1 테이블 생성
CREATE TABLE emp1 AS SELECT * FROM emp;
-- 2. PK 생성
ALTER TABLE emp1 ADD CONSTRAINT pk_emp1 PRIMARY KEY (empno);
-- 3. 필요 인덱스 생성
CREATE INDEX x01_emp1 ON emp1(deptno, sal, empno);
-- 4. 테스트용 row 삭제
DELETE emp1 WHERE empno = 7566;
COMMIT;
-- 5. 쿼리 수행
SELECT a.empno, a.ename, a.deptno, a.sal
     , b.empno, b.ename, b.deptno, b.sal, b.job
  FROM (SELECT a.empno, a.ename, a.deptno, a.sal
             , COALESCE(b.empno
             , (SELECT empno
                  FROM emp1
                 WHERE (deptno, sal) = (SELECT a.deptno
                                             , MAX(sal) max_sal
                                          FROM emp1
                                         WHERE deptno = a.deptno
                                         )
                   AND ROWNUM = 1
                ) ) empno_b
          FROM emp  a
             , emp1 b
         WHERE a.empno = b.empno(+)
        ) a
     , emp1 b
 WHERE a.empno_b = b.empno(+)
;

 


by 야신 [2018.10.09 07:34:04]

예전에도 알려주신 인덱스를 활용한 방법이네요. 그 때 많은 도움이 됐습니다. 감사했습니다.

테이블을 동일하게 만들어 테스트 해 보긴 했는데 적용하긴 어려울것 같네요.

일단 여기서 b 라고 가정한 테이블이 오라클ERP 의 스탠다드 테이블로 구성되어 6개 테이블 정도로 조인되어 있는 상태입니다. 하나의 테이블이라면 인덱스를 추가하여 진행해 볼텐데 애매하네요..


by 마농 [2018.10.10 09:15:28]

테이블이 하나이든 여러개이든,
서브쿼리를 이용해 조인을 하려면 인덱스는 필수입니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입