데이터베이스 질문과 답변

oracle 서브쿼리 안의 인라인뷰에서 메인쿼리 사용 0 6 3,419

by 리뉴사 [SQL Query] 서브쿼리 인라인뷰 [2016.04.08 09:39:38]

select 문의 서브쿼리 내에서 

결과조건을 1개로 하려고 인라인뷰로 해서 rownum = 1 조건으로 1행만 출력하려하는데

이렇게하면 서브쿼리 내의 인라인 뷰에서 메인쿼리를 조건절에 사용할수가 없다고 나오는데

어떻게해야 메인쿼리를 사용할 수 잇을까요?

 

?
1
2
3
4
5
6
7
8
9
10
select e.eno
       , (select *
         from(
              select d.dname
              from dept d
              where e.dno = d.dno
              order by d.dname
              )
         where rownum = 1 )
from employee e

위와 비슷한 상황입니다

by 창조의날개 [2016.04.08 10:00:17]
?
1
2
3
4
5
6
7
8
select e.eno
       , (
          select min(d.dname)
          from dept d
          where e.dno = d.dno
          ) dname
from employee e
;

 

by 리뉴사 [2016.04.08 10:16:20]

답변 감사합니다

혹시 메인쿼리의 조건을 가져다가 쓸 수는 없는건가요?

by jkson [2016.04.08 10:23:49]

일반적인 2-depth 해결 방안은

?
1
2
3
4
5
6
7
8
SELECT E.ENO, (WITH DEPT1 AS (SELECT DNO, D.DNAME
                                FROM DEPT D
                              ORDER BY D.DNAME)
               SELECT DNAME
                 FROM DEPT1
                WHERE DNO = E.DNO
                  AND ROWNUM = 1)
  FROM EMPLOYEE E

이런식으로 with문을 사용하여 해결합니다.

그런데 문의하신 쿼리는 의미상 제일 작은 dname을 출력하는 것이니

굳이 with문을 사용할 필요 없이 창조님 답변처럼 min을 사용하시는 게 간편하죠.

by 리뉴사 [2016.04.08 10:51:26]

답변 감사합니다.

2-dept 라고 하는군요 이경우에대한 해결책이 궁금했습니다.

두분다 감사드립니다.

 

by 이재현 [2016.04.08 14:11:49]

정말 획기적인 방법인데요.. 근데 이거 ROW단위로 FTS 발생 하지 않나요?

테스트 해봐야 겠네요.

창조의날개님의 방식에서 인덱스 컬럼 순서가 ( dno  , dname ) 존재 하면 first row 오퍼레이션이 발생해서 효율적으로 보이는군요.

위 방식 테스트 해볼게요.

by 마농 [2016.04.11 10:46:19]

굳이 WITH 문 사용할 필요는 없죠.
인라인뷰 사용해도 마찬가지 입니다.
문제는 조건절이 2dept 로 자동으로 침투해 주느냐가 관건인죠.
11g 테스트 결과 with 나 inlineview 나 동일하게 침투하는 것 확인되네요.

?
1
2
3
4
5
6
7
8
SELECT e.eno
     , (SELECT dname
          FROM (SELECT dno, dname FROM dept ORDER BY dname) dept1
         WHERE dno = e.dno
           AND ROWNUM = 1
        ) x
  FROM employee e
;

정렬항목과 추출항목이 동일한 경우 그냥 MIN 을 사용하면 되구요.
정렬항목과 추출항목이 다른 경우엔 다양한 시도를 해볼 수 있겠네요.
 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 1. 집계함수에 KEEP 을 이용
SELECT e.eno
     , (SELECT MIN(loc) KEEP(DENSE_RANK FIRST ORDER BY dname)
          FROM dept
         WHERE dno = e.dno
        ) x
  FROM employee e
;
-- 2. 적절한 인덱스(dno, dname)가 존재하는 경우 인덱스 힌트를 이용
SELECT e.eno
     , (SELECT /*+ INDEX(dept 인덱스명) */ loc
          FROM dept
         WHERE dno = e.dno
           AND ROWNUM = 1
         ORDER BY dname
        ) x
  FROM employee e
;

 

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