포스트

DML과 트랜잭션: INSERT·UPDATE·DELETE

테이블의 행을 추가·수정·삭제하는 DML과 그것이 트랜잭션과 맺는 관계, 읽기 일관성·병행제어·데드락, INSERT·UPDATE·DELETE 문법, 그리고 부모·자식 테이블에서 키가 DML을 어떻게 제약하는지까지 정리했다.

게시 업데이트
By 이소연
42 분읽는 시간
DML과 트랜잭션: INSERT·UPDATE·DELETE

Oracle SQL/DB 학습 노트 시리즈의 일곱 번째 글이다. 데이터를 검색하는 SELECT를 지나, 이번에는 테이블의 행을 직접 바꾸는 DML(INSERT·UPDATE·DELETE)과 그것이 트랜잭션과 맺는 관계를 정리한다.

[ 목차 ]

  • DML과 트랜잭션
  • 읽기 일관성·병행제어, 그리고 데드락
  • SYSDATE 함수와 DUAL 테이블
  • INSERT — 새 행 추가
  • UPDATE와 DELETE
  • DML과 KEY — 부모·자식 테이블의 제약
  • 객체와 데이터 타입 — DDL 준비
  • 디스크와 트랜잭션 일괄처리



ㅇ DML과 트랜잭션

DML(Data Manipulation Language)은 테이블에 새 행을 추가하고, 기존 행을 수정하고, 삭제하는 데이터 조작 언어다. SELECT와 결정적으로 다른 점은 트랜잭션에 직접 영향을 준다는 것이다. SELECT는 데이터를 읽기만 하므로 트랜잭션을 만들지 않지만, DML은 다르다.

SQL 명령문이 트랜잭션을 다루는 방식은 영역마다 다르다.

  • SELECT : 트랜잭션에 영향을 주지 않는다
  • DML : 연속된 여러 문장을 묶어 하나의 트랜잭션으로 관여한다
  • DDL·DCL : 문장 하나하나가 각각 하나의 트랜잭션이다

그래서 DML·DDL·DCL은 서로 다른 트랜잭션을 생성한다. 여기서 특히 조심해야 할 것이 DDL과 DCL이다. 이들은 실행과 동시에 AUTO COMMIT이 일어나 곧바로 영구 저장된다. DROP 같은 DDL 명령을 무심코 실행했다가 테이블이 그 자리에서 사라져 회사 DB를 날리는 사고가 생기는 이유가 바로 이것이다. DDL·DCL은 항상 신중하게 실행한다.

TCL(Transaction Control Language)은 DCL의 부분집합으로, 트랜잭션을 제어하는 commit·rollback·savepoint 명령을 가리킨다.


ㅇ 트랜잭션의 종료 — 수동 종료와 자동 종료

DML이 만든 트랜잭션은 두 가지 방식으로 끝난다.

  • 수동 종료 : commit·rollback 명령문을 직접 작성해 끝내는 것
  • 자동 종료 : 새로운 트랜잭션이 시작되거나, 프로그램이 정상 종료될 때 자동으로 AUTO COMMIT이 발생하는 것

롤백을 실습 상황으로 보자. 원래 부서 테이블에는 10번부터 270번까지 부서가 있었는데, INSERT로 300번부터 340번까지 삽입했다. 이때 아직 저장된 것이 아니다. 트랜잭션이 진행 중일 뿐이다. 여기서 rollback을 수행하면 다시 270번까지만 남는다.

“새로운 트랜잭션이 시작되면 자동 종료된다”는 것은, 앞선 DML 트랜잭션이 끝나지 않은 상태에서 DDL 등 다른 트랜잭션이 시작되면 앞의 것이 자동으로 커밋된다는 뜻이다.

정상 종료는 SQL 프로그램을 정상적으로 빠져나갈 때다. 창의 X 버튼을 누르거나 exit 명령으로 나가는 경우가 해당한다. 반대로 전원을 뽑거나 ALT+F4로 강제로 끄는 것은 비정상 종료이고, 이때는 AUTO ROLLBACK이 발생한다.


