[SQL 21] 서브쿼리를 사용한 DML 문장과 다이렉트 로드

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
서브 쿼리를 사용한 INSERT 문 & 다이렉트 로드(Direct load)

기본 구조

INSERT INTO [/*+APPEND */] 테이블 [NOLOGGING] [(컬럼, 컬럼, …]

SELECT 문장

• 한 번에 여러 행을 입력할 수 있다.
• 서브 쿼리에 검색된 행을 입력 값으로 사용한다.
• 컬럼 목록과 SELECT 문의 컬럼이 1:1 대응돼야 한다.
• /*+APPEND */ : 다이렉트 로드, 대량의 입력 작업을 더 빠르게 수행한다.
• [NOLOGGING] : 로그 정보를 남기지 않음으로 입력 작업이 빨라진다. (장애 발생시 복구 불가능)

◈ 예제

desc departments	desc copy_departments

insert into copy_departments(department_id, department_name, manager_id)

select department_id, department_name, manager_id

from departments

where department_name = 'IT Helpdesk';

1 행 이(가) 삽입되었습니다.

INSERT 문장을 실행할 때는 항상 테이블의 구조를 파악하고 작업해야 한다.

위의 경우는 같은 구조의 테이블을 만들었기 때문에 제약조건을 제외하고는 컬럼의 이름, 데이터 타입이 같지만 컬럼의 이름은 달라도 상관없다. 그러나 반드시 데이터 타입은 같아야 한다. 데이터 타입은 같아야 하지만 데이터의 크기는 달라도 상관없다. 다만 주의할 것은 입력되는 데이터 크기보다 입력될 컬럼의 크기는 같거나 더 커야한다.

▶ 다른 테이블의 전체 데이터 삽입

insert into copy_departments

select *

from departments;

29개 행 이(가) 삽입되었습니다.

서브 쿼리에서 WHERE 절을 생략하면 그 테이블의 모든 데이터를 삽입한다.

▣ 다이렉트 로드(Direct load)

DML 작업은 모두 메모리에서 이루어진다. 오라클의 경우 SGA라는 메모리 영역 내에 데이터베이스 버퍼 캐시(Database Buffer Cache)라는 영역을 작업 공간으로 이용하여 이를 통해 디스크 접근 횟수와 량을 줄여 성능을 향상시킨다. 쉽게말해 DML 작업은 메모리에서 먼저 이러우진 후 트랜잭션이 종료된 이후에 실제 디스크에 저장시킨다.(메모리의 속도가 더 빠르기 때문에 성능 향상을 위해) 그러나 대량의 DML 작업을 할 경우 오히려 이 방식이 성능을 저하시킬 때가 있다.

예를 들어 메모리의 용량보다 더 큰 DML 작업을 할 경우 메모리의 용량만큼 작업이 이루어지고 이를 디스크에 반영한 후 다시 메모리를 정리하고 작업을 하는 비효율적인 작업이 이루어진다. 이런 문제를 피하기 위해 메모리를 통하지 않고 디스크에 직접 작업을 수행하는 것을 다이렉트 로드라 한다. DML작업을 다이렉트 로드하기 위해서는 '/*+ APPEND */'를 문장에 삽입한다. 그리고 디스크에 직업 저장시킨다고해서 트랜잭션이 자동으로 종료되는 것이 아니기 때문에 INSERT 작업이 끝나면 반드시 트랜잭션을 종료(COMMIT) 하자.

다이렉트 로드로 INSERT 작업을 할 경우에는 좋은 점만 있는 것은 아니다. 다음과 같은 단점도 존재한다. 다이렉트 로드를 할 때 테이블에 할당되었지만 현재는 빈영역이 있더라도, 테이블에 새롭게 영역을 할당해 INSERT한다. 이로 인해, 통상의 INSERT문보다 많은 디스크 영역을 사용하는 일이 있다. 그리고 ISNERT 작업이 종료할 때까지 다른 트랜잭션에서 INSERT/UPDATE/DELETE 작업을 동시에 할 수 없다.

▣ NOLOGGING 옵션

오라클은 안전하게 시스템을 운영을 하기 위해 장애 발생 시 복구할 수 있도록 데이터베이스에 변경을 가하는 모든 작업의 내역을 Redo log 영역에 로그 정보로 보관한다. 예를 들면 INSERT 작업을 하면 데이터를 삽입하는 작업도 하지만 동시에 Redo log 영역에 INSERT 작업이 실행되었다는 정보를 저장하는 작업도 한다. 그러나 다이렉트 로드처럼 대량의 DML 작업의 효율을 위해 이를 수행하지 않을 수 있다. 작업 성능을 위해 로그 정보를 남기지 않겠다고 'NOLOGGING' 옵션을 이용하면 된다. 그러나 장애가 발생하여 작업한 데이터들이 손상된 경우에 Redo log 영역의 로그 정보를 이용하여 복구를 하는데 로그 정보를 남기지 않았다면 복구가 불가능하다. 작업 성능은 빨라지지만 작업의 안전도는 떨어진다고 할 수 있다. 그러므로 복구가 불가능하도록 작업한 이후에는 장애에 대비한 백업을 잊지 않아야 한다.

truncate table copy_departments;

Table COPY_DEPARTMENTS이(가) 잘렸습니다.

insert /*+ APPEND */ into copy_departments nologging (department_id, location_id)

select department_id, location_id

from departments;

29개 행 이(가) 삽입되었습니다.

commit;

커밋 완료.

No. 02
서브 쿼리를 사용한 UPDATE 문

기본 구조

UPDATE 테이블

SET 컬럼명 = (SELECT 문장),

 컬럼명 = (SELECT 문장), …

[WHERE 조건];

UPDATE 테이블

SET (컬럼, 컬럼, …) = (SELECT 문장)

[WHERE 조건];

• 서브 쿼리의 결과 행은 반드시 하나여야 한다.
• 서브 쿼리에서 검색되는 데이터는 반드시 수정되는 컬럼과 1:1 대응돼야 한다.

◈ 예제

▶ 사원번호 115사원의 직업과 급여를 205 사원의 직업, 124 사원의 급여와 동일하게 수정한다.

update employees

set job_id = (select job_id

      from employees

      where employee_id = 205),

     salary = (select salary

            from employees

            where employee_id = 124)

where employee_id = 115;

1 행 이(가) 업데이트되었습니다.

select employee_id, first_name, job_id, salary

from employees

where employee_id in (115, 124, 205);

rollback;

롤백 완료.

▶ 사원번호 115사원의 직업과 급여를 205 사원의 직업, 급여와 동일하게 수정한다.

[방법1]

update employees

set job_id = (select job_id

      from employees

      where employee_id = 205),

      salary = (select salary

      from employees

      where employee_id = 205)

where employee_id = 115;

[방법2]

update employees

set (job_id, salary) = (select job_id, salary

      from employees

      where employee_id = 205)

where employee_id = 115;

1 행 이(가) 업데이트되었습니다.

No. 03
서브 쿼리를 사용한 DELETE 문

기본 구조

DELETE FROM 테이블

WHERE 컬럼명 = (SELECT 문장);

• 서브 쿼리의 결과 행은 반드시 하나여야 한다.

◈ 예제

delete from copy_departments

where department_id = (select department_id

  from employees

  where first_name = 'Bruce');

1 행 이(가) 삭제되었습니다.

Bruce가 속한 부서를 copy_departments 테이블에서 삭제하는 작업이다.

DML 문장은 서브 쿼리를 사용하여 다른 테이블의 데이터들도 이용할 수 있다.

HR 스키마에 원래 존재하던 테이블에 대해서 DML 작업을 하는 것은 많은 제약 조건들 때문에 에러를 발생시키는 경우가 많다. DML 작업을 할 때는 어떤 제약 조건이 걸려있는지 등을 판단하여 수행해야지만 순조롭게 작업을 진행할 수 있을 것이다. 제약조건과 이 제약 조건들의 관리에 대해서는 곧 배워보도록 하겠다.