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;

실습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) 성능적인 관점

• 데이터 양이 증가할수록 일반 구현은 기하급수적으로 성능이 악화된다.

• (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가 버전을 관리하기 위하여 사용자 데이터에 부가한 추가적인 구조를 감추는 기능을 한다.