이펙티브 오라클 (2009년)
블랙박스 증후군을 피하라 0 0 44,793

by 구루비스터디 CONNECT BY [2018.05.26]


3.1 데이터베이스 독립 대 데이터베이스 종속

  • '데이터베이스 종속은 목표이어야지 회피의 대상은 아니다'
  • 기성품 소프트웨어처럼 여러 개의 서로 다른 데이터베이스에 실제로 적재될 제품을 만드는 게 아니라면 데이터베이스 독립이 추구해야 할 목표는 아니다.(PeopleSoft, SAP, ETL 툴)
  • 다수의 데이터베이스 사이에 어느 정도의 애플리케이션 이식성을 달성하기 위해서는 애플리케이션의 데이터베이스 구성 요소를 모두 저장 프로시저에 작성하는 것이 가장 바람직하다?


3.2 블랙박스 증후군의 위험

  • 실행할 수 없음
  • 올바른 해답을 얻을 수 없음
  • 소프트웨어를 신속하게 인도할 수 없음
  • 투자 효과를 극대화할 수 없음


3.2.1 실행할 수 없음


실습1
  • CONNECT BY 문을 사용하여 사원 계층을 보여주는 애플리케이션

 select rpad('*',2*level,'*') || ename ename
   from scott.emp
  start with mgr is null
  connect by prior empno = mgr;


실습2_1
  • 분석 함수를 사용하여 사원의 부서 번호, 이름, 월급을 포함하는 종업원 정보,
  • 부서별 월급 합계, 종업원의 월급이 부서와 전체에서 차지하는 비율 구하기



  column pct_dept format 99.9;
  column pct_oveall format 99.9;
  break on deptno skip 1;

  select deptno  -- 부서 번호
         ,ename   -- 사원 이름
         ,sal     -- 월급
         ,sum(sal) over (partition by deptno order by sal, ename) cum_sal
         -- 부서별 급여
         ,round(100*ratio_to_report(sal) over(partition by deptno),1) pct_dept
         -- 종업원의 월급이 부서에서 차지하는 비율
         ,round(100*ratio_to_report(sal) over(), 1) pct_overall
         -- 종업원의 월급이 회사 전체에서 차지하는 비율
    from scott.emp
   order by 1,3;


  • break on 구문 : 한 컬럼에 중복된 데이터가 있다면 하나만 출력되게 해주는 sqlplus의 기능
  • over( partition by expr ) : expr에 따라 그룹별로 단일 결과 셋으로 분리하는 역할 분석 함수의 계산대상 그룹을 지정
  • ratio_to_report 함수 : 계산 대상 값 전체에 대한 현재 로우의 상대적인 비율 값을 반환하는 함수
  • 참고 서적 : 뇌를 자극하는 오라클 프로그래밍 SQL&PL/SQL(한빛미디어)



실습2_2
  • 자체 조인(Self Join)/인라인 뷰(NL View)를 사용하여 사원의 부서 번호, 이름, 월급을 포함하는 종업원 정보, 부서별 월급 합계, 종업원의 월급이 부서와 전체에서 차지하는 비율 구하기

  select emp1.deptno  -- 부서 번호
        ,emp1.ename   -- 사원 이름
        ,emp1.sal     -- 월급
        ,sum(emp4.sal) cum_sal
         -- 부서별 급여
        ,round(100*emp1.sal/emp2.sal_by_dept,1) pct_dept
         -- 종업원의 월급이 부서에서 차지하는 비율
        ,round(100*emp1.sal/emp3.sal_overall,1) pct_overall
         -- 종업원의 월급이 회사 전체에서 차지하는 비율
    from scott.emp emp1
        ,( select deptno, sum(sal) sal_by_dept
           from scott.emp
          group by deptno ) emp2
        ,( select sum(sal) sal_overall
             from scott.emp ) emp3
        , scott.emp emp4
   where emp1.deptno = emp2.deptno
     and emp1.deptno = emp4.deptno
     and ( emp1.sal > emp4.sal or
          ( emp1.sal = emp4.sal and emp1.ename >= emp4.ename ) )
    group by emp1.deptno
            ,emp1.ename
            ,emp1.sal
            ,round(100*emp1.sal/emp2.sal_by_dept,1)
            ,round(100*emp1.sal/emp3.sal_overall,1)
   order by 1,3;



