특정 단어가 몇 번 포함됐는지 개수 세기

 

전체 문자열 내에서 특정 단어가 포함된 경우가 몇 번인지 세려면 COUNT 계열 함수로는 어렵습니다.

이런 경우 전체 문자열의 개수에서 특정 단어를 모두 지운 개수를 뺀 후 다시 단어의 문자 개수로 나눠주는 다소 복잡한 계산 과정이 필요합니다.

이 작업에서 사용되는 함수는 다음과 같습니다.

 

LEN (문자열)

문자열 내의 문자가 몇 개 있는지 세어 반환한다.

문자열 - 문자를 세려는 값 또는 해당 값이 입력된 셀

 

SUMPRODUCT (배열1, 배열2, ...)

데이터 범위 내 항목을 서로 곱하고 얻은 결과를 모두 더한 값을 반환해준다.

배열 - 계산하려는 값을 가진 데이터 범위 또는 집합

 

주의 사항

이 함수는 덧셈을 구하는 SUM 함수와 곱셈을 계산하는 PRODUCT 함수가 결합된 함수다.

이 함수는 주로 SUM 함수를 사용한 배열 수식을 간편하게 입력할 목적으로 사용된다.

 

엑셀 바이블저자최준선출판한빛미디어발매2019.05.08.

 

 

 

동시에 여러 조건을 판단하기

 

IF 함수나 IFS 함수 모두 한 번에 한 개의 조건을 판단할 수 있다.

하지만 여러 조건을 동시에 판단한 후 원하는 값을 반환해야 하는 경우도 종종 있다.

이런 경우에는 여러 개의 조건을 동시에 판단할 수 있는 AND, OR 함수를 사용하면 편리하다.

 

AND (조건식1, 조건식2, 조건식3, ...)

인수로 받은 모든 조건이 TRUE일 때만 TRUE를 반환하고, 하나라도 FALSE이면 FALSE를 반환한다.

 

조건식 - TRUE, FALSE를 반환하는 판단식

 

주의 사항

[조건식] 인수는 최대 255개까지 사용 가능하다.

AND 함수를 사용하지 않으면 필연적으로 IF 함수를 중첩 사용해야 한다.

다음은 조건식 세 개를 모두 만족할 때 원하는 값을 반환하는 IF 함수의 중첩 사용 예다.

 