ㅇ SAVEPOINT — 부분 롤백

그렇다면 작업 중 오타나 오류가 나면 처음으로 돌아가야 할까? 예전에는 그랬다. 오타·문법 오류·무결성 위반 같은 에러 하나로 트랜잭션 전체가 취소되곤 했다. 이를 막기 위해 취소 시점을 구분하는 SAVEPOINT를 쓴다. INSERT 명령문마다 SAVEPOINT를 주면, 문제가 생긴 마지막 문장만 롤백하는 식으로 트랜잭션을 보호할 수 있다.


ㅇ 트랜잭션 개수 세어 보기

연습 삼아 트랜잭션을 세어 보자. DML(INSERT·UPDATE·DELETE)은 연속된 묶음이 하나의 트랜잭션이고, DDL(CREATE 등)은 문장 하나가 트랜잭션 하나라는 규칙을 적용하면 된다.

1
2
3
4
5

INSERT
UPDATE     ┐ → 하나의 트랜잭션
CREATE     → 하나의 트랜잭션
INSERT     → 하나의 트랜잭션
CREATE     → 하나의 트랜잭션

INSERT·UPDATE가 묶여 하나, CREATE 하나, INSERT 하나, CREATE 하나 — 모두 4개다.

다음으로 DB에 실제 저장되는 것을 가려 보자.

1
2
3
4
5

① INSERT
② UPDATE
  CREATE
  ROLLBACK
③ INSERT

CREATE는 DDL이라 실행 즉시 AUTO COMMIT되어, 그 직전의 ①INSERT·②UPDATE까지 함께 커밋된다. 뒤이은 ROLLBACK은 이미 커밋된 것을 되돌리지 못하고, 그 시점 이후의 트랜잭션(③ 직전까지)만 취소한다. 따라서 저장되는 것은 ①·②다.

조금 더 복잡한 경우다.

1
2
3
4
5
6
7

① INSERT
② UPDATE
  CREATE
③ INSERT
  ROLLBACK
④ INSERT
  COMMIT

CREATE의 AUTO COMMIT으로 ①·②가 저장되고, ③INSERT는 뒤따르는 ROLLBACK에 의해 취소되며, ④INSERT는 COMMIT으로 저장된다. 결과는 ①·②·④다.



ㅇ 읽기 일관성·병행제어, 그리고 데드락

데이터베이스의 핵심 특징 중 하나는 동시성이다. 하나의 데이터베이스에 동시에 여러 사용자가 접근해 작업할 수 있고, 수많은 응용 프로그램이 하나의 DB에 붙어 동시에 트랜잭션을 수행한다. 이런 환경에서 데이터의 정합성을 지키는 두 가지 장치가 읽기 일관성과 병행제어다.


ㅇ 읽기 일관성

창을 두 개 띄워 같은 DB에 동시 접속했다고 하자. 1번 창에서 141번 사원의 급여를 수정(UPDATE, 즉 DML)하면 트랜잭션 t1이 진행 중이 되고, 아직 DB에는 저장되지 않은 상태다. 이때 2번 창에서 141번의 급여를 조회하면 변경되기 이전 값이 보인다. 이것이 읽기 일관성이다.

왜 이렇게 동작해야 할까? 예를 들어 보자.

  • 현재 : 1명은 급여 1억, 나머지 50명은 10만 원
  • 120번 사원이 (롤백하면 되니까) 자기 급여를 2억으로 UPDATE → t1이 진행되어 1명 1억 / 1명 2억 / 49명 10만 원
  • 금요일에 급여 1,000만 원 인상이 결정되어, 다른 곳에서 t2가 “급여 10만 원인 사람”을 1,000만 원으로 UPDATE

