ORACLE SCALAR SUB QUERY
from 실행계획으로 배우는 고성능 데이터베이스 튜닝 | 비팬북스
1. SCALAR SUB QUERY
SELECT A,(SELECT 절) FROM TABLE
위에서 바깥의 SELECT 절을 주쿼리 괄호안의 SELECT 절(SELECT-LIST 에 오는)을 SCALAR SUB QUERY라 한다.
2. SCALAR SUB QUERY 의 특징
SQL> GET SCLR1
1 select empno,ename,
2 (select dname from dept
3 where deptno = e.deptno) dname
4* from emp e
SQL> @disp
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 6hw56q8y791gn, child number 0
-------------------------------------
select empno,ename, (select dname from dept where deptno = e.deptno) dname
from emp e
Plan hash value: 2981343222
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
| 3 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:B1)
A. 스칼라서브 쿼리는 조인이며 동시에 함수이다.
위 실행계획을 보면 PLAN 에 명시적으로 JOIN OPERATION 은 표현되어 있지 않다.
여기서 조인을 추정할 수 있는 것은 인덱스의 사용여부이다.
조인방법은 대표적으로 NL JOIN, SORT MERGE JOIN, HASH JOIN 등이 있는데 NL JOIN 을 제외한 나머지 두 방법은 상수값을 처리범위의 감소조건으로 넘기지 못한다.
즉 스스로 처리범위를 줄이는 수밖에 없다.
다시 실행계획으로 돌아가 이야기를 하면 EMP 에 대해 FULL SCAN 을 해 PK_DEPT
키에 엑세스한다. 이 인덱스는 DEPTNO (여기서 조인의 연결키로 사용) 여기서 인덱스에 엑세스한 시점에 이미 상수값을 넘겨받은 걸 알 수 있고 이는 NL JOIN 을 의미한다. 처리범위를 줄이지도 못하는데 인덱스를 풀스캔하고 테이블에 대해 일일이 랜덤엑세스할 이유가 없다.
스칼라서브 쿼리가 조인이라는 것은 위 이야기로부터 얻을 수 있다.
스칼라 서브쿼리가 함수라는 것은 어떤의미인지 이야기해보자.
위에서 이야기 한 것 같이 실행계획을 보면 NL JOIN 으로 풀렸다. DRIVING 은 EMP
테이블이다. 여기에서 EMP.DEPTNO 을 상수값으로 받아 DEPTNO 에 해당하는
DEPT.DNAME 을 리턴한다.
값이 입력되고 이에 해당하는 (하나의)값을 리턴한다는 부분이 함수랑 판박이이다. 물론 함수와 다른 점이 존재하고 사용을 위한 제약이 추가적으로 있지만 이는 뒤에서 이야기 하겠다.
B. 조인순서가 결정되어있다.
조인순서를 결정한다는 것은 from 절에 여러테이블이 정의되어 있을 때의 이야기다.
이 경우 leading 이나 ordered 를 사용해 유도할 수 있지만 위에서 사용한 스칼라 서브쿼리는 애당초 SELECT-LIST 에 온다.
조인순서는 emp를 엑세스 후 dept 를 엑세스한다.
후에 스칼라서브쿼리를 OUTER JOIN 으로 변경하는 걸 이야기 할텐데 OUTER JOIN 역시 엑세스 순서가 고정되어 있다.
C. 스칼라 서브쿼리는 주쿼리에서 추출되는 데이터 건수만큼 반복수행된다.
D. 스칼라 서브 쿼리의 조건 중 처리범위를 가장 줄일 수 있는 조건은 일반적으로 조인조건이다. 간단히 위의 emp 와 dept 의 관계를 가지고 이야기 하겠다. 두 테이블은 m:1 의 관계를 가진다. 두 테이블의 연결고리는 deptno 이고 이는 emp에서 fk dept 에서 pk 로 정의되어 있다. emp.deptno 조건이 상수값으로 넘어가면 이에 대한 값을 index unique scan 한다. 당연히 조인조건을 조건으로 사용하는게 좋을 수 밖에 없다.
3. SCALAR SUB QUERY 와 OUTER JOIN
SQL> get sclr1
1 select empno,ename,
2 (select dname from dept
3 where deptno = e.deptno) dname
4* from emp e
SQL> get sclr2
1 select e.empno,e.ename,d.dname
2 from emp e,dept d
3* where d.deptno(+) = e.deptno
SCALAR SUB QUERY 는 위와 같이 OUTER JOIN 을 사용해 변경하면 된다.
위의 경우는 주쿼리 와 스칼라 서브쿼리의 ROWS 관계가 M:1 이었다. 반대의 경우는
앞서 이야기한 스칼라서브쿼리는 일종의 함수이기 때문에 (값 하나에 하나의 리턴)
에러가난다.
4. 스칼라 서브쿼리 : 주쿼리 관계가 m:1 인 경우의 조치사항
앞서 이야기 한 대로 스칼라서브쿼리는 함수와도 같기 때문에 값 하나에 대하여 하나의
값을 리턴해야 한다. 상식적으로도 row를 결과로 하나씩 산출한다 했을 때 하나의 입력에
대해 두개의 결과가 나온다면 한 row 의 특정 컬럼값이 두개있는 상태가 된다.
SQL> get sclr5
1 select dname,
2 (select ename from emp e
3 where e.deptno = d.deptno)
4* from dept d
SQL> /
(select empno,ename from emp
*
2행에 오류:
ORA-00913: 값의 수가 너무 많습니다
이를 해결하기 위한 방법으로 스칼라서브쿼리의 결과를 입력에 대해 1:1 이 되게 만드는
방법이 있다. 이 방법은 aggregation function 을 사용할 수 도 있으며 결과가 하나가
나오도록 강제하는 방법도 있다. (rownum = 1)