프로젝트 진행하다가 속도상 문제가 되는 부분이 있어 조언을 구해봅니다.
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 일 경우에만 쿼리를 수행하도록 되어 있습니다.
-- 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(+) ;