교재의 특성 세가지.
1. 최대 결과 건수만큼 반복적으로 수행된다.
2. 추출되는 데이터는 항상 1건만 유효하다.
3. 데이터가 추출되지 않아도 된다.
이에 앞서 스칼라서브 쿼리의 사전지식(?)을 다져 보자.
#############################################################
===================== 교재의 예 =====================
SQL > SELECT 사원번호,사원명,
(SELECT 부서명
FROM 부서 B
WHERE A.부서번호 = B.부서번호) 부서명,
연차일수
FROM 사원 A
WHERE 입사일 > '20040101'
AND 직급 = '대리';
=====================================================
실습1. 직업(JOB)이 SALESMAN이고 입사일(HIREDATE)이 1981년 5월 이후인 직원들의 사번(empno)과 성명(ename),
부서명(dname) 급여(SAL)를 추출하시오.(SCOTT)
답.
select empno,ename,
(select dname
from dept b
where a.deptno=b.deptno) dname,
sal
from employee a
where HIREDATE > to_date(19810501 , 'YYYYMMDD')
and JOB = 'SALESMAN';
실습2. 폰번호(PHONE_NUMBER)가 011로 시작되며, 입사일(HIRE_DATE)이 1981년 5월 이후인 직원들의
고용번호(EMPLOYEE_ID)와 성(LAST_NAME)
답.
select EMPLOYEE_ID,LAST_NAME,
(select DEPARTMENT_NAME
from DEPARTMENTS b
where a.DEPARTMENT_ID=b.DEPARTMENT_ID) DEPARTMENT_NAME,
PHONE_NUMBER
from employees a
where HIRE_DATE > to_date(19910501 , 'YYYYMMDD')
and PHONE_NUMBER like '011%' ;
###################################################################
SQL> select e.empno, e.ename, e.sal, e.hiredate,
(select d.dname from dept d where d.deptno = e.deptno) dname
from emp e
where sal >= 2000;
EMPNO ENAME SAL HIREDATE DNAME
6 rows selected.
-- 위의 쿼리와 아래 쿼리는 같은 결과
SQL> select /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where d.deptno(+) = e.deptno
and e.sal >= 2000;
-- 그렇다면 아래 쿼리와는 다른가?
SQL> select /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where d.deptno = e.deptno
and e.sal >= 2000;
실습.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
4 rows selected.
없는 부서명을 가지고 있는 값을 삽입.
insert into emp values (8500,'JAEHO','DBA',7839,to_date('20130101','yyyymmdd'),8000,500,50);
commit;
에러
-- 테이블 구조상 임시로 FK제거
– ALTER TABLE SCOTT.EMP ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES SCOTT.DEPT (DEPTNO);
ALTER TABLE SCOTT.EMP drop CONSTRAINT FK_DEPTNO;
select * from emp e;
SQL> select * from emp e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
SQL> select e.empno, e.ename, e.sal, e.hiredate,
(select d.dname from dept d where d.deptno = e.deptno) dname
from emp e
where sal >= 2000;
EMPNO ENAME SAL HIREDATE DNAME
7 rows selected.
SQL> select /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where d.deptno(+) = e.deptno
and e.sal >= 2000;
EMPNO ENAME SAL HIREDATE DNAME
7 rows selected.
SQL> select /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where d.deptno = e.deptno
and e.sal >= 2000;
EMPNO ENAME SAL HIREDATE DNAME
6 rows selected.
delete from emp where empno = 8500;
commit;
– ALTER TABLE SCOTT.DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);
ALTER TABLE SCOTT.DEPT drop CONSTRAINT PK_DEPT;
insert into dept values ( null,'SM','SEOUL');
commit;
SQL> select * from dept;
DEPTNO DNAME LOC
5 rows selected.
SQL> select e.empno, e.ename, e.sal, e.hiredate,
(select d.dname from dept d where d.deptno = e.deptno) dname
from emp e
where sal >= 2000;
EMPNO ENAME SAL HIREDATE DNAME
6 rows selected.
delete from dept where DNAME = 'SM'
commit;
ALTER TABLE SCOTT.DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);
ALTER TABLE SCOTT.EMP ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES SCOTT.DEPT (DEPTNO);
SQL> select /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where d.deptno = e.deptno
and e.sal >= 2000;
EMPNO ENAME SAL HIREDATE DNAME
6 rows selected.
SQL> select /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where d.deptno(+) = e.deptno
and e.sal >= 2000;
EMPNO ENAME SAL HIREDATE DNAME
6 rows selected.
아우터 조인과 100% 같은결과 이다.
즉 DEPT와 조인을 실패한 EMP레코드가 있다면 dname을 NULL로 출력할것이다.
select e.empno, e.ename, e.sal, e.hiredate,
(select d.dname from scott.dept d where d.deptno = e.deptno) dname
from scott.emp e
where sal >= 2000;
*************************[Explain Plan Time: 2013/09/02 17:48:27]*************************
Execution Plan
Predicate information (identified by operation id):
힌트(조인방법과 조인순서를 결정함으로)로 결과 뿐 아니라 조인을 수행하는 처리 경로도 동일해 진다.
select /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname
from scott.emp e, scott.dept d
where d.deptno(+) = e.deptno
and e.sal >= 2000;
*************************[Explain Plan Time: 2013/09/02 17:18:59]*************************
Execution Plan
Predicate information (identified by operation id):
이것에 대한 활용 방안.
아우터 조인은 조인순서와 방법등을 자유자재로 제어할수 있는 장점.
스칼라 서브 쿼리는 내부적인 캐싱기법을 활용할수 있는 장점.
============================== 함수 예 ================================
CREATE FUNCTION F_부서이름(V_부서번호 IN NUMBER)
RETURN NUMBER
IS V_부서이름 = VARCHAR2(10);
BEGIN
SELECT 부서이름 INTO V_부서이름
FROM 부서
WHERE 부서번호 = V_부서번호;
RETURN(V_부서이름);
END;
-- 책의 문장이 틀림.(수정된 문장을 알고 싶은 분은 아래쪽 테스트 참고)
SELECT 사원번호,사원명, F_부서이름(부서번호) 부서이름
FROM 사원
WHERE 성별 = '남자';
========================== 스칼라 서브쿼리 예 =============================
SELECT 사원번호, 사원명,
(SELECT 부서이름
FROM 부서 A
WHERE A.부서번호=B.부서번호) 부서이름
FROM 사원 B
WHERE 성별= '남자';
재사용 여부란? : 그때마다 쿼리를 다시 짜야 한다.
관리 어려움이란? : 마이그레이션시 관련 함수도 같이 마이그레이션 해야한다.
(2) 스칼라서브쿼리 캐싱효과
select e.empno, e.ename, e.sal, e.hiredate,
(
select d.dname ==> 출력값 : d.dname
from scott.dept d
where d.deptno = e.deptno ==> 입력값 : e.empno
) dname
from scott.emp e
where sal >= 2000;
오라클은 스칼라 서브 쿼리 수행횟수를 최소화 하기 위해 입력값과 출력값을 내부캐시(Query execution Cache)에 저장해 둔다.
입력값을 캐쉬에서 찾음.
조나단 루이스의 주장 참고.
맹점1. 해싱알고리즘 사용 하나 해시 충돌시 기존엔트리를 밀어내고 새 입출력 값을 저장하지는 않는다.
맹점2. 해시 충돌 발생한 입력값이 반복 입력 된다면 스칼라서브 쿼리도 반복 수행된다.
입력 값 종류가 다수라면 => 해시 충돌 가능성이 높다. 또한 캐시 확인 비용 때문에 성능 저하된다.
스칼라서브쿼리는 NL조인에서 인너쪽 인덱스와 테이블에 나타나는 피닝효과도 사라진다.
##############################################
런타임시 스칼라 서브 쿼리와 필터 서브쿼리를 최적화 하기위한 해시테이블의 크기는
8i , 9i에서는 256개 엔트리로 고정.
10g에서는 해시테이블이 고정된 크기의 메모리에 할당.
--> 이는 엔트리 갯수가 가변적임을 의미 한다.
################################################
이 기능을 함수 호출 횟수를 줄이는 데 사용할 수 있는데，
함수를 Dual 테이블을 이용해 스칼라 서브쿼리로 한번 감싸는 것이다.
함수 입력 값의 종류가 적을 때 이 기법을 활용하면 함수 호출횟수를 획기적으로 줄일 수 있다.
시장코드와 증권그룹코드로 만들어질 수 있는 값의 조합이 20개이므로,
SF_상품분류 함수에 대한 입력 값 종류도 20개다.
20 개에 대한 입력 값과 출력 값을 캐싱한다면 함수 호출횟수를 20번으로 줄일 수 있다.
-- 테이블 생성
CREATE TABLE 체결(체결일자, 체결번호, 시장코드, 증권그룹코드, 체결수량, 체결금액)
NOLOGGING
AS
SELECT '20090315'
, ROWNUM
, DECODE(SIGN(ROWNUM-100000), 1, 'ST', 'KQ') -- 유가증권, 코스닥
, DECODE(MOD(ROWNUM, 8), 0, 'SS', 1, 'EF', 2, 'EW' -- 주식, ETF, ELW
, 3, 'DR', 4, 'SW', 5, 'RT' -- DR, 신주인수권, 리츠
, 6, 'BC', 7, 'MF') -- 수익증권, 투자회사
, ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)
, ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2)
FROM DUAL
CONNECT BY LEVEL <= 500000
UNION ALL
SELECT '20090315'
, ROWNUM + 300000
,(CASE WHEN MOD(ROWNUM, 4) < 2 THEN 'SD' ELSE 'GD' END)
,(CASE WHEN MOD(ROWNUM, 4) IN (0, 2) THEN 'FU' ELSE 'OP' END)
, ROUND(DBMS_RANDOM.VALUE(10, 1000), -1)
, ROUND(DBMS_RANDOM.VALUE(10000, 1000000), -2)
FROM DUAL
CONNECT BY LEVEL <= 500000
;
CREATE TABLE 분류순서(분류명, 순서)
AS
SELECT '주식 현물', 1 FROM DUAL
UNION ALL
SELECT '주식외 현물', 2 FROM DUAL
UNION ALL
SELECT '파생', 3 FROM DUAL ;
-- 펑션 생성
CREATE OR REPLACE FUNCTION SF_상품분류(시장코드 IN VARCHAR2, 증권그룹코드 IN VARCHAR2)
RETURN VARCHAR2
IS
L_분류 VARCHAR2(14);
BEGIN
IF 시장코드 IN ('ST', 'KQ') THEN
IF 증권그룹코드 = 'SS' THEN
L_분류 := '주식 현물';
ELSIF 증권그룹코드 IN ('EF', 'EW') THEN
L_분류 := '파생';
ELSE
L_분류 := '주식외 현물';
END IF;
ELSE
L_분류 := '파생';
END IF;
SELECT 순서 || '. ' || L_분류 INTO L_분류
FROM 분류순서
WHERE 분류명 = L_분류;
RETURN L_분류;
END;
/
SELECT SF_상품분류('ST','SS') FROM DUAL
UNION ALL
SELECT SF_상품분류('ST','SW') FROM DUAL
UNION ALL
SELECT SF_상품분류('ST','EF') FROM DUAL ;
-- 트레이스후 노말 수행
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT SF_상품분류(시장코드, 증권그룹코드) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
AND ROWNUM > 0
) ;
-- 트레이스후 dual로 감싼 함수 수행
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT /*+ NO_MERGE */
(SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
-- 사이즈 재지정후 트레이스 dual로 감싼 함수 수행
ALTER SESSION SET "_query_execution_cache_max_size" = 2097152;
SELECT SUM(DECODE(상품분류, '1. 주식 현물' , 체결수량)) "주식현물_체결수량"
, SUM(DECODE(상품분류, '2. 주식외 현물', 체결수량)) "주식외현물_체결수량"
, SUM(DECODE(상품분류, '3. 파생' , 체결수량)) "파생_체결수량"
FROM (
SELECT /*+ NO_MERGE */
(SELECT SF_상품분류(시장코드, 증권그룹코드) FROM DUAL) 상품분류
, 체결수량
FROM 체결
WHERE 체결일자 = '20090315'
) ;
다시 교재로 돌아와서........................
1. 최대 결과 건수만큼 반복적으로 수행된다.
예> 추출건수가 100건. 스칼라 서브쿼리의 입력값이 모두 동일
-> 1회수행. 99개 멀티버퍼저장값리턴
추출건수가 100건. 스칼라 서브쿼리의 입력값이 유니크 함
-> 100번을 반복 수행.
< SCALAR_T1>
■ 생성요건
■ 테이블 생성
CREATE TABLE SCALAR_T1
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, LEVEL+99999 AS C3
FROM DUAL
CONNECT BY LEVEL <= 500000 ;
■ 각 칼럼에 인덱스 생성 및 통계정보 수집
CREATE INDEX SCALAR_T1_IDX_01 ON SCALAR_T1 ( C1 ) ;
EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'SCALAR_T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
< SCALAR_T2>
■ 생성요건
■ 테이블 생성
CREATE TABLE SCALAR_T2
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, DECODE(MOD(LEVEL,10),0,NULL,MOD(LEVEL,10)) AS C3
FROM DUAL
CONNECT BY LEVEL <= 500000 ;
■ 각 칼럼에 인덱스 생성 및 통계정보 수집
CREATE INDEX SCALAR_T2_IDX_01 ON SCALAR_T2 ( C1 ) ;
EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'SCALAR_T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
< SCALAR_T3>
■ 생성요건
■ 테이블 생성
CREATE TABLE SCALAR_T3
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, DECODE(MOD(LEVEL,100),0,NULL,MOD(LEVEL,100)) AS C3
FROM DUAL
CONNECT BY LEVEL <= 500000 ;
■ 각 칼럼에 인덱스 생성 및 통계정보 수집
CREATE INDEX SCALAR_T3_IDX_01 ON SCALAR_T3 ( C1 ) ;
EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'SCALAR_T3',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
2. 추출되는 데이터는 항상 1건만 유효하다.
Error: # 1427, ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.
-- 스칼라 서브쿼리의 T2.C2 는 Unique 값이 아니므로 T1.C2 와 조인을 수행할 경우 추출 데이터가 2 건 이상이다.
-- 이런 경우 ORA-01427 에러가 발생한다.
SQL> SELECT count(*)
FROM SCALAR_T2 T2
WHERE T2.C2 = 'A';
COUNT(*)
1 rows selected.
--에러가 발생하지 않도록 하기 위해서는 스칼라 서브쿼리의 추출 데이터가 1 건을 초과하지 않아야 한다.
--그러므로 스칼라 서브쿼리의 T2. C2 에서 추출되는 데이터가 1 건을 초과한다면 "ROWNUM <= 1" 조건을 추가하면 된다.
SQL> SELECT C1, C2, C3,
(SELECT T2.C1
FROM SCALAR_T2 T2
WHERE T2.C2 = T1.C2
AND ROWNUM <= 1) AS T2_C1
FROM SCALAR_T1 T1
WHERE C2 = 'A'
AND ROWNUM <= 1;
C1 C2 C3 T2_C1
1 rows selected.
SQL> SELECT c1, c2, c3,
NVL((SELECT NULL
FROM SCALAR_T2 T2
WHERE t2.c2 = t1.c2 -
AND ROWNUM <= 1), 'ISNULL') AS t2_c1
FROM SCALAR_T1 T1
WHERE c2 = 'A'
AND ROWNUM <= 1;
C1 C2 C3 T2_C1
1 rows selected.
3. 데이터가 추출되지 않아도 된다.
-- 스칼라 서브쿼리는 NULL 데이터를 주줄해도 SQL의 수행에 영향을 미치지 않는다.
-- 만약n스칼라 서브쿼리에서 NULL 데이터를 임의의 다른 값으로 치환하고 싶은 경우에는 NVL 함수를사용
C1 C2 C3 T2_C1
1 rows selected.
" 항상 1 건 이하의 데이터 추출"과 "NULL 데이터 추출 가능"
이란 두 가지 특성은 스칼라 서브쿼리가 SQL의 추줄 건수에 영향을 미치지 않는다는 것을 의미한다.