실습2_1/실습2_2 결과 분석
  • (1) 자체 조인(Self Join)/인라인 뷰(NL View)를 사용한 실습2_2가 제대로 동작하면서도 데이터베이스에 보다 독립적이다
  • (2) 성능적인 관점


테이블의 행 수CPU/분석CPU/일반차이
20000.052.1342배
40000.098.5795배
80000.1935.88188배


  • 데이터 양이 증가할수록 일반 구현은 기하급수적으로 성능이 악화된다.
  • (3)15개의 데이터베이스에서 모두 실행될 수 있다고 선전되는 것보다는 포장에 "고객의 데이터베이스를 제대로 활용한다!"는 문구가 있는 해석 툴이 왜 훨씬 바람직한지를 보여 주는 예시
  • (4)분석함수 사용시 유의사항
  • 참고 서적 : 뇌를 자극하는 오라클 프로그래밍 SQL&PL/SQL(한빛미디어)


1) 실습2_3
  • 자체 조인(Self Join)/인라인 뷰(NL View)를 사용하여 부서별 최대급여와 최소급여를 받는 사원 명단을 동시에 추출

  select emp1.department_id
        ,emp1.employee_id || ' ' || emp1.last_name max_sawon
        ,emp1.salary
        ,emp2.employee_id || ' ' || emp2.last_name min_sawon
        ,emp2.salary
    from hr.employees emp1
        ,hr.employees emp2
        ,( select department_id
                 ,max(salary) max_sal
             from hr.employees
            group by department_id ) max_dep -- 최대급여를 받는 사원명단
        ,( select department_id
                 ,min(salary) min_sal
             from hr.employees
            group by department_id ) min_dep -- 최소급여를 받는 사원명단
   where emp1.department_id = max_dep.department_id
     and emp1.salary        = max_dep.max_sal
     and emp2.department_id = min_dep.department_id
     and emp2.salary        = min_dep.min_sal
     and emp1.department_id = emp2.department_id
   order by emp1.department_id;



2) 실습2_4
  • 분석함수를 사용하여 부서별 최대급여와 최소급여를 받는 사원 명단을 동시에 추출



  select department_id
        ,max(employee_id || ' ' || last_name )
          keep ( dense_rank first order by salary desc ) 최대급여
         ,max(salary) 최대값
         ,min(employee_id || ' ' || last_name )
          keep ( dense_rank last order by salary desc ) 최소급여
         ,min(salary) 최소값
    from hr.employees
   group by department_id;



3)실습2_3/실습2_4 결과 분석
  • 분석함수를 사용한 결과(실습2_3)는 자체 조인(Self Join)/인라인 뷰(NL View)(실습2_4)를 사용한 쿼리와 그 결과 로우수가 다르다.
  • 즉 부서번호가 90인 건은 최소급여를 받는 사원이 두명이었는데 이 쿼리에서는 사번이 102번인 한 사람만 추출된 것이다.



  select department_id
        ,max(employee_id || ' ' || last_name )
          keep ( dense_rank last order by salary desc ) max_sawon
        ,min(employee_id || ' ' || last_name )
          keep ( dense_rank last order by salary desc ) min_sawon
        ,min(salary)
    from hr.employees
   where department_id = 90
   group by department_id;


  • FIRST와 LAST가 같이 사용된 집계함수의 결과는 대상 데이터에 따라 이와 같이 다른 값을 반환하게 된다.


3.2.2 올바른 해답을 얻을 수 없음

  • 일관성 및 동시성 제어(다중 버전, 읽기 일관성, 잠금 등)
  • 마스터/상세 테이블을 대상으로 수행되는 트랜잭션


(1)실습 준비

create  table ora9.s_emp  (
  empno                               varchar2(4)      not null
, dept_no                             varchar2(2)      not null
, salary                              number(10)       null
)
tablespace ora9;
create  table ora9.s_dept  (
  dept_no                             varchar2(2)      not null
, sum_of_salary                       number(10)       null
)
tablespace ora9;

alter table  ora9.s_emp add (
  constraint s_emp_pk primary key ( empno )
     using index tablespace ora9 );

alter table  ora9.s_dept add (
  constraint s_dept_pk primary key ( dept_no )
     using index tablespace ora9 );

alter table ora9.s_emp
  add constraint s_dept_fk1 foreign key ( dept_no )
    references ora9.s_dept ( dept_no ) ;

create index idx1
  on ora9.s_emp(dept_no)
    tablespace ora9;

insert into s_dept values ( '1', null );
insert into s_dept values ( '2', null );
insert into s_dept values ( '3', null );
commit;

