by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

블랙박스 증후군을 피하라




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/일반 차이
    2000 0.05 2.13 42배
    4000 0.09 8.57 95배
    8000 0.19 35.88 188배

    데이터 양이 증가할수록 일반 구현은 기하급수적으로 성능이 악화된다.
    (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가 버전을 관리하기 위하여 사용자 데이터에 부가한 추가적인 구조를 감추는 기능을 한다.

3.2.4 투자 효과를 극대화할 수 없음

문서에 대하여

  • 최초작성자 : [박상현],[이재광]
  • 최초작성일 : 2009년 9월 16일
  • 이 문서는 오라클클럽 2기-2차 대용량데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 THOMAS KYTE 저, 박민호 역의 이펙티브오라클,뇌를 자극하는 오라클 프로그래밍 SQL&PL/SQL(한빛미디어)을 참고했습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.