만약 t2가 t1이 변경한(아직 커밋되지 않은) 값을 본다면, 10만 원인 사람을 49명만 찾게 된다. 이를 커밋하면 1명 1억 / 1명 2억 / 49명 1,000만 원이 된다. 그런데 120번이 t1을 롤백하면 자기 급여만 10만 원으로 돌아가, 결국 혼자만 10만 원이고 나머지는 1,000만 원이 되는 모순이 생긴다.

다른 트랜잭션이 변경 중(미커밋)인 값을 보게 두어선 안 된다. 누구에게도 변경된 값을 보여주지 않고, 일관되게 변경 이전의 값을 보여주는 것 — 이것이 읽기 일관성이다.

나무 의자를 만들다 못 하나만 박으면 완성인 상황에서, 화장실이 급해 작업실 문을 열어 둔 채 나갔더니 누군가 망치로 의자를 부숴 버렸다고 하자. 다른 트랜잭션이 끼어든 것이다. 문을 잠그고 나갔다면 부수지 못했을 것이다. 그 잠금장치가 바로 LOCK이다.


ㅇ 병행제어와 LOCK

1번 창에서 141번 행에 트랜잭션을 수행 중인 상태에서 2번 창이 같은 141번 행을 UPDATE하려 하면, 커서만 깜빡이며 멈춘다. 데이터베이스 대기 상태다.

병행제어는 트랜잭션끼리 서로 침범하지 못하도록 관리하는 것으로, LOCK이라는 자원을 활용한다. t1 트랜잭션이 수행되면 서버가 141번 행에 락을 건다. 그래서 2번 창이 141번에 무언가를 시도하면 t1이 끝날 때까지 대기 상태가 된다. t1의 트랜잭션이 종료되어야 비로소 2번 창의 t2가 실행된다. 참고로 락은 행 단위라서, 같은 시점에 142번 행을 수정하는 것은 잘 된다. 다른 행이라 락이 걸려 있지 않기 때문이다.

프로젝트에서 Java 코드 안에 DML 연산을 넣을 때는 반드시 커밋을 함께 넣는다. 커밋을 빠뜨리면 트랜잭션이 계속 진행 중인 상태가 되어, 다른 응용 프로그램의 작업이 대기 상태에 빠진다. 반대로 롤백을 넣으면 실행할 때마다 작업이 취소되어, INSERT를 했는데도 회원가입이 되지 않는 황당한 상황이 된다.


ㅇ 데드락(교착 상태)

서로가 서로의 락을 기다리면 어떻게 될까?

대기 상태로 있다가 한쪽 창에 에러가 뜬다. 교착 상태(deadlock)다.

1
2

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

예전에는 양쪽 모두 대기 상태에 빠져 트랜잭션 종료 명령조차 실행하지 못하다가, 결국 시스템이 강제로 종료(셧다운)되곤 했다. DB 셧다운은 막대한 피해이고, 업무 시간에는 절대 일어나선 안 된다. 그래서 지금은 서버가 강제로 한쪽 트랜잭션을 풀어 주는 방식으로 데드락을 해소한다.

그렇다면 어느 지점까지 풀릴까? 직접 실험해 보았다. 1번 창에서 142 UPDATE → 2번 창에서 141 UPDATE → 1번 창에서 141 UPDATE(락) → 2번 창에서 142 UPDATE(락) → 양쪽 모두 락이라 데드락. 이때 풀리는 쪽은 1번 창이었고, 1번 창의 두 번째 수행(141 UPDATE)이 없던 일이 되었다. 즉 직전 SAVEPOINT(1번 창의 142 UPDATE 시점)로 되돌아간 셈이다. 다만 2번 창은 여전히 락 상태로 남았다.



ㅇ SYSDATE 함수와 DUAL 테이블

ㅇ SYSDATE 함수

SYSDATE는 현재 날짜와 시간을 기록하는 함수다. 테이블에 실제로 저장된 데이터가 아니라, 특정 이벤트가 발생할 때마다 그 시점의 값을 추출해 보여준다. (접속해 있는 사용자 아이디를 조회하는 USER 함수도 같은 성격이다.)

