교재의 특성 세가지.
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)
실습3. 스칼라 서브쿼리의 특성을 테스트
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번으로 줄일 수 있다.
실습 4.스칼라 서브쿼리의 캐싱효과 테스트
다시 교재로 돌아와서........................
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의 추줄 건수에 영향을 미치지 않는다는 것을 의미한다.
튜닝전]
SELECT ROWNUM rnum, X.*
FROM (
SELECT /*+ USE_NL(T1 T2 T3) */
t1.c1, t1.c2, t1.c3,
t2.c3 AS t2_c3, t3.c3 AS t3_c3
FROM SCALAR_T1 T1, SCALAR_T2 T2, SCALAR_T3 T3
WHERE t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
ORDER BY t1.c1, t1.c2
) X
WHERE ROWNUM <= 10 ;
call count cpu elapsed disk query current rows
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
튜닝1. 해쉬변환]
SELECT ROWNUM rnum, X.*
FROM (
SELECT /*+ LEADING(T1) USE HASH(T1 T2 T3) */
t1.c1, t1.c2, t1.c3,
t2.c3 AS t2_c3, t3.c3 AS t3_c3
FROM SCALAR_T1 T1, SCALAR_T2 T2, SCALAR_T3 T3
WHERE t1.c1 = t2.c1( +)
AND t1.c1 = t3.c1(+)
ORDER BY t1.c1 , t1.c2
) X
WHERE ROWNUM <= 10
call count cpu elapsed disk query current rows
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
튜닝2. 최종]
SELECT ROWNUM rnum, X.*,
(SELECT t2.C3
FROM scalar_t2 t2
WHERE t2.c1 = x.c1) AS t2_c3 ,
(SELECT t3.C3
FROM scalar_t3 t3
WHERE t3.c1 = x.c1) AS t3_c3
FROM (SELECT t1.C1, t1.c2, t1.c3
FROM scalar_t1 t1
ORDER BY t1.c1, t1.c2 ) X
WHERE ROWNUM <= 10
call count cpu elapsed disk query current rows
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
참고 1. 튜닝 사례 : 오라클 성능 고도화 원리와 해법2 (P286 ~ 288)
(3) 두개이상의 값을 리턴하고 싶을때
select d.deptno, d.dname, avg_sal, min_sal, max_sal
from dept d
,(select deptno, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal
from emp group by deptno) e
where e.deptno(+) = d.deptno
and d.loc = 'CHICAGO';
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
30 SALES 1566.666666666666666666666666666666666667 950 2850
Execution Plan
==> 시카고 부서만 대상으로 급여 집계해야하는데 EMP 테이블을 전체 스캔 하는 비효율 존재
select d.deptno, d.dname
,(select avg(sal), min(sal), max(sal) from emp where deptno = d.deptno)
from dept d
where d.loc = 'CHICAGO';
Error: # 913, ORA-00913: 값의 수가 너무 많습니다
SQL Execution Time > 00:00:00.000
==> 문맥상 안맞는 의미 : 스칼라서브 쿼리는 한 레코드당 하나의 값만 리턴
select d.deptno, d.dname
,(select avg(sal) from emp where deptno = d.deptno) avg_sal
,(select min(sal) from emp where deptno = d.deptno) min_sal
,(select max(sal) from emp where deptno = d.deptno) max_sal
from dept d
where d.loc = 'CHICAGO';
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
Execution Plan
Predicate information (identified by operation id):
==> EMP에서 같은 범위를 반복 억세스
select deptno, dname
, to_number(substr(sal, 1, 7)) avg_sal
, to_number(substr(sal, 8, 7)) min_sal
, to_number(substr(sal, 15)) max_sal
from (
select d.deptno, d.dname
,(select lpad(avg(sal), 7) || lpad(min(sal), 7) || max(sal)
from emp where deptno = d.deptno) sal
from dept d
where d.loc = 'CHICAGO'
);
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
30 SALES 1566.66 950 2850
*************************[Explain Plan Time: 2013/09/04 15:00:10]*************************
Execution Plan
Predicate information (identified by operation id):
==> 컨캣을 활용하여 하나의 컬럼에 넣은후 substr을 이용하여 원하는 값을 잘라내어 보여줌.
SQL> create or replace type sal_type as object
( avg_sal number, min_sal number, max_sal number ) ;
/
Statement Processed.
SQL> select deptno, dname
, a.sal.avg_sal, a.sal.min_sal, a.sal.max_sal
from (
select d.deptno, d.dname
,(select sal_type( avg(sal), min(sal), max(sal) )
from emp where deptno = d.deptno) sal
from dept d
where d.loc = 'CHICAGO'
) a;
DEPTNO DNAME SAL.AVG_SAL SAL.MIN_SAL SAL.MAX_SAL
1 rows selected.
*************************[Explain Plan Time: 2013/09/04 15:51:21]*************************
Execution Plan
Predicate information (identified by operation id):
참고2. 튜닝 사례 - 실핼계획으로 고성능 데이터베이스 튜닝
참고 3. 튜닝 사례 - 로지컬 옵티마이저.
SELECT A.EMPLOYEE_ID, A.FIRST_NAME , A.LAST_NAME , A.EMAIL
FROM (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, EMAIL,
(SELECT LOCATION_ID
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) AS LOCATION_ID
FROM EMPLOYEES E
WHERE E.JOB_ID = 'IT_PROG') A
WHERE A.LOCATION_ID> 0;
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, EMAIL,
(SELECT LOCATION_ID
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) AS LOCATION_ID
FROM EMPLOYEES E
WHERE E.JOB_ID = 'IT_PROG';
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME ,EMAIL
FROM EMPLOYEES E
WHERE E.JOB_ID = 'IT_PROG'
AND (SELECT LOCATION_ID
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) > 0 ;
Predicate Information이완전히 같음을 알 수 있다.
10053 Trace를 분석해보자
검증1.
SELECT /*+ PUSH_SUBQ(@SUB) */
A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, A.EMAIL
FROM (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, EMAIL,
(SELECT /*+ QB_NAME(SUB) */ LOCATION_ID
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) AS LOCATION_ID
FROM EMPLOYEES E
WHERE E.JOB_ID = 'IT_PROG' ) A
WHERE A.LOCATION_ID > 0;
검증2.
CREATE INDEX EMP_JOB_DEPT_IX ON EMPLOYEES(JOB_ID, DEPARTMENT_ID);
SELECT /*+ PUSH_SUBQ(@SUB) */
A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, A.EMAIL
FROM (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, EMAIL,
(SELECT /*+ QB_NAME(SUB) */ LOCATION_ID
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) AS LOCATION_ID
FROM EMPLOYEES E
WHERE E.JOB_ID = 'IT_PROG' ) A
WHERE A.LOCATION_ID > 0;