=IF(조건식1, IF(조건식2, IF(조건식3, "반환값",

 

위 수식을 조금 간단하게 구성하려면 다음과 같은 수식을 사용할 수 있다.

=IF((조건식1*조건식2*조건식3)=1, "반환값", ...)

 

OR (조건식1, 조건식2, 조건식3, ...)

AND 함수와 사용 방법은 동일하다.

하나라도 TRUE이면 TRUE를 반환하고, 모두 FALSE이면 FALSE를 반환한다.

 

주의 사항

OR 함수를 사용하지 않아도 IF 함수를 중첩 사용해야 한다.

다음은 조건식 세 개 중 하나를 만족할 떄 원하는 값을 반환하는 IF 함수의 중첩 사용 예다.

 

=IF(조건식1, "반환값", IF(조건식2, "반환값", IF(조건식3, "반환값", ...

 

이런 수식을 조금 간단하게 구성하려면 다음과 같은 수식을 사용할 수 있다.

 

=IF((조건식1+조건식2+조건식3)>0, "반환값", ...)

 

엑셀 바이블저자최준선출판한빛미디어발매2019.05.08.

 

 

 

값을 비교하고 결과를 원하는 값으로 대체하기

 

IF (조건식, TRUE일 때 값, FALSE일 때 값)

 

IF 사용자의 조건을 판단해 TRUE인 경우와 FALSE인 경우에 각각 다른 값을 반환하도록 합니다.

 

조건식 - TRUE, FALSE 값을 반환하는 계산식

TRUE일 때 값 - 조건식의 결과가 TRUE일 때 대체할 값 또는 계산식

FALSE일 때 값 - 조건식의 결과가 FALSE일 때 대체할 값 또는 계산식

 

주의 사항

IF 함수는 한 번에 하나의 조건만 처리 가능하다.

예를 들어 30대 남자 데이터만 구분해 처리하고 싶다면 '나이'와 '성별'을 구분하는 값이 입력되어 있어야 하며,

조건은 나이가 30 이상이고 40 미만이어야 하며 성별은 '남'이어야 한다.

이 경우 조건은 세 개가 되므로, IF 함수 안에 IF 함수를 중첩해 다음과 같은 수식이 구성되어야 한다.

 

엑셀 바이블저자최준선출판한빛미디어발매2019.05.08.

 

 

 

데이터 일관성(data consistency)은 트랜잭션에 의한 변경을 일관된 상태로 볼 수 있는 것을 의미한다.

데이터 동시성과 데이터 일관성은 트레이드오프(tradeoff) 관계를 가진다.

동시성이 높아지면 일관성이 낮아지고, 일관성이 높아지면 동시성이 낮아진다.

 

[다중 버전 읽기 일관성]

오라클 데이터베이스는 다중 버전 읽기 일관성(Multiversion Read Consistency, MVRC)을 지원한다.

(해당 기능을 데이터 동시성 관점에서 Multiversion Concurrency Control, MVCC로 부르기도 한다.)

다중 버전 읽기 일관성은 쿼리가 시작된 시점의 데이터를 조회할 수 있게 해주는 기능이다.

쿼리가 시작된 시점의 SCN과 블록의 SCN을 비교하여 쿼리가 시작된 이후에 블록이 변경되었다면 언두 세그먼트의 변경 이전 데이터를 상요하여 쿼리가 시작된 시점의 데이터를 반환한다.

 

-- 아래 예제를 순서대로 수행해보자.

S1 세션에서 데이터를 변경했더라도 커밋 전까지는 S2 세션에서 변경 전 데이터가 조회된다.

커밋을 수행하기 전까지 변경된 데이터는 데이터를 변경한 세션에서만 조회할 수 있다.

 

S2

SELECT VL FROM T1 WHERE CD = 1; -- 40

 

S1

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

SELECT VL FROM T1 WHERE CD = 1; -- 50

 

S2

SELECT VL FROM T1 WHERE CD = 1; -- 40

 

S1

COMMIT;

 

S2

SELECT VL FROM T1 WHERE CD = 1; -- 50

 

언두 세그먼트의 공간은 일정 시점이 지나면 재사용될 수 있다.

쿼리 시작 시점의 변경 이전 데이터가 저장된 공간이 재사용되면 해당 시점의 데이터를 반환할 수 없기 때문에 "ORA-01555: 너무 이전 스냅샷" 에러가 발생한다. 다중 버전 읽기 일관성의 부작용으로 생각할 수 있다.

 

[문장 수준 읽기 일관성]

문장 수준 읽기 일관성(statement-level read consistency)는 단일 쿼리 수준의 읽기 일관성이다.

오라클 데이터베이스는 다중 버전 읽기 일관성을 지원하기 때문에 락을 사용하지 않고 문장 수준 읽기 일관성을 보장한다.

 

-- 예제를 위해 SYS 사용자로 접속한 세션에서 SCOTT 사용자에게 DBMS_LOCK 패키지의 실행 권한을 부여해야 한다.

 

C:\>sqlplus sys/ora@ora12cr2 AS SYSDBA

 

GRANT EXECUTE ON DBMS_LOCK TO scott;

 

-- 아래와 같이 함수를 생성하자.

CREATE OR REPLACE FUNCTION F1 (I_SECONDS IN NUMBER) RETURN NUMBER

IS

BEGIN

DBMS_LOCK.SLEEP (I_SECONDS);

RETURN 1;

END F1;

 

 

S1 세션의 첫 번째 쿼리는 F1 함수로 인해 10초 후 결과가 반환된다.

그 사이에 S2 세션에서 데이터를 변경하고 커밋을 수행하더라도 쿼리가 시작한 시점의 값 (50)이 반환된다.

T1 테이블을 다시 조회하면 변경된 값(40)이 반환된다.

 

S1

SELECT *

FROM T1

WHERE CD = 1

AND F1 (10) = 1;

 

S2

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

COMMIT;

 

S1

SELECT * FROM T1 WHERE CD = 1;

 

[트랜잭션 수준 읽기 일관성]

트랜잭션 수준 읽기 일관성 (Transaction-level read consistency)은 말 그대로 트랜잭션 수준의 읽기 일관성이다. 동일한 트랜잭션 내의 쿼리는 다시 조회하더라도 결과가 동일해야 한다. 오라클 데이터베이스는 기본적으로 트랜잭션 수준 읽기 일관성을 보장하지 않는다. 트랜잭션 수준 읽기 일관성을 보장하려면 트랜잭션 고립화 수준을 변경해야 한다.

 

ANSI/ISO SQL 표준은 아래 네 가지의 트랜잭션 고립화 수준(transaction isolation level)을 정의하고 있다.

오라클 데이터베이스는 read committed, serializable 수준을 지원한다는 기본값은 read committed다.

 

고립화 수준 - 설명

read uncomitted - 커밋되지 않은 데이터를 읽는 것을 허용

read committed - 커밋된 데이터만 읽는 것을 허용

repeatable reads - 트랜잭션 내에서 읽은 데이터는 갱신과 삭제를 금지

serializable - 트랜잭션 내에서 읽은 테이블에 삽입을 금지

 

아래는 읽기 일관성이 보장되지 않을 때 발생할 수 있는 읽기 이상 현상(read phenomena)이다.

 

읽기 현상 - 설명

dirty read - 동일한 쿼리가 커밋되지 않은 데이터를 읽어 다른 결과를 반환

non-repeatable read - 동일한 쿼리가 갱신 또는 삭제에 의해 다른 결과를 반환

phantom read - 동일한 쿼리가 삽입에 의해 다른 결과를 반환

 

트랜잭션 고립화 수준에 따라 아래와 같은 읽기 이상 현상이 발생할 수 있다.

 

dirty read - non-repeatable read - phantom read

read uncommitted Y - Y - Y

read committed Y - Y

repeatable reads Y

serializable

 

오라클 데이터베이스는 repeatable reads 수준을 지원하지 않지만 FOR UPDATE 절을 사용하여 non-repeatable read 현상을 회피할 수 있다.

아래 예제는 S1 세션에서 FOR UPDATE 절을 사용했기 때문에 S2 세션에서 값을 갱신할 수 없다.

S1 세션에서 T1 테이블을 반복해서 조회하더라도 값이 변경되지 않는 것이 보장된다.

 

S1

SELECT * FROM T1 FOR UPDATE;

 

S2

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

-- 블로킹

 

S1

SELECT SUM (VL) AS VL FROM T1; -- 100

 

SELECT SUM (VL) AS VL FROM T1; -- 100

 

S2

ROLLBACK;

 

S1

COMMIT;

 

아래 예제는 S1 세션에서 FOR UPDATE 절을 사용했지만 S2 세션에서 신규 행을 삽입하는 것을 방지할 수 없기 때문에 변경된 값(150)이 조회된다. FOR UPDATE 절을 사용하더라도 phantom read 현상을 방지할 수 없다.

 

S1

SELECT * FROM T1 FOR UPDATE;

 

SELECT SUM (VL) AS VL FROM T1; -- 100

 

S2

INSERT INTO T1 (CD, VL) VALUES (3, 50);

COMMIT;

 

S1

SELECT SUM (VL) AS VL FROM T1; -- 150

 

COMMIT;

 

[SET TRANSACTION 문]

트랜잭션 고립화 수준을 serializable 수준으로 설정하려면 SET TRANSACTION 문을 사용해야 한다.

SET TRANSACTION 문의 구문은 아래와 같다.

기본값은 READ WRITE, READ COMMITTED다.

 

SET TRANSACTION {{READ {ONLY | WRITE}

| ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}

| USE ROLLBACK SEGMENT rollback_segment} [NAME string]

| NAME string};

 

항목 - 설명

READ ONLY - 읽기 전용 트랜잭션으로 설정 (트랜잭션 수준 읽기 일관성 보장)

READ WRITE - 읽기 쓰기 트랜잭션으로 설정 (문장 수준 읽기 일관성 보장)

SERIALIZABLE - 트랜잭션 고립화 수준을 SERIALIZABLE로 설정

READ COMMITTED - 트랜잭션 고립화 수준을 READ COMMITTED로 설정

 

아래 예제는 SET TRANSACTION 문을 사용하여 트랜잭션 고립화 수준을 SERIALIZABLE로 설정했기 때문에 phantom read 현상이 발생하지 않는다.

COMMIT을 수행하면 트랜잭션이 종료된다.

(트랜잭션 고립화 수준은 트랜잭션이 종료되면 초기화된다. 동일한 세션에서 새로운 트랜잭션을 시작하면 기본값인 READ WRITE로 트랜잭션이 생성된다.)

 

S1

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE;

 

SELECT SUM (VL) AS VL FROM T1; -- 150

 

S2

INSERT INTO T1 (CD, VL) VALUES (4, 50);

COMMIT;

 

S1

SELECT SUM (VL) AS VL FROM T1; -- 150

 

COMMIT;

 

SELECT SUM (VL) AS VL FROM T1; -- 200

 

 

-- 아래 예제는 S1 세션에서 에러가 발생한다. 트랜잭션 고립화 수준을 SERIALIZABLE로 설정하면 다른 트랜잭션에 의해 변경 후 커밋된 데이터를 변경할 수 없다.

 

S1

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE;

 

S2

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

COMMIT;

 

S1

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

 

ORA-08177: 이 트랜잭션에 대한 직렬화 액세스를 할 수 없습니다.

 

ROLLBACK;

 

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

 

데이터 동시성(data concurrency)은 다수의 사용자가 동일한 데이터에 동시에 접근할 수 있는 것을 말한다.

 

[락킹 메커니즘]

오라클 데이터베이스는 데이터 동시성을 보장하기 위해 락(lock)을 사용한다. 락은 자원의 사용을 직렬화하기 위한 방법 중 하나다.

(오라클 데이터베이스는 락 외에도 래치(latch)와 뮤텍스(mutex) 등을 사용하여 자원의 사용을 직렬화한다.)

 

오라클 데이터베이스는 다양한 락을 사용한다.

V$LOCK_TYPE 뷰에서 락의 종류를 조회할 수 있다.

DML 문은 TM 락과 TX 락을 사용한다.

TM 락 (table lock)은 테이블, TX 락(row lock)은 트랜잭션에 설정되는 락이다.

TX 락은 로우 레벨 락(low level lock)과 연결된다.

(로우 레벨 락은 데이터 블록의 로우에 설정된다. 하나의 TX 락이 다수의 로우 레벨 락과 연결되는 구조다.)

 

락은 종류에 따라 아래의 모드로 설정될 수 있다.

Y로 표시된 항목은 자원을 동시에 사용할 수 있는 조합이다.

DML 문은 TM 락을 RX 모드, TX 락을 X 모드로 설정한다.

 

RS - RX - S - SRX - X

RS (Row Share) Y - Y - Y - Y

S (Share) Y - Y

SRX (Share Row eXclusive) Y

X (eXclusive)

 

2개의 세션에서 아래의 쿼리를 순서대로 수행해보자.

S1 세션의 SID는 100, S2 세션의 SID는 200이다.

S1 세션은 CD가 1인 행, S2 세션은 CD가 2인 행을 갱신한다.

 

S1 (100)

SELECT USERENV ('SID') FROM DUAL; -- 100

 

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

 

 

S2 (200)

SELECT USERENV ('SID') FROM DUAL -- 200

 

UPDATE T1 SET VL = VL - 10 WHERE CD = 2;

 

DBA_LOCK 뷰 또는 V$LOCK 뷰에서 현재 설정된 락에 대한 정보를 조회할 수 있다.

또 다른 세션에서 DBA_LOCK 뷰를 조회해보자.

아래 쿼리는 S1 세션(SID = 100)의 락 정보를 조회한다.

TM 락은 RX 모드 ,TX 락은 X 모드로 설정되어 있다.

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOCKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID = 100

AND LOCK_TYPE IN ('DML', 'Transaction')

 

아래 쿼리는 S2 세션(SID = 200)의 락 정보를 조회한다.

TM 락은 RX 모드, TX 락은 X 모드로 설정되어 있다.

TM 락이 동일한 테이블(T1)에 설정되었지만 RX 모드는 서로 호환되기 때문에 블로킹되지 않고 락을 획득할 수 있다.

TX 락의 X 모드는 서로 호환되지 않지만 다른 행을 갱신했기 때문에 블로킹되지 않는다.

(갱신되는 행에 로우 레벨 락을 설정하고, 로우 레벨 락을 X 모드의 TX 락에 연결한다.)

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOCKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID = 200

AND LOCK_TYPE IN ('DML', 'TRANSACTION');

 

-- 다음 예제를 위해 두 세션에서 롤백을 수행

 

S1 (100) - S2 (200)

ROLLBACK; - ROLLBACK;

 

롤백을 수행하면 트랜잭션이 종료되고, 트랜잭션이 종료되면 락도 해제된다.

DBA_LOCK 뷰를 다시 조회하면 락이 해제된 것을 확인할 수 있다.

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOCKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID IN (100, 200)

AND LOCK_TYPE IN ('DML', 'TRANSACTION');

 

-- 아래의 예제를 순서대로 수행해보자. 동일한 행(CD = 1)을 갱신했기 때문에 S2 세션은 S1 세션의 락이 해제될 때까지 블로킹(BLOCKING) 된다.

 

S1 (100) - S2 (200)

UPDATE T1 SET VL = VL + 10 WHERE CD = 1; UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

-- 블로킹

 

 

-- 아래 쿼리는 S1 세션(SID = 100)의 락 정보를 조회한다.

BLOCKING_OTHERS 열에서 TX 락이 다른 세션을 블로킹하고 있는 것을 확인할 수 있다.

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID = 100

AND LOCK_TYPE IN ('DML', 'TRANSACTION');

 

-- 아래 쿼리는 S2 세션 (SID = 200)의 락 정보를 조회한다. TM 락은 S1 세션의 RX 모드와 호환되기 때문에 블로킹되지 않고 락을 획득할 수 있다. TX 락은 동일한 행을 갱신했기 때문에 블로킹 된 상태로 X 모드를 요청하고 있다.

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOCKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID = 200

AND LOCK_TYPE IN ('DML', 'TRANSACTION');

 

다음과 같은 과정으로 S2 세션이 블로킹된다.

(실제 수행 과정은 훨씬 더 복잡하다.)

 

순서 - 동작

1 - 로우 레벨 락을 설정하기 위해 블록을 방문하여 로우 헤더(C1 = 1)를 확인

2 - 로우 레벨 락이 설정되어 있는 것을 확인

3 - 로우 레벨 락과 연결된 S1 세션의 TX 락이 X 모드인 것을 확인

4 - 호환되지 않은 모드이므로 대기 목록에 트랜잭션을 등록하고 대기(블로킹)

 

또 다른 세션에서 아래 쿼리를 수행하면 에러가 발생한다. DROP TABLE 문은 DDL 문이다.

DDL 문은 TX 락을 X 모드로 설정한다. RX 모드와 X 모드는 호환되지 않기 때문에 DML 문이 수행 중인 오브젝트에 DDL 문을 수행하면 에러가 발생한다.

(DDL 문이 수행중인 오브젝트에 DML 문을 수행해도 에러가 발생한다. DDL 문이 장시간 수행되면 DML 문이 수행될 수 없기 때문에 장애가 발생할 수 있다.)

 

DROP TABLE T1;

 

ORA-00054: 리소스가 사용 중이어서 NOWAIT가 지정되었거나 시간 초과가 만료된 상태로 획득한다.

 

S1 세션에서 롤백을 수행하면 S2 세션의 블로킹이 해제된다.

다음 예제를 위해 S2 세션에서도 롤백을 수행하자.

(롤백을 수행할 수 없는 경우 블로킹하고 있는 세션을 강제 종료시켜야 한다.)

 

S1 (100) - S2 (200)

ROLLBACK; - ROLLBACK;

 

[동시성 제어]

MULTITIER 환경에서는 개발자가 직접 데이터 동시성을 제어해야 한다.

 

두 세션에서 아래 쿼리를 순서대로 실행해보자.

S2 세션의 갱신 결과가 유실된 것을 확인할 수 있다.

이런 현상을 LOST UPDATE라고 한다.

동시성 제어를 통해 LOST UPDATE를 방지할 수 있다.

 

S1

COLUMN VL NEW_VALUE V_VL

 

SELECT VL FROM T1 WHERE CD = 1;

 

S2

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

COMMIT;

 

S1

UPDATE T1

SET VL = &V_VL + 10

WHERE CD = 1;

 

SELECT VL FROM T1 WHERE CD = 1;

 

-- 다음 예제를 위해 아래와 같이 데이터를 원상 복구하자.

UPDATE T1 SET VL = 40 WHERE CD = 1;

COMMIT;

 

두 가지 방식의 동시성 제어를 사용할 수 있다.

비관적 동시성 제어와 낙관적 동시성 제어를 차례대로 살펴보자.

 

[비관적 동시성 제어]

비관적 동시성 제어(pessimistic concurrency control 또는 pessimistic locking)은 다수의 사용자가 동일한 데이터를 동시에 갱신하는 일이 빈번하다고 가정한다.

 

[FOR UPDATE 절]

비관적 동시성 제어는 FOR UPDATE 절을 사용한다.

SELECT 문에 FOR UPDATE 절을 기술하면 조회한 행에 로우 레벨 락이 설정된다.

 

FOR UPDATE [OF [{table | view}.] column [, [{table | view}.]column]...]

[{NOWAIT | WAIT integer | SKIP LOCKED}]

 

S1 세션에서 FOR UPDATE 절로 CD가 1인 행에 로우 레벨 락을 설정하고, S2 세션에서도 동일한 쿼리를 수행해보자. S2 세션은 S1 세션이 락이 해제될 때까지 블로킹된다. 갱신할 행에 로우 레벨 락을 설정하고, 갱신할 값을 계산하는 작업을 수행한 후, 행을 갱신하는 업무에서 활용할 수 있다.

 

S1

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE; -- 작업

 

S2

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE; -- 블로킹

 

S1

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

COMMIT;

 

S2

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

COMMIT;

 

-- 아래 쿼리는 S2 세션에서 FOR UPDATE 절에 NOWAIT를 지정했기 때문에 즉시 에러가 발생한다.

 

S1

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE;

 

S2

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE

NOWAIT;

 

S1

COMMIT;

 

S2

ORA-00054: 리소스가 사용 중이어서 NOWAIT이 지정되었거나 시간 초과가 만료된 상태로 획득합니다.

 

-- 아래 쿼리는 S2 세션에서 FOR UPDATE 절에 WAIT 10으로 지정했기 때문에 10초 후 에러가 발생한다.

블로킹이 발생하면 동시성이 저하될 수 있다. FOR UPDATE 절에 NOWAIT이나 WAIT integer를 지정하여 에러를 발생시킨 후 에러에 대한 예외 처리를 통해 동시성을 향상시킬 수 있다.

 

S1

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE;

 

S2

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE

WAIT 10;

 

S1

COMMIT;

 

S2

ORA-30006: 리소스 사용 중. WAIT 시간 초과로 획득이 만료됨

 

FOR UPDATE 절에 SKIP LOCKED를 지정하면 락이 설정된 행을 조회하지 않는다.

아래 예제의 S2 세션에 락이 설정되지 않은 C1이 2인 행만 조회된다.

SKIP LOCKED는 동시성 제어보다 다중 큐(multiconsumer queue)를 구현할 때 사용되는 기능이다.

 

S1

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE;

 

S2

SELECT * FROM T1 FOR UPDATE SKIP LOCKED;

 

S1, S2

COMMIT;

 

-- 아래 쿼리는 조인이 포함된 SELECT 문에 FOR UPDATE 절을 사용했다.

FOR UPDATE 절에 열을 지정하면 열을 지정한 테이블의 행에만 락이 설정된다.

 

S1

SELECT *

FROM EMP A, DEPT B

WHERE B.DEPTNO = A.DEPTNO

FOR UPDATE OF A.DEPTNO;

 

S2

SELECT * FROM DEPT FOR UPDATE NOWAIT;

 

SELECT * FROM EMP FOR UPDATE NOWAIT;

 

ORA-00054: 리소스가 사용 중이어서 NOWAIT이 지정되었거나 시간 초과가 만료된 상태로 획득합니다.

 

S1, S2

COMMIT;

 

[선분 이력 변경]

선분 이력 변경은 기존 이력을 단절(update)하고, 신규 이력을 생성(insert)하는 두 가지 작업을 하나의 트랜잭션으로 수행해야 한다. FOR UPDATE 절로 갱신은 방지할 수 있지만, 삽입은 방지할 수 없다.

이런 경우 FOR UPDATE 절로 부모 테이블에 락을 설정하는 기법을 사용할 수 있다.

 

-- 예제를 위해 아래와 같이 테이블을 생성

-- TP 테이블이 부모 테이블, TC 테이블이 선분 이력을 관리하는 자식 테이블.

-- TC 테이블의 PK는 코드(CD), 시작일자(BD)다.

 

DROP TABLE TP PURGE;

DROP TABLE TC PURGE;

 

CREATE TABLE TP (CD VARCHAR2(1), CONSTRAINT TP_PK PRIMARY KEY (CD));

CREATE TABLE TC (CD VARCHAR2(1) -- 코드

, BD DATE -- 시작일자

, ED DATE -- 종료일자

, VL NUMBER -- 값

, CONSTRAINT TC_PK PRIMARY KEY (CD, BD)

, CONSTRAINT TC_F1 FOREIGN KEY (CD) REFERENCES TP (CD));

 

INSERT INTO TP VALUES ('A');

INSERT INTO TC VALUES ('A', DATE '2050-01-01', DATE '9999-12-31', 1);

 

-- 아래 방식으로 선분 이력을 갱신할 수 있다.

-- 자식 테이블(TC)을 갱신하기 전에 부모 테이블(TP)에 락을 설정했기 때문에 데이터 동시성을 확보할 수 있다.

다른 세션에서 동시에 CD가 A인 선분 이력을 변경하면 부모 테이블(TP)에 락을 설정하는 단계에서 블로킹된다.

 

SELECT * FROM TP WHERE CD = 'A' FOR UPDATE;

 

UPDATE TC -- 기존 이력 단절

SET ED = DATE '2050-02-01' - 1

WHERE CD = 'A'

AND ED = DATE '9999-12-31';

 

INSERT

INTO TC -- 신규 이력 생성

VALUES ('A', DATE '2050-02-01', DATE '9999-12-31', 2);

 

COMMIT;

 

아래는 TC 테이블을 조회한 결과다.

 

SELECT * FROM TC ORDER BY 2;

 

TC 테이블의 PK를 코드(CD), 최종일자(ED)로 변경해보자.

성능상의 이유로 PK에 최종일자 속성을 사용하기도 한다.

 

ALTER TABLE TC DROP CONSTRAINT TC_PK;

ALTER TABLE TC ADD CONSTRAINT TC_PK PRIMARY KEY (CD, ED);

 

아래 방식으로 선분 이력을 갱신할 수 있다.

기본 식별자가 변경되지 않아야 한다는 원칙을 준수하기 위해 다소 복잡한 방식으로 처리해야 한다.

 

SELECT * FROM TP WHERE CD = 'A' FOR UPDATE;

 

INSERT

INTO TC -- 기존 이력 단절

SELECT CD, BD, DATE '2050-03-01' - 1 AS ED, VL

FROM TC

WHERE CD = 'A'

AND ED = DATE '9999-12-31';

 

UPDATE TC -- 신규 이력 생성

SET BD = DATE '2050-03-01', VL = 3

WHERE CD = 'A'

AND ED = DATE '9999-12-31';

 

아래는 TC 테이블을 조회한 결과

 

SELECT * FROM TC ORDER BY 2;

 

[낙관적 동시성 제어]

낙관적 동시성 제어(Optimistic Concurrency Control 또는 optimistic locking)은 다수의 사용자가 동일한 데이터를 동시에 갱신하는 일이 드물다고 가정한다.

 

[컬럼 확인 방식]

칼럼 확인 방식은 변경할 값을 조회하여 저장하고, 값을 변경하기 전에 저장한 값의 변경 여부를 확인하는 방식이다. 변경할 값이 많은 경우 모든 값을 비교해야 하기 때문에 쿼리가 길어질 수 있다.

 

아래 쿼리는 컬럼 확인 방식으로 낙관적 동시성 제어를 구현했다.

S1 세션에서 값을 조회한 후 S2 세션에서 값을 변경했기 때문에 S1 세션이 UPDATE 문에서 행이 갱신되지 않았다.

 

S1

COLUMN VL NEW_VALUE V_VL

 

SELECT VL FROM T1 WHERE CD = 1;

 

S2

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

COMMIT;

 

S1

UPDATE T1

SET VL = VL + 10

WHERE CD = 1

AND VL = &V_VL;

 

[버전 확인 방식]

버전 확인 방식은 버전 정보를 변수에 저장하고, 값을 변경하기 전에 저장한 버전 정보의 변경 여부를 확인하는 방식이다. 버전 정보만 확인하면 되기 때문에 쿼리를 간결하게 작성할 수 있는 장점이 있지만, 추가적인 버전 속성을 생성해야 하는 단점도 있다.

 

-- 예제를 위해 아래와 같이 열을 추가하자. 버전 정보는 주로 시스템 속성으로 관리된다.

ALTER TABLE T1 ADD MD DATE DEFAULT DATE '2050-01-01' NOT NULL;

 

-- 아래 쿼리는 버전 확인 방식으로 낙관적 동시성 제어를 구현한다. 갱신할 열이 많더라도 버전 정보가 저장된 MD 열만 비교하면 된다.

 

S1

COLUMN MD NEW_VALUE MD

 

SELECT MD FROM T1 WHERE CD = 1;

 

S2

UPDATE T1

SET VL = VL - 10, MD = SYSDATE

WHERE CD = 1;

 

COMMIT;

 

S1

UPDATE T1

SET VL = VL - 10

WHERE CD = 1

AND MD = TO_DATE ('&md', 'YYYY-MM-DD HH24:MI:SS');

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

 

오라클 데이터베이스는 COMMIT 문, ROLLBACK 문, SAVEPOINT 문, SET TRANSACTION 문, SET CONSTRAINT 문 등의 TCS 문을 제공한다.

 

[COMMIT 문]

COMMIT 문은 현재 트랜잭션의 변경 내용을 데이터베이스에 영구적으로 저장하고 트랜잭션이 종료한다.

(COMMIT 문의 옵션은 대부분 성능과 관련된 기능이다.)

 

COMMIT [WORK] [[COMMENT string] | [WRITE [WAIT | NOWAIT] [IMMEDIATE | BATCH]]

| FORCE string [, integer]];

 

-- 아래 쿼리는 DELETE 문을 수행한 후 COMMIT 문을 수행했다.

-- CD가 3인 행이 영구적으로 삭제되었다.

 

DELETE FROM T1 WHERE CD = 3;

COMMIT;

 

아래는 잘못된 계좌이체 트랜잭션의 예시다.

1번 계좌의 잔고에서 10을 빼고 COMMIT 문을 수행했기 때문에 하나의 트랜잭션으로 처리되지 않았다.

트랜잭션의 원자성이 보장되지 않기 때문에 데이터 일관성이 위배될 수 있다.

 

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

COMMIT;

 

UPDATE T1 SET VL = VL + 10 WHERE CD = 2;

 

명령 프롬프트 창을 강제 종료한 후 다시 접속해보자.

(EXIT 명령어가 아니라 명령 프롬프트 창의 X 버튼을 눌러 종료시켜야 한다.)

T1 테이블을 조회해보면 2번 계좌의 잔고에 10을 더한 작업이 롤백된 것을 확인할 수 있다.

잔고의 합계가 90이 되어 데이터 일관성이 위배되었다.

 

SELECT * FROM T1;

 

-- 다음 예제를 위해 데이터 원상 복구

UPDATE T1 SET VL = 50 WHERE CD = 1;

COMMIT;

 

-- 아래 예제는 2개의 UPDATE 문을 하나의 트랜잭션으로 처리

-- COMMIT 문으로 트랜잭션을 종료했기 때문에 트랜잭션에 의한 변경 내용이 영구적으로 저장된다.

 

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

UPDATE T1 SET VL = VL + 10 WHERE CD = 2;

COMMIT;

 

-- 명령 프롬프트 창을 강제 종료한 후 다시 접속해보자.

-- T1 테이블을 조회해보면 트랜잭션에 의한 변경 내용이 저장된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

[ROLLBACK 문]

ROLLBACK 문은 현재 트랜잭션의 변경 내용을 모두 취소하고 트랜잭션을 종료한다.

 

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint | FORCE string];

 

-- 아래와 같이 T1 테이블의 전체 행을 삭제해보자.

DELETE FROM T1;

 

-- T1 테이블을 조회해보면 행이 존재하지 않는 것을 확인할 수 있다.

SELECT * FROM T1;

 

아래와 같이 롤백을 수행해보자.

ROLLBACK;

 

T1 테이블을 다시 조회해보면 변경 사항이 모두 롤백된 것을 확인할 수 있다.

ROLLBACK 문은 언두 세그먼트에 저장된 변경 이전 데이터를 통해 데이터를 복구한다.

 

SELECT * FROM T1;

 

-- 아래 예제는 2개의 UPDATE 문을 하나의 트랜잭션으로 처리했다.

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

UPDATE T1 SET VL = VL + 10 WHERE CD = 2;

 

명령 프롬프트 창을 강제 종료한 후 다시 접속해보자.

T1 테이블을 조회해보면 변경 사항이 모두 롤백된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

[SAVEPOINT 문]

SAVEPOINT 문은 롤백할 수 있는 저장점을 생성한다.

 

SAVEPOINT savepoint;

 

-- 아래 쿼리는 SAVEPOINT 문으로 S1, S2 저장점을 생성한 후 DELETE 문으로 전체 행을 삭제했다.

 

UPDATE T1 SET VL = VL - 10 WHERE CD = 2;

 

SAVEPOINT S1;

 

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

 

SAVEPOINT S2;

 

DELETE FROM T1;

 

SELECT * FROM T1;

 

-- 아래 쿼리는 S2 저장점으로 트랜잭션을 롤백한다.

T1 테이블을 조회해보면 두 번째 UPDATE 문이 적용된 시점으로 데이터가 롤백된 것을 확인할 수 있다.

 

ROLLBACK TO SAVEPOINT S2;

 

-- 아래 쿼리는 S1 저장점으로 트랜잭션을 롤백한다.

T1 테이블을 조회해보면 첫 번째 UPDATE 문이 적용된 시점으로 데이터가 롤백된 것을 확인할 수 있다.

 

RLLBACK TO SAVEPOINT S1;

 

SELECT * FROM T1;

 

아래 쿼리는 에러가 발생한다.

특정 저장점(S1)으로 롤백하면 해당 저장점 이후에 생성된 모든 저장점(S2)이 제거되기 때문이다.

 

ROLLBACK TO SAVEPOINT S2;

 

ORA-01086: 'S2' 저장점이 이 세션에 설정되지 않았거나 부적합합니다.

 

-- 전체 트랜잭션 롤백. 데이터 원상 복구

ROLLBACK;

SELECT * FROM T1;

 

-- 아래 예제의 두 번째 UPDATE 문은 NUMBER 타입인 VL 열에 문자 값을 입력했기 때문에 오류가 발생했다.

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

 

UPDATE T1 SET VL = 'A' WHERE CD = 1;

 

ORA-01722: 수치가 부적합합니다.

 

T1 테이블을 조회해보면 첫 번째 UPDATE 문의 변경 내용이 롤백되지 않은 것을 확인할 수 있다.

오라클 데이터베이슨느 DML 문이 수행될 때마다 내부적으로 저장점을 생성하고, 에러가 발생하면 직전의 저장점으로 문장 단위 롤백(statement-level rollback)을 수행한다.

(이런 동작을 문장 수준 원자성(statement-level atomicity)이라고 한다.

 

SELECT * FROM T1;

 

다음 예제를 위해 롤백을 수행

ROLLBACK;

 

[암시적 커밋]

DDL 문은 수행 전에 트랜잭션을 커밋하고 종료 후 다시 트랜잭션을 커밋한다.

이런 동작을 암시적 커밋(implicit commit)이라고 한다.

 

-- DDL 문의 동작에 대한 슈도 코드다.

BEGIN

COMMIT;

DDL 문;

COMMIT;

EXCEPTION

WHEN OTHERS THEN ROLLBACK; RAISE;

END;

 

-- 예제 테이블 생성

DROP TABLE T2 PURGE;

CREATE TABL2 T2 (C1 NUMBER);

 

-- 아래와 같이 INSERT 문을 수행하고, DDL 문인 CREATE TABLE 문을 수행한 후, 롤백을 수행하자.

INSERT INTO T2 VALUES (1);

 

CREATE TABLE T2 (C1 NUMBER);

 

ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.

 

ROLLBACK;

 

T2 테이블을 조회해보면 데이터가 롤백되지 않은 것을 확인할 수 있다.

CREATE TABLE 문에 의해 트랜잭션이 커밋되기 때문에 INSERT 문에 의한 변경이 롤백되지 않은 것이다.

 

SELECT * FROM T2;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

 

TCS는 Transaction Control Statement의 약자다.

트랜잭션 제어문으로 해석할 수 있다.

트랜잭션과 TCS 문의 기본 문법을 살펴본 후, 트랜잭션과 관련된 데이터 동시성, 데이터 일관성에 대해 살펴보자.

 

-- 예제 테이블 생성

DROP TABLE T1 PURGE;

CREATE TABLE T1 (CD NUMBER, VL NUMBER) ROWDEPENDENCIES;

 

INSERT INTO T1 VALUES (1, 50);

INSERT INTO T1 VALUES (2, 50);

COMMIT;

 

[ROWDEPENDENCIES 절]

테이블 생성 시 ROWDEPENDENCIES 절을 기술하면 row-level dependency tracking 기능이 활성화된다.

해당 기능을 활성화하면 SCN이 행 수준으로 저장되며 행의 길이가 6바이트씩 증가한다.

기본값은 NOROWDEPENDENCIES로 SCN이 블록 수준으로 저장된다.

 

[트랜잭션]

트랜잭션(transaction)은 함께 수행해야 하는 작업의 논리적인 단위다.

계좌이체 트랜잭션은 출금 계좌의 잔고를 차감하는 작업과 입금 계좌의 잔고를 증가시키는 작업으로 구성된다.

두 작업은 반드시 하나의 트랜잭션으로 수행되어야 한다.

 

-- 아래 쿼리는 1번 계좌에서 2번 계좌로 10원을 이체하는 트랜잭션을 수행한다.

1번 계좌의 잔고에서 10원을 빼는 쿼리와 2번 계좌의 잔고에 10원을 더하는 쿼리가 하나의 트랜잭션으로 수행되었다.

두 쿼리가 하나의 트랜잭션으로 수행되지 않으면 잔고의 합계가 달라질 수 있다.

 

UPDATE 계좌 SET 잔고 = 잔고 - 10 WHERE 계좌번호 = 1;

UPDATE 계좌 SET 잔고 = 잔고 + 10 WHERE 계좌번호 = 2;

COMMIT;

 

[구조]

트랜잭션은 DML 문이나 SET TRANSACTION 문이 실행되면 시작되고, COMMIT 문이나 ROLLBACK 문이 실행되면 종료된다. 트랜잭션이 시작되면 내부적으로 언두 세그먼트 (undo segment)가 할당되고,

(언두 세그먼트는 변경 전의 데이터가 저장되는 테이블이다. 트랜잭션 롤백, 읽기 일관성, 트랜잭션 복구 등을 위해 사용된다.)

트랜잭션에 트랜잭션 ID(XID)가 부여된다. 트랜잭션 ID는 언두 세그먼트의 번호, 슬롯, 시퀀스의 조합으로 생성된다.

 

V$TRANSACTION 뷰에서 트랜잭션에 대한 정보를 조회할 수 있다.

아래 쿼리는 결과가 반환되지 않는다.

수행 중인 트랜잭션이 존재하지 않기 때문이다.

 

SELECT * FROM V$TRANSACTION;

 

아래와 같이 INSERT 문을 수행해보자.

INSERT INTO T1 VALUES (3, 50);

 

V$TRANSACTION 뷰를 다시 조회해보면 트랜잭션이 시작된 것을 확인할 수 있다.

START_SCN 열은 트랜잭션이 시작된 SCN을 반환한다.

 

SELECT XID, XIDUSN, XIDSLOT, XIDSQN, START_DATE, START_SCN FROM V$TRANSACTION;

 

COMMIT 문을 수행하면 트랜잭션이 종료된다.

COMMIT;

 

V$TRANSACTION 뷰를 다시 조회하면 결과가 반환되지 않는다.

SELECT * FROM V$TRANSACTION;

 

[SCN]

SCN(System Change Number)은 오라클 데이터베이스의 논리적 TIMESTAMP다. 데이터베이스 내부의 작업 순서를 식별하는 용도로 사용된다. 트랜잭션도 내부적으로 SCN을 사용한다.

 

ORA_ROWSCN 슈도 칼럼은 행의 SCN을 반환한다.

CD가 3인 행의 ORA_ROWSCN 값이 앞서 살펴본 V$TRANSACTION 뷰의 START_SCN 값과 동일한 것을 확인할 수 있다.

SCN_TO_TIMESTAMP 함수를 사용하면 SCN 값을 TIMESTAMP 값으로 변환할 수 있다.

 

SELECT CD, VL, ORA_ROWSCN, SCN_TO_TIMESTAMP (ORA_ROWSCN) AS C1 FROM T1;

현재 SCN은 V$DATABASE 뷰의 checkpoint_change# 열에서 확인할 수 있다.

 

SELECT checkpoint_change# FROM V$DATABASE;

 

[ORA-08181 에러]

SCN_TO_TIMESTAMP 함수는 SYS.SMON_SCN_TIME 테이블을 참조한다.

해당 테이블은 데이터베이스가 시작된 시간으로부터 최대 120시간(5일) 동안의 SCN을 저장한다.

(버전에 따라 저장되는 행의 개수가 상이할 수 있다.)

5일 이전의 SCN에 SCN_TO_TIMESTAMP 함수를 사용하면 "ORA-08181: 지정된 번호는 적합한 시스템 변경 번호가 아님" 에러가 발생할 수 있다.

 

아래는 SYS.SMON_SCN_TIME 테이블을 조회한 결과다.

 

SELEC TIME_DP, SCN_BAS FROM SYS.SMON_SCN_TIME ORDER BY SCN_BAS DESC;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

 

DML 문에서 에러가 발생하면 해당 DML 문에 의한 변경 사항이 모두 롤백된다.

99행까지 변경한 후 마지막 1행에서 에러가 발생하면 99행이 모두 롤백되는 것이다.

이런 상황을 피하기 위해 DML 에러 로깅(DML error logging) 기능을 사용할 수 있다.

DML 에러 로깅 기능은 10.2 버전부터 사용할 수 있다.

 

DML 에러 로깅 구문은 아래와 같다.

DML 에러 로깅 기능은 DML 수행 시 에러가 발생하면 에러를 로그 테이블에 기록한 후,

다음 행에 대한 DML을 계속 진행한다.

 

LOG ERRORS [INTO [schema.] table] [(simple_expression)]

[REJECT LIMIT {integer | UNLIMITED}]

 

항목 - 설명

INTO - 에러 로깅 테이블을 지정

simple_expression - 에러 태그로 사용할 값을 지정

REJECT LIMIT - integer로 에러의 한계 값을 지정 (기본값은 0 또는 UNLIMITED)

 

-- 예제 테이블 생성

-- t1 테이블의 PK가 empno이므로 중복된 empno를 삽입하면 에러가 발생한다.

 

DROP TABLE T1 PURGE;

 

CREATE TABLE T1 (EMPNO NUMBER(4), ENAME VARCHAR2(10), SAL NUMBER(7, 2)

, CONSTRAINT T1_PK PRIMARY KEY (EMPNO));

 

INSERT INTO T1 VALUES (7782, 'CLARK', 2450);

COMMIT;

 

DBMS_ERRLOG, CREATE_ERROR_LOG 프로시저로 로그 테이블을 생성할 수 있다.

아래 코드는 E1 테이블을 T1 테이블의 로그 테이블로 생성한다.

 

BEGIN

DBMS_ERRLOG.CREATE_ERROR_LOG (DML_TABLE_NAME => 'T1'

, ERR_LOG_TABLE_NAME => 'E1');

END;

 

-- 생성된 E1 테이블의 구조

DESC E1;

 

-- 아래 쿼리는 모두 에러가 발생한다.

-- 첫 번째 쿼리는 5자리 정수부를 저장할 수 있는 SAL에 100000을 삽입했기 때문에 에러가 발생했고,

(NUMBER(7,2)로 생성된 열은 정수부 5자리, 소수부 2자리의 숫자 값을 저장할 수 있다.)

두 번째 쿼리는 T1 테이블에 존재하고 있는 EMPNO(7782)를 삽입했기 때문에 에러가 발생했다.

세 번재 쿼리도 두 번째 쿼리와 동일한 원인으로 에러가 발생했다.

 

INSERT INTO T1 VALUES (7839, 'KING', 100000);

 

ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.

 

INSERT INTO T1 VALUES (7782, 'CLARK', 2450);

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_PK)에 위배됩니다

 

INSERT INTO T1 SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_PK)에 위배됩니다.

 

-- 아래와 같이 에러 로깅 절을 기술하면 에러가 발생하지 않는다. 세 번째 쿼리는 3행 중 EMPNO가 중복된 1행은 무시되고, 중복되지 않은 2행은 정상적으로 삽입된다.

 

INSERT INTO T1 VALUES (7839, 'KING', 100000)

LOG ERRORS INTO E1 ('1') REJECT LIMIT UNLIMITED;

 

INSERT INTO T1 VALUES (7782, 'CLARK', 2450)

LOG ERRORS INTO E1 ('2') REJECT LIMIT UNLIMTED;

 

INSERT INTO T1 SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10

LOG ERRORS INTO E1 ('3') REJECT LIMIT UNLIMTED;

 

-- 아래는 T1 테이블을 조회한 결과

-- KING과 MILLIER가 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

E1 테이블에서 에러에 대한 정보를 확인할 수 있다.

 

SELECT ORA_ERR_MESG$, ORA_ERR_OPTYP$, ORA_ERR_TAG$, EMPNO, SAL FROM E1;

 

-- 로그 테이블은 아래와 같이 직접 삭제해야 한다.

DROP TABLE E1 PURGE;

 

[IGNORE_ROW_ON_DUPKEY_INDEX 힌트]

IGNORE_ROW_ON_DUPKEY_INDEX 힌트를 사용하면 PK 제약 조건이나 UNIQUE 제약 조건에 위배되는 행을 무시할 수 있다.

 

-- 예제 테이블 생성

DROP TABLE T1 PURGE;

CREATE TABLE T1 (C1 NUMBER, CONSTRAINT T1_U1 UNIQUE (C1));

 

INSERT INTO T1 VALUES (1);

COMMIT;

 

-- 아래 쿼리는 T1 테이블의 C1 열에 중복 값인 1인 행을 삽입했기 때문에 에러가 발생한다.

INSERT INTO T1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 2;

 

ORA-00001: 무결성 제약 조건(SCOTT.T1_U1)에 위배됩니다.

 

아래와 같이 IGNORE_ROW_O_DUPKEY_INDEX 힌트를 사용하면 에러가 발생하지 않는다.

DML 에러 로깅과 유사하게 동작한다.

 

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (T1 T1_U1) */

INTO T1

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 2;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

+ Recent posts