1
2
3
4
5
6

SELECT sysdate
FROM dual;

SYSDATE
--------
21/05/26

시스템의 현재 날짜가 출력된다. 사용자의 로그인 정보와 함께 접속 시각을 기록하고 싶을 때 유용하다. 다만 SYSDATE만으로는 연·월·일까지만 얻을 수 있고, 시·분·초는 다른 방법이 필요하다(다음 글에서 다룬다). 또한 날짜 데이터이므로 숫자나 문자 컬럼에 넣으면 에러가 난다.


ㅇ DUAL 테이블

DUAL은 실제 데이터를 가진 테이블이 아니라, 그때그때 값을 추출해 받아들이도록 만들어 둔 임시 테이블이다. 테이블에 저장되지 않은 값을 계산하거나 함수 결과만 확인하고 싶을 때 쓰며, 현업에서도 은근히 자주 사용한다.

1
2
3
4
5
6
7
8
9
10
11

DESC dual
 Name        Null?    Type
 --------- -------- -----------------
 DUMMY               VARCHAR2(1)

SELECT *
FROM dual;

DU
----
X

DUMMY라는 컬럼 하나만 가지며(가변 길이 문자 1byte), 그 안에는 X라는 변수 값이 들어 있다.



ㅇ INSERT — 새 행 추가

INSERT는 테이블에 새 행을 추가한다. 몇 가지 성질을 먼저 짚어 둔다.

  • 새 행은 맨 마지막에 저장된다. 테이블은 행의 순서를 지정하지 않고, 저장된 주소가 아니라 안의 데이터로 찾기 때문에 행이 어디에 있든 상관하지 않는다.
  • 한 번에 하나의 행만 삽입한다. INSERT 명령문 하나는 한 행만 추가하므로, 100개의 행을 넣으려면 INSERT 문이 100개 필요하다.

삽입 전에 DESC로 테이블 구조를 확인해, 각 컬럼에 어떤 데이터 타입과 값을 넣어야 할지 미리 점검하는 것이 좋다.

1
2

INSERT INTO 테이블 (컬럼1, 컬럼2, 컬럼3)   -- 컬럼 목록은 선택 사항
VALUES (각 컬럼에 맞는 데이터 값)

VALUES 절을 쓸 때의 핵심은, INTO 절에 명시된 컬럼의 수·순서·타입·길이·조건에 맞게 VALUES 절의 데이터가 1:1로 매핑되어야 한다는 것이다.


ㅇ 테이블 이름만 쓰는 방법

부서 테이블에는 department_id(PK)가 10번부터 270번까지 27개 행이 있다. 여기에 300번 ITCENTER 부서를 추가한다.

1
2
3

INSERT INTO departments
VALUES (300, 'ITCENTER', 100, 1700);
1 row created.

컬럼 목록을 생략했으므로 (department_id, department_name, manager_id, location_id) 순서와 데이터 타입에 그대로 맞춰 값을 넣었다.


ㅇ 컬럼 이름을 직접 명시하는 방법

310번 ADMIN 부서를 추가한다. 컬럼을 직접 적으면 의도가 분명해진다.

1
2

INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (310, 'ADMIN', 101, 1800);

컬럼 순서를 바꿔도 된다. 다만 VALUES도 그 순서에 맞춰 적어야 한다.

1
2

INSERT INTO departments (location_id, manager_id, department_id, department_name)
VALUES (1700, 100, 320, 'CCC');

결국 INTO 절과 VALUES 절이 1:1로 매핑되는 것이 INSERT의 포인트다. ★


ㅇ NULL을 입력하는 두 가지 방법

  • 암시적 방법(implicit) : INTO 절에서 컬럼을 빼면 그 컬럼에는 자동으로 NULL이 들어간다. INSERT는 행 단위 작업이라, 빠진 컬럼에 NULL을 채워 행을 완성하기 때문이다. 예를 들어 4개 컬럼 중 2개만 적고 VALUES에도 2개만 넣으면 나머지 둘은 NULL이 된다.
  • 명시적 방법(explicit) : VALUES 절에 NULL 키워드를 직접 적는다. 의도가 분명해 더 선호된다.