insert into s_emp values ( '100', '1', 600 );
insert into s_emp values ( '101', '1', 800 );
insert into s_emp values ( '102', '2', 400 );
insert into s_emp values ( '103', '2', 1000 );
insert into s_emp values ( '104', '3', 1200 );
insert into s_emp values ( '105', '3', 300 );
commit;

update ora9.s_dept
   set sum_of_salary = ( select sum(salary)
                           from ora9.s_emp
                          where s_emp.dept_no = s_dept.dept_no );

select *
  from ora9.s_emp;

select *
  from ora9.s_dept;


(2)실습3

--<Session 1> : 첫번째 쿼리
insert into ora9.s_emp ( empno, dept_no, salary )
 values ( '106', '2', 700 );

--<Session 2> : 두번째 쿼리
update ora9.s_emp
   set dept_no = '2'
 where empno = '100';

--<Session 2> : 세번째 쿼리(LOCK 발생)
update ora9.s_dept
   set sum_of_salary = ( select sum(salary)
                           from ora9.s_emp
                          where ora9.s_emp.dept_no = ora9.s_dept.dept_no )
 where ora9.s_dept.dept_no in ( '1', '2');

--<Session 1> : 네번째 쿼리
update ora9.s_dept
   set sum_of_salary = ( select sum(salary)
                           from ora9.s_emp
                          where ora9.s_emp.dept_no = ora9.s_dept.dept_no)
 where ora9.s_dept.dept_no = '2';

--<Session 1> : 다섯번째 쿼리(세번째 쿼리 LOCK 해제)
commit;

--<Session 2> : 여섯번째 쿼리
commit;

--<Session 1> : 일곱번째 쿼리
select * from ora9.s_dept;

--<Session 1> : 여덟번째 쿼리
select dept_no
      ,sum(salary)
  from ora9.s_emp
group by dept_no;



  • 이 결과는 확실히 잘못되었다.
  • 원인은 서버가 Session1과 2 사이에 교착상태 조건(deadlock condition)을 감지하여, 하나를 교착상태의 희생자로 선택(예를 들면, 세션1)하여 강제로 롤백(rollback)시킨다.
  • 이 사례를 통해 배울 점은 관계형 데이터베이스마다 서로 다른 동시성 및 일관성 모델을 가지고 있다는 것이다.
  • 만약 오라클 개발팀 구성원이 오라클의 메커니즘이 SQL Server 혹은 DB2의 메커니즘과 동일하게 동작하고 있다고 생각하면 결과는 손상된 데이터, 부정확한 해석, 부정확한 해답 등으로 나타날 것이다.


3.2.3 소프트웨어를 신속하게 인도할 수 없음

  • 데이터베이스와 데이터베이스 기능들을 최대한 활용할 경우 짧은 시간 내에 데이터베이스 애플리케이션을 작성할 수 있다.
  • 분석함수를 사용함으로써 개발시간을 단축할 수 있다.
  • 모든 변경 사항을 감사하는 기능을 애플리케이션에 부여할 경우(시작부터 종료 시점까지 행의 변경이력)
    • (1)독자적인 구현을 디자인하고 작성한 다음 결함을 수정하여 유지하는 방법
    • (2)단일 데이터베이스 명령을 사용하여 동일한 기능을 구현하는 방법


1) 실습 준비

create table ora9.emp
as
select empno
      ,ename
      ,sal
      ,comm
  from scott.emp;

alter table ora9.emp
 add constraint emp_pk
  primary key(empno);

begin
 dbms_wm.EnableVersioning
 ( 'EMP', 'VIEW_WO_OVERWRITE' );
end;
/



2)변경 사항 발생

update ora9.emp
   set sal = 5000
 where ename = 'KING';

commit;

update ora9.emp
   set sal = 4000
 where ename = 'KING';

commit;

delete from ora9.emp
 where ename = 'KING';

commit;



2)변경 사항 확인

select ename
      ,sal
      ,comm
      ,user_name
      ,type_of_change
      ,createtime
      ,retiretime
  from ora9.emp_hist
 where ename = 'KING'
order by createtime;



  • Workspace Manager가 EMP 테이블의 이름을 EMP_LT로 변경하고 사용자를 위해 EMP 뷰를 생성하였음.
  • 이 뷰는 Workspace Manager가 버전을 관리하기 위하여 사용자 데이터에 부가한 추가적인 구조를 감추는 기능을 한다.
"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3448

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

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