교재의 특성 세가지.
1. 최대 결과 건수만큼 반복적으로 수행된다.
2. 추출되는 데이터는 항상 1건만 유효하다.
3. 데이터가 추출되지 않아도 된다.
이에 앞서 스칼라서브 쿼리의 사전지식(?)을 다져 보자.
참고1. 사전지식 : 실핼계획으로 고성능 데이터베이스 튜닝 (P306~P311)
SQL > SELECT 사원번호,사원명,
(SELECT 부서명
FROM 부서 B
WHERE A.부서번호 = B.부서번호) 부서명,
연차일수
FROM 사원 A
WHERE 입사일 > '20040101'
AND 직급 = '대리';
실행계획
SELECT STATEMENT
TABLE ACCESS (BY INDEX ROWID) OF '부서'
INDEX (RANGE SCAN) OF '부서_IDX'
TABLE ACCESS (BY INDEX ROWID) OF '사원'
INDEX (RANGE SCAN) OF '사원_IDX'
인덱스
부서_IDX : 부서번호
사원_IDX : 직급 + 입사일
조인순서
조인방법
실습1. 직업(JOB)이 SALESMAN이고 입사일(HIREDATE)이 1981년 5월 이후인 직원들의 사번(empno)과 성명(ename),
부서명(dname) 급여(SAL)를 추출하시오.(SCOTT)
실습2. 폰번호(PHONE_NUMBER)가 011로 시작되며, 입사일(HIRE_DATE)이 1981년 5월 이후인 직원들의
고용번호(EMPLOYEE_ID)와 성(LAST_NAME), 직업(DEPARTMENT_NAME), 폰번호(PHONE_NUMBER)를 추출하시오.(HR)
참고2. 사전지식 : 오라클 성능 고도화 원리와 해법2.(P284~P285)
실습.
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):
아우터 조인과 100% 같은결과 이다.
즉 DEPT와 조인을 실패한 EMP레코드가 있다면 dname을 NULL로 출력할것이다.
위의 힌트(조인방법과 조인순서를 결정함으로)로 결과 뿐 아니라 조인을 수행하는 처리 경로도 동일해 진다.
아우터 조인은 조인순서와 방법등을 자유자재로 제어할수 있는 장점.
스칼라 서브 쿼리는 내부적인 캐싱기법을 활용할수 있는 장점.
참고3. 사전지식 : 실핼계획으로 고성능 데이터베이스 튜닝 (P312 ~ P315)
함수 예 >
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 성별= '남자';
함수와 스칼라서브쿼리의 차이
함수 : 재사용이 가능 , 관리가 불리
스칼라서브쿼리 : 재사용이 불가능 , 관리가 용이
재사용 여부? : 그때마다 쿼리를 다시 짜야 한다.
관리 어려움? : 마이그레이션시 관련 함수도 같이 마이그레이션 해야한다.
스칼라서브쿼리 의 고려사항 (함수도 마찬가지임)
1. 한건의 데이터도 추출되지 않을경우
2. 두건 이상의 데이터가 추출되는 경우
==>위의 예제는 SQL튜닝의 시작에 테스트 참고
참고4. 사전지식 : 오라클 성능 고도화의 원리2 (P285 ~ P286)
(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조인에서 인너쪽 인덱스와 테이블에 나타나는 피닝효과도 사라진다.
참고5. 사전지식 : 비용기반 오라클 (P566)
런타임시 스칼라 서브 쿼리와 필터 서브쿼리를 최적화 하기위한 해시테이블의 크기는
8i , 9i에서는 256개 엔트리로 고정.
10g에서는 해시테이블이 고정된 크기의 메모리에 할당.
--> 이는 엔트리 갯수가 가변적임을 의미 한다.
참고6. 사전지식 : 고도화 1 - (P417 ~ P420)
*스칼라 서브쿼리의 캐싱효과를 이용한 함수 호출 최소화
서브쿼리가 수행될 때마다 입력 값을 캐시에서 찾아보고
거기 있으면 저장된 출력 값을 리턴하고，
없으면 쿼리를 수행한 후 입력 값과 출력 값을 캐시에 저장해 두는 원리
이 기능을 함수 호출 횟수를 줄이는 데 사용할 수 있는데，
함수를 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의 추줄 건수에 영향을 미치지 않는다는 것을 의미한다.