1
2

INSERT INTO departments
VALUES (340, 'FFFF', NULL, NULL);

이때 NULL에 작은따옴표를 붙이면 안 된다. 'NULL'은 빈 값이 아니라 ‘NULL’이라는 문자열을 저장하라는 뜻이 되어 버린다.


ㅇ 현재 날짜 삽입

VALUES에 값 대신 SYSDATE를 넣으면 현재 날짜가 저장된다.

특정 날짜 값을 직접 삽입하는 방법은 아직 다루지 않았으므로 넘어간다. 한편, 여러 행을 넣을 때 값을 키보드로 입력받는 치환 변수(&a, &b) 방식도 있지만, INSERT에서는 잘 쓰이지 않아 소개만 하고 지나간다. 서브쿼리를 포함하는 INSERT는 테이블 생성(DDL)을 배운 뒤 함께 다룬다.


ㅇ INSERT에서 자주 하는 실수 — 무결성 검증

데이터를 삽입할 때 DB는 무결성 검증을 수행한다.

  • 컬럼 무결성 : 컬럼의 데이터 타입과 길이에 맞는 데이터만 삽입할 수 있다. 예를 들어 department_id가 숫자(5)라면 'aa'(문자)나 293845(6자리)는 들어가지 못한다.
  • 제약조건 무결성 : PK·FK 같은 제약조건이 걸려 있으면 그 조건에 맞는 데이터만 들어온다. 이미 존재하는 10번을 department_id에 넣으면, PK는 중복을 허용하지 않으므로 에러가 난다.
1
2
3
4

INSERT INTO departments
VALUES (10, 'AA', 100, 1700);
ERROR at line 1:
ORA-00001: unique constraint (HR.DEPT_ID_PK) violated

department_id는 PK인데 10번이 이미 있어 발생한 오류다.

1
2
3
4

INSERT INTO departments
VALUES ('AA', 'AA', 100, 1700);
ERROR at line 2:
ORA-01722: invalid number

department_id는 숫자만 가능한데 문자를 넣어 발생한 오류다.

다시 강조하면 INSERT는 한 번에 하나의 행만 삽입한다. INSERT 하나에 VALUES를 여러 줄 이어 붙이는 방식은 동작하지 않는다(엑셀 로드나 고급 기능으로 여러 행을 넣을 수는 있지만, 기본 문법은 그렇다).



ㅇ UPDATE와 DELETE

ㅇ UPDATE

INSERT와 DELETE가 행 단위 작업인 데 비해, UPDATE는 열(컬럼) 단위 작업이다.

1
2
3

UPDATE 테이블
SET 컬럼이름 = 변경 값
WHERE 수행할 특정 행   -- 선택 사항

206번 사원의 급여를 20000으로 수정한다.

1
2
3
4

UPDATE employees
SET salary = 20000
WHERE employee_id = 206;
1 row updated.

WHERE 절을 생략하면 테이블의 모든 행이 수정된다. 위 예제에서 WHERE를 빼면 전 사원의 급여가 20000이 되어 버린다. 그럴 땐 즉시 롤백해야 한다.


ㅇ 서브쿼리로 갱신

114번 사원의 업무와 급여를 205번 사원과 같게 맞춘다. SET의 값 자리에 서브쿼리를 둘 수 있다.

1
2
3
4
5
6
7
8
9

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 = 114;
1 row updated.

여러 컬럼을 서브쿼리로 갱신할 때는 컬럼 사이의 콤마를 빠뜨리기 쉬우니 주의한다.

UPDATE 역시 무결성 제약조건의 영향을 받는다. 부모 테이블에 없는 값으로는 자식 테이블을 갱신할 수 없다.


ㅇ DELETE

