데이터베이스 질문과 답변
프로젝트 진행하다가 속도상 문제가 되는 부분이 있어 조언을 구해봅니다.
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 일 경우에만 쿼리를 수행하도록 되어 있습니다.
출력되는 결과물이 작으면(웹에서는 전체다 보여주지 않으니까요.)
sal이 null 일때만 subquery 쓰시는건 어떠신가요?
sal 이 기준이 아니라 b.empno 가 기준이라서요. 현재 pipe function 이 해당 방식을 이용한 것한 것입니다.
-- 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(+)
;
예전에도 알려주신 인덱스를 활용한 방법이네요. 그 때 많은 도움이 됐습니다. 감사했습니다.
테이블을 동일하게 만들어 테스트 해 보긴 했는데 적용하긴 어려울것 같네요.
일단 여기서 b 라고 가정한 테이블이 오라클ERP 의 스탠다드 테이블로 구성되어 6개 테이블 정도로 조인되어 있는 상태입니다. 하나의 테이블이라면 인덱스를 추가하여 진행해 볼텐데 애매하네요..
테이블이 하나이든 여러개이든,
서브쿼리를 이용해 조인을 하려면 인덱스는 필수입니다.