DELETE는 행을 삭제한다.

1
2

DELETE FROM 테이블
WHERE 조건   -- 선택 사항, 생략하면 모든 행 삭제

그렇다면 WHERE 절 없이 부서 테이블 전체를 지우면 정말 다 삭제될까? 그렇지 않다.

1
2
3

DELETE FROM departments;
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found

다른 테이블에서 외래 키로 참조 중인 기본 키를 포함하는 행은 삭제할 수 없다. 사원 테이블이 부서 테이블의 부서번호를 참조하고 있으므로, 부서를 함부로 지우면 사원들의 근거 값이 사라지기 때문이다.



ㅇ DML과 KEY — 부모·자식 테이블의 제약

PK·FK로 연결된 테이블에 DML을 수행하면, 키 제약조건 때문에 특정 연산이 막히거나 허용된다. 부서 테이블(부모)에 10·20·30번이 있고, 사원 테이블(자식)이 그 부서번호를 FK로 참조한다고 하자.


ㅇ 자식 테이블에서 DML을 수행할 때

  • INSERT : 부서 10번을 가진 사원 삽입 → 가능(존재하는 부서). 부서 40번을 가진 사원 삽입 → 불가(부모에 40번이 없음). FK는 참조하는 값의 범위 안에서만 쓸 수 있다.
  • UPDATE : 사원의 부서를 10 → 20으로 갱신 → 가능(존재하는 부서로 이관). 10 → 40으로 갱신 → 불가(부모에 40번이 없음).
  • DELETE : 부서 10번을 가진 사원 삭제 → 가능(퇴사 처리).

자식 테이블은 참조 범위 안에서라면 DML 연산을 자유롭게 수행할 수 있다. 여기서는 10·20·30번이 그 범위다.


ㅇ 부모 테이블에서 DML을 수행할 때

까다로운 쪽은 부모 테이블이다. 자식이 참조하고 있는 값을 함부로 바꾸면 자식의 근거가 무너지기 때문이다.

  • INSERT : 부서 10번 삽입 → 불가(PK라 중복 불허). 부서 40번 삽입 → 가능(존재하지 않던 값).
  • UPDATE :
    • 10 → 20 갱신 → 불가(PK 중복, 20번이 이미 있음).
    • 10 → 40 갱신 → 상황에 따라 다르다. 현재 구조에서는 불가다. 10번을 자식 테이블이 참조 중이기 때문이다. 부모에게 잘 다녀오겠다고 나왔는데 돌아와 보니 다른 이가 “우리 애”라며 반기는 격으로, 참조 중인 부서번호가 바뀌면 자식의 근거 값이 사라져 무결성이 깨진다.
    • 30 → 40 갱신 → 가능. 30번은 자식 테이블에서 사용되지 않아 참조되는 값이 없기 때문이다.
  • DELETE : 10번 삭제 → 불가(참조 중). 30번 삭제 → 가능(참조되지 않음).

즉, 자식 테이블이 참조하고 있는 데이터는 갱신·삭제될 수 없다. FK를 만든 가장 큰 이유가 관계 설정과 더불어 종속적 삭제를 방지하는 것이기 때문이다(부모인 10번을 지웠을 때 자식이 함께 망가지지 않도록). 다만 FK를 설정할 때 특정 옵션을 주면 변경되게 만들 수도 있는데, 이는 거의 쓰지 않으며 다음에 다룬다.



ㅇ 테이블의 관계를 이해하는 것이 핵심

실무에서 테이블이 독립적으로 움직이는 경우는 거의 없다. 90% 이상이 서로 연결되어 있다. 이 관계를 모른 채 DML을 수행하면, 문법에는 오류가 없는데도 자꾸 동작하지 않는 상황에 빠진다. 초보 개발자가 흔히 겪는 일이다. 게다가 DB는 하나의 데이터베이스에 수많은 사용자가 동시에 들어와 작업하므로, 관계를 정확히 인지하고 있어야 한다.

문법은 책을 보며 찾아 쓰면 된다. 정작 중요한 것은 부모·자식 간 테이블 관계와 동시 트랜잭션, 이 두 가지다. 이것이 DML 연산의 핵심이다.



ㅇ 객체와 데이터 타입 — DDL 준비

여기서부터는 다음 글에서 다룰 9장 테이블 생성 및 관리(DDL)의 준비에 해당한다.

ㅇ 객체

DB 안에는 테이블처럼 구조를 가진 여러 객체가 있다. DDL은 이런 객체들을 생성·수정·삭제하는 명령어다.

  • 뷰(view) : 데이터 접근을 제어하는 객체. 예를 들어 employees 테이블에는 전 사원 정보가 들어 있어 10번 부서 직원이 20번 부서 직원의 정보까지 보게 되는데, 뷰를 통하면 10번만 제한해서 보여줄 수 있다. 보안과 관련된, 자주 쓰는 객체다.
  • 시퀀스(sequence) : 중복되지 않는 번호를 1, 2, 3… 자동으로 생성한다. 특히 PK를 지정할 때 많이 쓴다.
  • 인덱스(index) : 데이터 검색 속도를 높이는 객체로, 튜닝과 관련이 있다. 튜닝의 본래 개념은 문제가 일어나기 전에 미리 개선하는 행위다.
  • 동의어(synonym) : emp처럼 별칭을 붙이는 것으로, 지금은 몰라도 된다.

이 중 뷰·시퀀스·인덱스는 반드시 숙지해 둔다.


ㅇ 테이블 이름 지정 규칙

테이블을 만든다는 것은 ① 테이블 이름을 정하고 ② 컬럼들의 이름을 지정하고 ③ 컬럼의 데이터 타입과 길이를 지정하는 일이다.

  • 반드시 문자로 시작한다. 숫자나 특수기호로 시작할 수 없다.
  • 최대 30자까지 가능하다.
  • 특수기호는 _ $ #만 쓸 수 있다. 한글도 가능하지만 권장하지 않는다(영어 알파벳은 1byte, 한글은 받침 때문에 2byte를 할당한다).
  • 한 계정 안에 같은 이름의 테이블·컬럼은 존재할 수 없다. 단, 테이블이 다르면 같은 이름의 컬럼은 존재할 수 있다.
  • SELECT·FROM 같은 예약어는 이름으로 쓸 수 없다.

오라클 테이블은 두 종류다. 하나는 우리가 다루는 사용자 테이블이고, 다른 하나는 데이터 딕셔너리다. 데이터 딕셔너리에는 모든 정보가 메타데이터 형태로 저장된다. 메타데이터란 하나의 물리적 데이터에 관련된 모든 정보로, “101번 사원이 어느 테이블에 저장되어 있고, 얼마만큼의 크기를 쓰며, 누구의 소유인지” 같은 것이다. 오라클 서버의 뇌에 해당하는 부분으로, 서버가 자동으로 생성·관리하며 우리가 손댈 수 있는 공간이 아니다.


ㅇ 데이터 타입

앞쪽의 문자·숫자·날짜 타입이 자주 쓰이고, 뒤쪽은 크기가 큰 데이터를 다루는 타입들이다. 그중 BFILE은 멀티미디어 처리에 많이 쓰는데, 데이터는 외부에 두고 테이블에는 포인터(링크)만 둔다. ROWID는 인덱스가 사용하는 타입으로, 행의 물리적 주소 정보(아파트 101동 101호처럼)를 저장한다.


문자 데이터 — CHAR vs VARCHAR2

  • CHAR : 고정 길이. aaa를 CHAR(5)에 저장하면 공간 5개를 먼저 할당한 뒤 채우며, 남은 공간은 재사용하지 않는다. 공간은 낭비되지만 성능이 월등하다.
  • VARCHAR2 : 가변 길이. 들어오는 데이터만큼만 할당한다. 공간 활용도가 월등하지만 성능은 떨어진다.

CHAR의 성능이 좋은 이유는 데이터 딕셔너리와 관련이 있다. VARCHAR2는 값이 들어올 때마다 공간이 새로 할당되어 사용 공간 정보가 끊임없이 갱신되지만, CHAR는 공간을 미리 쪼개 두어 디스크에 접근해 공간 정보를 바꿀 일이 없다.

다만 CHAR는 자릿수를 신중히 정해야 한다. 15자리 주민번호를 100자리로 잡으면 매번 85자씩 낭비된다(물론 나중에 ALTER로 크기를 바꿀 수는 있다).

가장 이상적인 크기는 실제 입력 데이터보다 약 20% 크게 잡는 것이다. DB의 확장성을 고려한 여유다.


숫자 데이터 — number, number(p), number(p, s)

  • number : 음수·0·양수를 모두 쓴다. 가변 폭이 너무 커 저장 공간 성능이 떨어져 많이 쓰지 않는다.
  • number(p) : 정수만 쓴다.
  • number(p, s) : 소수점 s자리까지 표현한다. 소수점까지 다룰 수 있어 가장 선호된다.


날짜 데이터 — date, timestamp

  • date : 연·월·일을 저장한다. 출력 기본 형식은 언어 체계(한글·영어 등)에 따라 달라진다. 입력한 그대로 반영되어 시차 문제가 생길 수 있고, 연도를 두 자리로 저장하던 밀레니엄 이슈도 있었다(실제로는 1999년 12월 31일 자정을 문제없이 넘겼다).
  • timestamp : 시·분·초까지 저장한다. 데이터 타입을 timestamp로 두면 SYSDATE도 시·분·초까지 볼 수 있어, 요즘 많이 쓰인다.

참고로 '01/01/01'을 날짜와 문자에 각각 입력하면 저장 방식이 다르다. date로 저장하면 숫자(number)로 변환되어 저장되고, char로 저장하면 아스키코드로 변환되어 문자로 저장된다.



ㅇ 디스크와 트랜잭션 일괄처리

DML이 왜 묶음 단위로 처리되는지는 디스크의 동작을 보면 이해된다. 디스크(저장 공간)는 블록으로 구성되며, 처음에는 비어 있다. 디스크는 데이터를 순차적으로 채워 저장하고, 검색도 일반적으로 순차 검색을 한다. 예를 들어 30을 찾으려면 블록 3개(10 → 20 → 30)를 읽어야 한다.

흥미로운 점은, 시스템 수준에는 업데이트 기능이 없다는 것이다. 변경은 곧 삭제와 삽입으로 이루어진다. 20을 50으로 바꾸는 것은 20을 삭제하고 50을 맨 뒤에 추가하는 것이고, 20이 있던 자리는 비어 디스크 조각이 된다. 사용하다 보면 이런 조각이 쌓여 읽어야 할 블록 수가 늘어나는데, 이를 회수해 빈 공간을 합치는 것이 디스크 조각 모음이다(윈도우가 느려질 때 조각 모음을 하는 이유다).

실제로 DB 내에서 공간이 할당되면 그 사용 정보가 데이터 딕셔너리에 파일 형태로 저장되고, 공간이 할당될 때마다 그 정보가 계속 갱신되어야 한다. 그래서 트랜잭션을 일괄 처리한다. INSERT마다 매번 저장하면 저장 정보의 I/O가 폭발적으로 늘어나지만, 묶어서 처리하면 I/O 효율이 좋아진다. 트랜잭션은 시스템의 저장 공간 활용도까지 고려해 만들어진 개념인 셈이다.



다음 글에서는 본격적으로 객체를 다루는 DDL(CREATE·ALTER·DROP)로, 테이블을 직접 생성하고 구조를 바꾸고 삭제하는 방법을 정리한다.

CS, Database
Database SQL Oracle DML INSERT UPDATE DELETE 트랜잭션 KEY PK FK SYSDATE 데이터타입
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.