출처 : http://majesty76.tistory.com/64

redo log file이 3개 있다. 이후 강제 log switch가 일어나거나, 혹은 자동으로 log switch가 일어나 redo log file이 3번까지 가득 찼다. 그러면 다음 log는 다시 첫번째 redo log file에 덮어 씌워질 것이고, 그렇게 되면 처음 기록된 log는 나중에 기록된 log에 의해 삭제 될 것이다. 이런 경우 datafile을 백업 받고, 언젠가 datafile이 손상 되었을 때, 백업 datafile을 이용 하려는 경우 SCN이 일치하지 않아  redo log file에 모든 로그 기록을 보고 SCN을 일치 시켜야 하는데, 앞서 기록된 log가 덮어 쓴 log로 인해 삭제 되어서 그때의 SCN번호를 매길 때 어떤 작업이 일어났는지 알 수 없기 때문에 database를 올릴 수 없게 된다. (no archive mode)

 이와 같은 현상을 방지 하기 위하여 등장 한 것이 Archive file(아카이브 파일)이다. archive file은 redo log file이 가득 찼을 때, 처음 file에 덮어 쓰기 전에 archiver 백그라운드 프로세스를 통해 처음 redo log file을 archive file에 저장한다. 이로 인해 log 기록들이 저장되어 복구를 손실 없이 좀더 안전하게 할 수 있는 것이다.

그렇다고 꼭 장점만 있는 것은 아니다. archive file은 용량을 많이 차지 할 뿐더러, 만약 archive 저장 디렉토리가 가득 찼는데(사실 금방 찬다고 한다) redo log file을 archive에 내려 써야 할 차례가 오면, archive file에 빈 공간이 생길 때까지 archiver 백그라운드 프로세스는 동작하지 않는다. 그러면 LGWR 백그라운드 프로세스 역시 동작하지 않을 것이고, 결국 이렇게 되면 DB가 죽어버리는 현상이 나타난다(archive hang 현상).
혹은 archive 지정 디렉토리가 oracle을 설치한 계정인지도 살펴봐야 한다. 만약 root로 되어 있다면 archiver가 저장을 하지 못해 DB가 죽는 경우도 생길 수 있다.


조심해야 할 점을 기억하며 DB를 archive mode로 바꾸어 보자.(필자는 pfile로 작업할 것이다)

우선 DB를 내린 후 터미널 창이 나오게 한다.
SQL> shutdown immediate
SQL> exit


archive file이 들어갈 디렉토리 2개를 만들어 준다.
oracle]$ mkdir -p /app/archdata/arch01
oracle]$ mkdir -p /app/archdata/arch02


pfile(inittestdb.ora)를 수정하기 위해 pfile이 있는 디렉토리로 가서 init[instance명].ora 파일을 vi editor로 연다.
oracle]$ cd /app/product/10g/dbs
oracle]$ vi inittestdb.ora



pfile(inittestdb.ora) 맨 아래부분에 추가해 준다. (파일 형태가 %s_%t_%r.arc로 만들어 질 것이다.)
*.log_archive_dest_1='location=/app/archdata/arch01'
*.log_archive_dest_2='location=/app/archdata/arch02'
log_archive_format=%s_%t_%r.arc



다시 sys 계정으로 접속하여 no archive mode인지 확인 해 보자.
SQL> startup mount
SQL> archive log list (no archive mode 확인)



archive mode로 바꾸어 주자. 그리고 DB를 open한다.
SQL> alter database archivelog;
SQL> alter database open;



archive mode로 바뀌었는지 확인 한다.
SQL>archive log list



redo log file의 log들이 archive file 에 모두 내려가도록 log switch를 여러번 수행한다. 
SQL> alter system switch logfile; (여러번 수행)



log switch로 인해 archive file이 생성 되었는지 확인 한다. (터미널 창)
oracle]$ ls /app/archdata/arch02



다시 no archive 상태로 바꿔주자.

SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;



no archive mode로 변경 되었는지 확인 해 보자.

SQL>archive log list




Copyright 김도익 All rights reserved.


테이블 이력(관련 테이블) 저장시 일련번호(Sequence) Trigger로 부여하기

 

샘플

CREATE TABLE TB_TEST1 (
    NUM NUMBER PRIMARY KEY,
    NM VARCHAR2(100)
);

CREATE TABLE TB_TEST2 (
    NUM NUMBER,
    IDX NUMBER DEFAULT NULL,
    NM VARCHAR2(100),
    CONSTRAINT PK_TB_TEST2 PRIMARY KEY (NUM, IDX),
    CONSTRAINT FK_TB_TEST1_TB_TEST2_01 FOREIGN KEY(NUM) REFERENCES TB_TEST1(NUM) ON DELETE CASCADE
);

CREATE SEQUENCE SQ_TB_TEST1
START WITH 1
INCREMENT BY 1
NOCYCLE;

CREATE OR REPLACE TRIGGER TG_TEST1
BEFORE INSERT OR UPDATE OF NM ON TB_TEST1
FOR EACH ROW
BEGIN
    INSERT INTO TB_TEST2(NUM, NM) VALUES (:NEW.NUM, :NEW.NM);
END;
/

CREATE OR REPLACE TRIGGER TB_TEST2
AFTER INSERT ON TB_TEST2
FOR EACH ROW
WHEN (NEW.IDX IS NULL)
BEGIN
     SELECT SQ_TB_TEST1.NEXTVAL INTO :NEW.IDX FROM DUAL;
END;
/

INSERT INTO TB_TEST1 VALUES (1, '한국');

SELECT * FROM TB_TEST1;

SELECT * FROM TB_TEST2;

UPDATE TB_TEST1 SET NM = '코리아' WHERE NUM = 1;

SELECT * FROM TB_TEST1;

SELECT * FROM TB_TEST2; 

 

TB_TEST2 의 IDX 값을 해당 NUM의 대한 일련번호를 사용할 경우에는 MAX를 사용해야 하나 FOR UPDATE를 사용하여 LOCK을 걸어야 하며 해당 ROW가 없을 경우에는 LOCK을 걸 수 없다.
그럴 경우 채번 테이블 생성하는 샘플을 보고 함수를 만들어 사용할 수 있다.

참고 : http://tyboss.tistory.com/entry/Oracle-채번-테이블-사용하여-채번하기-채번하기 

ALTER TABLE TB_TEST1 ADD IDX NUMBER DEFAULT 0; 

위처럼 TB_TEST1에 IDX 컬럼을 추가한 후 참고 링크의 채번 테이블 처럼 사용하면 된다.

 

 

출처 : http://blog.naver.com/imdkkang/120087840004
참고 : http://database.sarang.net/?inc=read&aid=38554&criteria=oracle
        

 데이터베이스의 존재 이유 중 하나는 수많은 사용자가 동시에 데이터에 접근, 수정하고 입력하는 작업을 수행하는 환경을 제공하는 데 있다. 자고로 많은 RDBMS 벤더에서 동시에 데이터에 접근하고 정합성을 보장하는 방법에 대해서 끊임없는 경쟁을 펼치고 새로운 방법을 내놓았다. 결국에는 많은 유저가 정합성을 보장하면서 데이터에 접근하는 환경을 제공하는 능력이 데이터 처리 능력과 트랜잭션 최대값으로 직결되고, 이것이 곧 각 RDBMS의 우열을 가르는 잣대라고 봐도 과언이 아니다. 이 글에서는 오라클 데이터베이스를 중심으로 LOCKING 메커니즘에 대해 설명한다.

 

<그림 1>과 같이 동시 유저수가 많을수록 트랜잭션 개수는 일정 수준 이상이 되면 줄어들고 정합성과 동시성 관계 역시나 반비례 관계에 있다. 데이터의 일관성을 높이기 위해서는 반드시 락(LOCK)을 사용해야 하지만, 동시성을 높이기 위해서는 락의 사용을 최소화해야 한다는 점에서 이 둘의 관계 제어(CON CURRENCY CONTROL)가 상당히 힘든 일임을 알 수 있다. 따라서 이번 글을 통해 동시성 제어를 향상시킬 수 있는 방법에 대해 알아보기로 하겠다.

 

<그림 1> 동시성과 정합성 관계

 

동시성 향상의 방법

 

LOST UPDATE 방지

① A 유저가 데이터를 조회한다.

② B 유저가 같은 로우를 조회한다.

③ A 유저가 애플리케이션을 통해 로우를 업데이트하고 COMMIT을 수행한다.

④ B 유저가 같은 로우를 업데이트한다.

 

결국은 A 유저가 업데이트한 데이터는 B 유저에 의해 오버라이트되어 버린다. 따라서 A가 수정한 데이터는 잃어버리게 된다. 이것이 전형적인 LOST UPDATE의 예이다. 이러한 오라클의 SELECT에 대한 non-blocking의 특성(select에 락모드가 null)으로 인한 세션에서의 LOST UPDATE를 방지하기 위해 정합성이 중시되는 프로그램 작성 시에 SELECT를 수행하는 로우에 SELECT FOR UPDATE 문을 사용해 명시적으로 락을 걸어주고 해당 로우에 대해 데이터 수정을 하는 경우가 많다.


그러나 이러한 Pessimistic Lock을 설정한 후 락의 보유 시간이 길거나 중간에 수행되는 로직이 복잡하면 해당 데이터를 동시에 변경하는 유저수가 늘어나고 늘어날수록 동시성은 떨어지게 된다. 물론 FOR UPDATE 문에 nowait , wiat n [second]와 같은 옵션이 존재해 대기시간을 조금 줄이는 효과를 낼 수 있지만 그리 큰 효과는 없을 것이다. 현장 애플리케이션 개발에서 오라클 locking 메커니즘을 제대로 이해하지 못하거나 믿지 못해 Pessmistic Locking을 과도하게 사용해 동시성이 저하되는 경우를 많이 목격했다.


그렇다면 Optimistic Locking만으로 동시성과 정합성이란 두 마리 토끼를 잡을 수는 없을까? 많은 방법들이 존재하지만 최적으로 꼽히는 방법 가운데 tomas kyte의 asktom 사이트에서도 수차례 언급했던 글을 토대로 방법을 알아보기로 하자.

 

Version 컬럼과 Pseudo 컬럼을 이용한 체크

 

그렇다면 일반적인 UPDATE 문, 즉 Optimistic Lock만으로 동시성을 확보하면서 여러 사용자가 동일한 데이터를 수정하더라도 정합성을 보장하는 방법에 무엇이 있는지를 살펴보도록 하자.

 

● Version 필드를 통한 체크

첫 번째 방법은 변경하는 테이블에 변경시간을 기록하는 컬럼을 추가하는 것이다. <리스트 1>은 DEPT 테이블에 last_mod라는 timestamp 형식의 컬럼을 추가해 SELECT FOR UPDATE로써 레코드 락을 걸지 않고 변경 컬럼 값만을 체크해 해당 컬럼이 변경되었다면 update되지 않도록 수행하는 처리 절차를 구사한 예이다. 이 방법은 명시적인 락을 걸지 않고도 정합성을 유지할 수 있다.

 

 

● Pseudo 컬럼을 이용한 변경 체크

앞의 변경 컬럼을 사용하는 것은 추가적인 테이블 변경 작업을 수반하기 때문에 사용할 수 없는 테이블이 수정 불가능한 환경에서는 부적합할 수도 있다. 여기서 기존의 테이블에 컬럼 추가 없이 Pseudo 컬럼을 이용한 방법을 사용할 수 있다.

 

이는 10g에서의 SCN 번호를 가져올 수 있는 ORA_ROWSCN 함수를 사용해서 체크하는 방법을 이용하면 간단히 구현될 수 있다. 10g에서는 SCN 값의 변경사항 중 하나가 종래의 block level SCN에서 Row level SCN을 지원하게 되었다는 것이다(SCN은 System Change Number 또는 System Commit Number라고 병행해서 사용된다. 이 값은 커밋시마다 부여되는 오라클의 내부시계와 같은 역할을 수행한다).

 


기존에 BLOCK 레벨로 부여하던 SCN 값이 로우 레벨에 따라 다른 번호를 가질 수 있게 된 것이다. 따라서 이러한 ora_ rowscn 값을 이용해 테이블의 로우가 언제 변경되었는지에 대한 정보도 뽑아볼 수 있다. 그러나 모든 테이블에 다 적용되는 것이 아닌 ROW LEVEL SCN을 적용하게끔 테이블 생성시 ROWDEPENDENCIES 옵션으로 생성해야 한다는 것이다.

 

UPDATE 여부 확인 

CREATE TABLE T_LOCK (
    ID NUMBER PRIMARY KEY
)
ROWDEPENDENCIES

INSERT INTO T_LOCK (ID) VALUES (1);

COMMIT;

INSERT INTO T_LOCK (ID) VALUES (2);

COMMIT;

SELECT ID
           , ORA_ROWSCN
           , SCN_TO_TIMESTAMP(ORA_ROWSCN) 
   FROM T_LOCK;

SELECT TABLE_NAME
   , DEPENDENCIES
 FROM USER_TABLES
WHERE TABLE_NAME = 'T_LOCK';

 

SEQUENCE와 채번 테이블을 이용한 동시성 향상

 

일반적으로 해당 테이블의 최대값을 읽어와서 INSERT를 수행하는 경우에 동시에 많은 유저가 수행된다면 PK 중복에러가 발생할 여지가 크다. 따라서 일반적으로 max 값을 읽어오는 select되는 시점에서 for update 문을 사용해 해당 레코드에 락을 거는 방법을 사용한다. 그러나 역으로는 동시성이 저하되는 부작용이 나타난다.

 

<그림 2> 다른 세션에서 최대값을 획득한 후 입력 시 에러 발생

 

● SEQUENCE 객체의 사용

단순 일련번호로 PK가 구성되어 있다면 시퀀스의 경우에는 동시다발적으로 여러 명의 유저가 시퀀스를 부여 받더라도 절대 중복이 발생하지 않는다. 중복 방지에만 초점을 맞춘다면 적합할지 모르지만 CACHE 값만큼 메모리에 고정(keeping)되어 있는 경우 데이터베이스 서버의 갑작스런 다운시나 메모리 영역에서 ageout되어 다음에 시퀀스를 발행할 경우 이전에 caching되어 있는 시퀀스의 수만큼 중간을 건너뛰고 발행될 수도 있다. 따라서 일련번호가 타이트하게 관리되어야 할 필요성이 있다면 적합하지 않다.

 

● 채번 테이블의 이용

시퀀스를 사용할 수 없는 특수한 환경에서는 채번 테이블을 사용하는 경우 동시 유저에 의해 채번 테이블이 사용될 수 있다. 다만 채번 테이블의 경우에는 여러 명이 동시에 접근할 경우 해당되는 채번테이블이 UPDATE 되는 경합을 유의해야 한다.

 

 

● 시퀀스를 이용한 update 분산

<리스트 4>의 경우 동시다발적인 유저가 많다면 해당되는 상태인 데이터의 첫 번째 로우를 업데이트하는 LOCKING 부하가 집중되는 문제가 발생한다.

 

 

따라서 시퀀스의 최대값을 객체를 동시에 수정하는 최대 유저수만큼 생성한 후 <리스트 5>와 같은 쿼리로 수정하면 시퀀스의 수만큼 LOCKING이 분산되는 효과를 볼 수가 있다.

 

 

Deadlock의 방지

 

프로세스 A는 테이블 T1에 락을 걸고 있고 테이블 T2의 데이터를 변경하려 하며, 프로세스 B는 테이블 T2에 락을 걸고 있고 테이블 T1의 데이터를 변경하려는 상황에서 더 이상 진행되지 않고 HANG이 발생하는 경우가 여러분들이 잘 아는 전형적인 Deadlock이라고 할 수 있다. 오라클에서 Deadlock이 발생하면 뒤에 락을 획득한 쪽(프로세스B)이 실패해 모든 락을 해제하면서 에러가 발생하고 트랜잭션이 rollback된다.

 

수년간 데이터베이스 운영과 프로젝트를 수행했지만 이러한 데이터베이스 관리 시에 Deadlock은 굉장히 드물게 발생했다. 다만 신규로 개발된 프로시저나 애플리케이션의 단일 테이블에서 일반적으로 아주 드물게 발생하는 경우가 있고 오라클에서도 자체적으로 발생하는 경우보다는 애플리케이션 설계상의 문제로 발생되는 경우가 많다. 특히 데이터베이스 애플리케이션뿐만 아니라 자바의 synchronized 등의 다양한 상황에서 만날 수 있는 경우가 많다. 프로젝트 초기에 이러한 Deadlock의 상황을 만난다면 원인을 찾아내기가 상당히 까다로운 게 사실이다. 그렇다면 이러한 Deadlock을 방지하는 방법에는 어떤 것들이 있을까?

 

<그림 3> Deadlock의 도식화

 

오라클의 락(Lock) 종류

 

오라클에서는 락의 종류가 크게 세 가지로 구분된다.

락의 종류
설명
참조 뷰
DML Lock INSERT, UPDATE,MERGE, DELETE
등의 DML 작업 시 발생. 지정된 로우
레벨 또는 테이블 레벨(테이블의 모든
로우의 Lock을 잡는 경우)로 발생		 dba_dml_locks
DDL Lock CREATE, ALTER,COMPLIE 등의
DDL 작업에 의해 발생(일반적으로
오브젝트 구조 정의를 보호하기 위한
LOCK)		 Dba_ddl_locks
Latch, Mutex 오라클의 메모리 구조를 보호하기
위한 가벼운 lock(latch),
(10g R2)

V$latch,

Wait event 뷰

 

 

● INITRANS의 추가

동일 블록 당 진행할 수 있는 트랜잭션의 개수를 지정하는 옵션인 INTRANS 값이 부족하면 Deadlock이 발생할 수 있다. 이 값이 부족하면 서로 다른 트랜잭션이 각기 동일 테이블의 각기 다른 데이터를 수정하더라도 그 데이터가 동일 블록에 위치한다면 Deadlock이 발생할 수 있다. 따라서 하나의 테이블에 대해 퍼포먼스 등의 이유로 동시에 다른 데이터를 처리하는 트랜잭션을 수행하고자 한다면, 테이블의 INTRANS 값을 크게 해서, 하나의 블록에 여러 개의 트랙잭션이 DML 처리를 수행하더라도 공간이 부족해서 기다리는 상황은 없도록 해야 한다.

 

● 적은 블록 사이즈와 블록당 적은 데이터

너무 큰 블록 사이즈(표준 블록 사이즈 8K보다 큰)를 사용한다거나 하나의 블록에 많은 데이터가 들어 있다면 BLOCK LEVEL 락이 발생할 가능성이 크다. 따라서 pctfree를 약간 크게 잡음으로써 의도적으로 블록 당 로우수를 줄이는 방법도 그 대안이 될 수 있다. 될 수 있으면 표준 블록사이즈를 사용하거나 필요하다면 오라클의 멀티블록 사이즈를 이용해 2K, 4K 적은 블록 사이즈의 이용을 고려해본다.

>> 각주:PCTFREE: 업데이트 시 사이즈가 늘어날 것을 대비한 BLOCK의 여유공간 비율. 일반적으로 10%. 이 값을 크게 잡는다면 블록당 들어가는 로우 사이즈는 줄어들 것이다.

 

● 해당 테이블에 적절한 인덱스의 존재 여부 확인

오라클 데이터베이스에는 해당되지 않지만 SQL 서버의 경우에는 INDEX SCAN이 아닐 경우 Lock escalation(락의 확대)울 수행해서 테이블 락으로 확대해 버린다. 오라클은 Lock escalation은 발생하지 않지만 FK, PK 관계의 부모 자식 테이블에서 부모 테이블의 로우를 삭제할 경우 인덱스가 없다면 자식 테이블 전체가 Shared Lock이 발생해버린다(이러한 사항은 8i 이전의 문제였고 Oracle 9i 이상이라면 FK에 INDEX가 존재하지 않아도 어떠한 락도 발생하지 않는 알고리즘으로 변화).

 

● 트랜잭션 처리의 최적화

SELECT FOR UPDATE로 LOCKING된 레코드의 처리에 너무 많은 로직이 들어간다거나 처리되는 DML 문이 최적화되지 못해 락의 보유시간이 길어지는 것을 방지해야 한다. 또한 정합성을 지나치게 중시해 과도하게 넓은 범위를 LOCKING하는 경우를 가능하면 줄이도록 한다.

● 애플리케이션 튜닝

업데이트와 관련된 싱글 스레딩으로 변경하는 것을 고려할 수 있다. 또한 업데이트 작업과 관련된 배치작업을 리스케줄링하는 것도 고려할 수 있다. 가능한 조치를 했음에도 불구하고 이러한 상황이 발생했다는 것은 제대로 된 애플리케이션 프로세스 모델링과 데이터베이스 모델링이 있지 않다는 증거이다. 가급적 이러한 상황을 막기 위해서는 무엇보다도 업무단위에 대한 철저한 분석과 설계가 선행되어야 한다.

 

 

Deadlock 분석을 위한 팁

 

Deadlock이 발생한다면 alert.log에 해당 로그가 기록되지만 원인과 주체를 분석하기에는 정보가 부족한 것이 사실이다. 따라서 해당되는 Deadlock의 자세한 사항을 수집할 수 있도록 10027 이벤트를 추가적 으로 걸어줘서 해당주체를 분석할 수 있다. 레벨에 따라서 call stack, action, process state, SQL 문장 등의 정보를 획득할 수 있다. 다만 DBA 권한을 가진 유저만이 수행할 수 있다. 다음은 4 레벨로 call stack trace를 생성하게끔 수행한 예이다.

 

SQL>ALTER SYSTEM SET EVENTS‘ 10027 trace name context
forever, level 4’

 

 

● SELECT FOR UPDATE(비관적 잠금)의 동시성 증대

일반적으로 동시성 향상보다도 정합성에 초점을 두거나 LOST UPDATE를 방지하기 위한 목적으로 FOR UPDATE 옵션으로서 SELECT되는 해당 로우에 대한 잠금을 명시적으로 실행하는 방법을 많이 쓰고 있으리라고 생각된다. 그러나 이러한 명시적인 잠금은 잘 쓰면 정합성을 유지하는데 약이 되지만 잘못 쓸 경우 엄청난 동시성 저해 요소가 될 수 있다.

 

만일 다중 로우에 대한 락을 걸 때 한 건의 로우가 기존에 락이 걸린 상태면 전체가 잠금을 시도하는데 있어서 에러가 발생한다. 따라서 넓은 범위의 로우를 Locking하고 싶은 경우 이 한 건의 락이 해제될 때까지 기다려야 하는 상황이 발생한다.

리조트 예약시스템의 경우를 예로 들어보자. 단체 손님을 받아서 10층의 룸을 전부 락을 건 채 예약 작업을 시도한다고 가정하자. 그러나 옆의 다른 직원이 동일 층의 개인 손님 예약을 이미 락을 걸고 작업 중이면 앞의 직원은 Locking 작업은 실패할 것이며 이전 직원의 작업이 끝나기를 기다려야 할 것이다.

여기에 이러한 상황에 적합한 SELECT FOR UPDATE SKIP LOCKED라는 기존 Locking 범위를 Skip하는 추가적인 옵션이 존재한다. 이 방법은 9i부터 지원되는 undocumented된 방법이며 과거부터 메시지 처리를 위한 AQ(Advanced Queuing) 기능을 위해 존재했던 기능이기도 하다. 이 기능을 이용한 예제를 살펴보자.

 

 

<그림 4> SKIP LOCKED를 사용한 LOCK의 확대

 

유의해야 할 것은 이 기능은 Oracle 11g부터 정식으로 매뉴얼 상에 등장하는 기능이므로 이전 버전에서는 몇 가지 불안정한 면이 존재했다. 따라서 오라클 데이터베이스의 동시성 향상을 위한 예로서 소개한 기능이므로 실 환경에서의 적용보다는 테스트에 적용해 사용하길 바란다.

 

짧게나마 간단한 예제로서 동시성 제어와 관련된 사항과 이에 대한 방법론에 대해 살펴봤다. 동시성 제어는 매우 중요함에도 불구하고 프로젝트 수행 중에 만났던 많은 개발자들이 아직 많이 간과하는 부분이었다.

 

DBMS_LOCK을 이용한 사용자 정의 lock(User-defined lock)

 

사용자가 임의로 특정 LOCK을 재현하기 위해서는 오브젝트 생성과 특정 트랜잭션이 필요하지만 DBMS_LOCK을 사용해 사용자 정의 lock을 구현할 수 있다.
몇 가지 UL을 잘 사용한다면 특정 사항을 재현해낸다거나 procedure 내에서 특정 구간의 작업을 지연시켜 변동사항을 확인 가능한 유용한 방법이 될 수 있다.

 

- DBMS_LOCK.SLEEP(n) : n초 만큼 대기
- DBMS_LOCK.REQUEST : 지정된 lockmode로 LOCK을 획득
- DBMS_LOCK.RELEASE : 락을 해제
- DBMS_LOCK.ALLOCATE_UNIQUE : LOCK 명을 LOCK HANDLE로 컨버전

 

 

 

 

필자소개

 

김도근 kilgw@naver.com｜OracleAce, OCM, MCDBA ‘데이터베이스란 OS 위에 올라가 있는 애플리케이션에 불과하다' 라는 생각으로 항상 데이터베이스를 생각하면서 데이터베이스를 공부하는 것을 낙으로 삼고 있다. 스터디 및 온라인, 오프라인 기고 등의 활동을 전개 중이다.

 

출처 : 한국 마이크로 소프트웨어 [2009년 7월호]

제공 : DB포탈사이트 DBguide.net


=================================================================================================================
=================================================================================================================

출처 : http://www.gurubee.net/pages/viewpage.action?pageId=22904839

동시성 구현 사례

학습 목표
  • 일련번호 채번, 선분이력 등의 동시성 구현 사례를 살펴보고 완벽하게 이해한다

일련번호 채번 동시성 높이기

DBMS가 제공하는 Sequence 기능을 사용하는 것이 가장 좋으나, 이를 사용할 수 없는 경우 별도 구현해야 함

(1) 데이터가 삽입되는 시점에 실시간으로 현재의 MAX 값을 취해 1만큼 증가시킨 값을 이용하는 방식
  • 두 개의 트랜젝션이 동시에 같은 값을 읽었을 경우 insert 하려는 순간 PK 제약 위배됨
  • Exception 처리를 통해 동시성 제어
(2) MAX 값을 관리하는 별도의 채번 테이블에서 값을 가져오는 방식
  • 채번 테이블 생성 및 채번 함수 정의 
    create table seq_tab (
  gubun varchar2(1),
  seq   number,
  constraint pk_seq_tab primary key(gubun, seq)
)
organization index;

create or replace function seq_nextval(l_gubun number) return number
as
  pragma autonomous_transaction; -- 메인 트랜젝션에 영향을 주지 않고 서브 트랜젝션만 따로 커밋
  l_new_seq seq_tab.seq%type;
begin
  update seq_tab
     set seq = seq + 1
   where gubun = l_gubun;

  select seq into l_new_seq
    from seq_tab
   where gubun = l_gubun;

  commit;
  return l_new_seq;
end;
/
  • 앞서 정의한 테이블 및 함수를 사용한 트랜젝션 예시 
    begin
  update tab1
     set col1 = :x
   where col2 = :y ;

  insert into tab2
  values (seq_nextval(123), :x, :y, :z);

  loop
    -- do anything ...
  end loop;

  commit;

exception
  when others then
  rollback;
end;
/
  • pragma autonomous_transaction 옵션을 사용하지 않은 경우는?
    • 메인 트랜젝션의 insert 구문 이후에 롤백이 되는 경우 앞의 update문까지 이미 커밋된 상태로 되어 데이터 일관성이 깨짐
    • DB2 9.7 버전부터 지원
    • SQL Server에서는 지원하지 않음. Savepoint 활용 권고.
  • seq_nextval 함수에서 커밋을 안하는 경우는?
    • 메인 트랜젝션이 종료될 때까지 채번 테이블에 Lock이 걸린 상태가 되어 성능저하 초래

선분이력 정합성 유지

선분이력을 추가하고 갱신할 때 발생할 수 있는 동시성 이슈를 해결한 사례

  • 선분이력모델은 여러 장점이 있지만 잘못하면 데이터 정합성이 쉽게 깨질 수 있는 단점 존재
  • 정확히 핸들링하는 방법을 알아야 한다

  • 기본 최종 선분이력을 끊고 새로운 이력 레코드를 추가하는 전형적인 처리 루틴 
    declare
  cur_dt varchar2(14);
begin
  select 고객ID
    from 부가서비스이력
   where 고객ID = 1
     and 종료일시 = to_date('99991231235959', 'yyyymmddhh24miss')
     for update nowait ;

  select 고객ID
    from 고객
   where 고객ID = 1
     for update nowait ;

  cur_dt := to_char(sysdate, 'yyyymmddhh24miss') ; -- ①

  update 부가서비스이력 -- ②
     set 종료일시 = to_date(:cur_dt, 'yyyymmddhh24miss') - 1/24/60/60
   where 고객ID = 1
     and 부가서비스ID = 'A'
     and 종료일시 = to_date('99991231235959', 'yyyymmddhh24miss') ;

  insert into 부가서비스이력(고객ID, 부가서비스ID, 시작일시, 종료일시) -- ③
  values (1, 'A', to_date(:cur_dt, 'yyyymmddhh24miss'),
         to_date('99991231235959', 'yyyymmddhh24miss')) ;

  commit; -- ④
end;
  • 신규등록 건이면 ②번 update문에서 실패하고 ③번에서 한 건이 insert 됨
  • select for update문이 없다면?
    • 첫 번째 트랜젝션이 ①을 수행하고 ②로 진입하기 직전에 두 번째 트랜젝션이 동일 이력에 대해 ①~④를 먼저 진행해 버린다면 선분이력이 깨짐
    • 트랜젝션이 순차적으로 진행할 수 있도록 직렬화 장치 필요 : select for update문을 이용해 해당 레코드에 Lock을 설정
  • 부가서비스이력 테이블에만 select for update로 Lock을 거는 경우?
    • 기존에 부가서비스이력이 전혀 없던 고객인 경우 Lock이 걸리지 않음
    • 동시에 두 개 트랜젝션이 ③번 insert문으로 진입하여 시작일시는 다르면서 종료일시는 같은 두 개의 이력 레코드 생성
    • 상위엔티티인 고객 테이블에 select for update로 Lock을 걸어 완벽하게 동시성 제어
    • 다른 상위엔티티인 부가서비스 테이블에 Lock을 걸 수도 있지만, 여러 사용자가 동시에 접근할 가능성이 크기 때문에 동시성이 나빠질 수 있으므로 고객 테이블에 Lock 설정

 

 

출처 : http://inhim.blog.me/100101665843

채번 테이블을 사용하여 키값을 가져오는 방식은 추천할만한 방법은 아니다.

특히 키값이 건너뜀 없이 순차적으로 발행되어야 하는 업무요건이라면 채번 트랜잭션과 메인 트랜잭션이 분리될 수 없기 때문에 심한 Lock 경합을 피할 수 없을 것이다.

이런 업무요건은 heavy 한 동시트랜잭션 환경에서는 받아들일 수 없는 것이다.

키값이 건너뛰어 발행되도 괜찮다면 채번 트랜잭션은 메인 트랜잭션으로부터 분리하여 Lock 경합을 최소화하여야 한다.

 

아래의 방법은 간단한 자율트랜잭션 채번펑션을 이용해 이를 구현한 사례로서 "오라클 성능 고도화 원리와 해법 1(조시형 저)" 에서 발췌하였다.

 

이 사례에서 중요한 것은 채번펑션이 자율트랜잭션(autonomous_transaction) 이어야 한다는 것이다. 그렇지 않다면 메인트랜잭션의 일관성이 깨지게 될 것이기 때문이다.

예를 들어, 메인트랜잭션에서 8번 라인에서 에러를 만나 rollback 될 때에도 2번 라인의 update 문은 commit 될 것이기 때문이다.

하지만 채번펑션이 자율트랜잭션이라면 2번 라인의 update 문은 commit 되지 않을 것이므로 메인트랜잭션은 일관성을 지킬 수 있다.

 
채번 테이블 생성

CREATE TABLE SEQ_TAB (GUBUN  VARCHAR2 (3)
          , SEQ   NUMBER
          , CONSTRAINT PK_SEQ_TAB PRIMARY KEY (GUBUN, SEQ)
           )
ORGANIZATION INDEX; 

ORGANIZATION INDEX 에 대한 설명 : http://blog.naver.com/tyboss/70151522617

 

채번 Function

 CREATE OR REPLACE FUNCTION SEQ_NEXTVAL (L_GUBUN VARCHAR2)
  RETURN NUMBER
IS
  PRAGMA AUTONOMOUS_TRANSACTION;                  --자율트랜잭션 선언
  L_NEW_SEQ  SEQ_TAB.SEQ%TYPE;
BEGIN
  SELECT SEQ + 1
   INTO L_NEW_SEQ
   FROM SEQ_TAB
  WHERE GUBUN = L_GUBUN
       FOR UPDATE;

  UPDATE SEQ_TAB
   SET SEQ = L_NEW_SEQ
  WHERE GUBUN = L_GUBUN;

  COMMIT;
  RETURN L_NEW_SEQ;
END;
/

 

채번 Function 사용

 BEGIN
  UPDATE TAB1
   SET COL1 = :X
  WHERE COL2 = :Y;

  INSERT INTO TAB2
    VALUES (SEQ_NEXTVAL ('100')
       , :X
       , :Y
       , :Z);

  LOOP
   -- do anything ...
   END LOOP;
   COMMIT;
 EXCEPTION
   WHEN OTHERS THEN ROLLBACK;
 END;
/

 

======================================================================================================

 

자율트랜잭션(autonomous transaction)이란 부모트랜잭션으로부터 독립적인 트랜잭션이라는 뜻으로서, 자율트랜잭션에 대해 더 알기 원한다면 아래 실습 결과를 보세요.

채번할 때 사용하는 COMMIT 때문에 PRAGMA AUTONOMOUS_TRANSACTION; 옵션을 사용하지 않은 경우에는 비지니스 로직의 ROLLBACK이 실행되지 않음

 CREATE TABLE TAB_NONAUTO (MSG CLOB);

CREATE TABLE TAB_AUTO (MSG CLOB);

-- 1번 프로시저 생성
CREATE OR REPLACE PROCEDURE NONAUTONOMOUS_INSERT
AS
BEGIN
  INSERT INTO TAB_NONAUTO
    VALUES ('일반자식트랜잭션 입력');

  COMMIT;
END;
/

-- 2번 프로시저 생성
CREATE OR REPLACE PROCEDURE AUTONOMOUS_INSERT
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO TAB_AUTO
    VALUES ('자율자식트랜잭션 입력');

  COMMIT;
END;
/

-- 테스트
BEGIN
    INSERT INTO TAB_NONAUTO VALUES('부모트랜잭션 입력');
     NONAUTONOMOUS_INSERT;
     ROLLBACK;
END;
/

SELECT * FROM TAB_NONAUTO;

MSG

---------------------------

부모트랜잭션 입력

일반자식트랜잭션 입력

 

 

 

BEGIN
      INSERT INTO TAB_AUTO VALUES('부모트랜잭션 입력');
     AUTONOMOUS_INSERT;
     ROLLBACK;
END;
/

 

SELECT * FROM TAB_AUTO;

 

 

MSG

---------------------------

자율자식트랜잭션 입력

 

 

 

 

 

출처 : http://blog.naver.com/genezerg?Redirect=Log&logNo=20142202559

분석함수중에서 윈도우절(WINDOW절)을 사용할수 있는 함수를 윈도우함수라고 한다.
고로 분석함수중에서 일부만 윈도우절을 사용할수 있다는것이다.
PARTITION BY 절에 의해 명시된 그룹을 다시 그룹핑할수 있다.

윈도우 함수 종류

 - AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN, STDDEV, SUM...

 

문법 > SUM(컬럼명) OVER(PARTITION BY [컬럼] ORDER BY [컬럼] [ASC/DESC] 
                            ROWS / RANGE BETWEEN UNBOUNDED PRECEDING / PRECEDING / CURRENT ROW
        AND UNBOUNDED FOLLOWING / CURRENT ROW)

          ROW : 부분집합인 윈도우 크기를 물리적인 단위로 행 집합을 지정

          RANGE : 논리적인 주소에 의해 행 집합을 지정

          UNBOUNDED PRECEDING : 윈도우의 시작 위치가 첫번째 ROW

          UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 ROW

          CURRENT ROW : 윈도우의 시작 위치가 현재 ROW

 

 예 >    SELECT JOB, SAL
           ,SUM(SAL) OVER(PARTITION BY JOB ORDER BY EMPNO
           ROWS BETWEEN UNBOUNDED PRECEDING  AND UNBOUNDED FOLLOWING) SAL1

           ,SAL
           ,SUM(SAL) OVER(PARTITION BY JOB ORDER BY EMPNO
           ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW) SAL2

           ,SAL
           ,SUM(SAL) OVER(PARTITION BY JOB ORDER BY EMPNO
           ROWS BETWEEN CURRENT ROW  AND UNBOUNDED FOLLOWING) SAL3
           FROM EMP
           WHERE JOB = 'MANAGER'

 

          

SAL1 : 첫째ROW부터 마지막ROW까지 윈도우로 설정되었으므로 전체합계

SAL2 : 첫째ROW부터 현재ROW까지 윈도우로 설정되었으므로 누적합계

SAL3 : 현재ROW부터 마지막ROW까지 설정되었으으로 누적합계의 반대개념

 

 

           SELECT JOB, SAL
           ,SUM(SAL) OVER(PARTITION BY JOB ORDER BY EMPNO
           ROWS BETWEEN 1 PRECEDING  AND 1 FOLLOWING) SAL1

           ,SAL
           ,SUM(SAL) OVER(PARTITION BY JOB ORDER BY EMPNO
           ROWS BETWEEN 1 PRECEDING  AND CURRENT ROW) SAL2

           ,SAL
           ,SUM(SAL) OVER(PARTITION BY JOB ORDER BY EMPNO
           ROWS BETWEEN CURRENT ROW  AND 1 FOLLOWING) SAL3
           FROM EMP
           WHERE JOB = 'MANAGER'

 

          

            SAL1 : 현재ROW중심으로 이전과 다음ROW의 합계

 SAL2 : 이전ROW와 현재ROW의 합계

 SAL3 : 현재ROW와 다음ROW의 합계

 

 

Oracle 10g SQL MODEL Clase.pdf

참고 : http://radiocom.kunsan.ac.kr/lecture/oracle/statement_select/model.html
         http://www.oracleclub.com/article/26172


위 첨부파일을 꼭 참고하세요

다음과 같은 seq, amt 자료가 있을때 result를 구하는 쿼리를 작성하세요.
순차적으로 amt값을 누적합산하되 그 값이 음수일경우엔 0이 되어야 합니다.

WITH t AS
(
SELECT 1 seq, -2000 amt FROM dual
UNION ALL SELECT  2,   4000 FROM dual
UNION ALL SELECT  3,  -5000 FROM dual
UNION ALL SELECT  4,  -2000 FROM dual
UNION ALL SELECT  5,   3000 FROM dual
UNION ALL SELECT  6,   1500 FROM dual
UNION ALL SELECT  7,   -250 FROM dual
UNION ALL SELECT  8,    320 FROM dual
UNION ALL SELECT  9,  -4000 FROM dual
UNION ALL SELECT 10,  10000 FROM dual
UNION ALL SELECT 11, -20000 FROM dual
)
SELECT * FROM t

 

SEQ

AMT

RESULT

계산 방법 참고

1

 -2000 0  -2000 이 음수이므로 0

2

 4000 4000  0 + 4000 = 4000

3

 -5000 0  4000 - 5000 = -1000 = 0

4

 -2000 0  0 - 2000 = -2000 = 0

5

 3000 3000  0 + 3000 = 3000

6

 1500 4500  3000 + 1500 = 4500

7

 -250 4250  4500 - 250 = 4250

8

 320 4570  4250 + 320 = 4570

9

 -4000 570  4570 - 4000 = 570

10

 10000 10570  570 + 10000 = 10570

11

 -20000 0  10570 - 20000 = -9430 = 0

 

WITH T AS
(
SELECT 1 seq, -2000 amt FROM dual
UNION ALL SELECT  2,   4000 FROM dual
UNION ALL SELECT  3,  -5000 FROM dual
UNION ALL SELECT  4,  -2000 FROM dual
UNION ALL SELECT  5,   3000 FROM dual
UNION ALL SELECT  6,   1500 FROM dual
UNION ALL SELECT  8,    320 FROM dual
UNION ALL SELECT  7,   -250 FROM dual
UNION ALL SELECT  9,  -4000 FROM dual
UNION ALL SELECT 10,  10000 FROM dual
UNION ALL SELECT 11, -20000 FROM dual
)
SELECT SEQ, AMT, RESULT FROM T
MODEL
DIMENSION BY (SEQ)
MEASURES (AMT, 0 RESULT)
IGNORE NAV
RULES
AUTOMATIC ORDER
(
      RESULT[SEQ] = GREATEST(0, RESULT[CV(SEQ) - 1] + AMT[CV(SEQ)])
)
ORDER BY SEQ

================================================================================================================
================================================================================================================

참고로 누적 구하는 쿼리

WITH TEST AS
    (
    SELECT '1' SEQ , '입고' INOUT_STAT , 10 CNT FROM DUAL
    UNION ALL SELECT '2' , '출고' , 2 FROM DUAL
    UNION ALL SELECT '3' , '입고' , 3 FROM DUAL
    UNION ALL SELECT '4' , '입고' , 5 FROM DUAL
    UNION ALL SELECT '5' , '출고' , 7 FROM DUAL
    UNION ALL SELECT '6' , '입고' , 11 FROM DUAL
    )
SELECT SEQ
   , INOUT_STAT
   , CNT
   , SUM (DECODE (INOUT_STAT, '입고', CNT, CNT * -1)) OVER (ORDER BY SEQ) CNT2
   , SUM(CNT) OVER(ORDER BY SEQ) CNT3
 FROM TEST 

 

출처 : http://blog.naver.com/rainbow8830?Redirect=Log&logNo=70119741931

 

1. EXTRACT
   - date type 의 값에서 지정한 field(년, 월, 일) 항목을 추출
   + 사용법
     - SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL : 현재 날짜에서 년도만 추출
     - SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL : 현재 날짜에서 월만 추출
     - SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL : 현재 날짜에서 일 수만 추출 

2. NUMTOYMINTERVAL(NUMBER, TYPE_OF_INTERVAL)
   - 년, 월에 대한 시간 간격을 구함
   + 사용법
     - SELECT SYSDATE - NUMTOYMINTERVAL (1, 'YEAR') FROM DUAL : 현재 날짜에서 1년을 뺌
     - SELECT SYSDATE - NUMTOYMINTERVAL (1, 'MONTH') FROM DUAL : 현재 날짜에서 1달을 뺌

 3. NUMTODSINTERVAL(NUMBER, TYPE_OF_INTERVAL)
   - 날짜, 시, 분, 초에 대한 시간 간격을 구함
   + 사용법
     - SELECT SYSDATE - NUMTODSINTERVAL (1, 'DAY') FROM DUAL : 현재 날짜에서 1일을 뺌
     - SELECT SYSDATE - NUMTODSINTERVAL (1, 'HOUR') FROM DUAL : 현재 날짜에서 1시간을 뺌
     - SELECT SYSDATE - NUMTODSINTERVAL (1, 'MINUTE') FROM DUAL : 현재 날짜에서 10분을 뺌
     - SELECT SYSDATE - NUMTODSINTERVAL (1, 'SECOND') FROM DUAL : 현재 날짜에서 100초를 뺌

 

WITH TBL AS (
SELECT  '201211051000' STM , '201211051100' ETM FROM DUAL UNION ALL
SELECT  '201211051100' STM , '201211051110' ETM FROM DUAL UNION ALL
SELECT  '201211051200' STM , '201211061420' ETM FROM DUAL
)
SELECT  CHA_1
       ,SUBSTR(CHA_1, 9,2) || '일 ' ||
        SUBSTR(CHA_1,12,2) || '시 ' ||
        SUBSTR(CHA_1,15,2) || '분' CHA_1C
       ,CHA_2
       ,TO_NUMBER(SUBSTR(CHA_2, 2, 9)) || '일 ' ||
        SUBSTR(CHA_2,12,2) || '시 ' ||
        SUBSTR(CHA_2,15,2) || '분' CHA_2C
       ,CHA_3
       ,SUBSTR(CHA_3, 8,3) || '일 ' ||
        SUBSTR(CHA_3,12,2) || '시 ' ||
        SUBSTR(CHA_3,15,2) || '분' CHA_3C
  FROM (
    SELECT  NUMTODSINTERVAL(SUM(
            TO_DATE(ETM,'YYYYMMDDHH24MI') - TO_DATE(STM,'YYYYMMDDHH24MI'))
                ,'DAY') CHA_1
           ,NUMTODSINTERVAL(SUM(
            TO_DATE(ETM,'YYYYMMDDHH24MI') - TO_DATE(STM,'YYYYMMDDHH24MI')) + 0.00000000001
                ,'DAY') CHA_2
           ,NUMTODSINTERVAL(SUM(
            TO_DATE(ETM,'YYYYMMDDHH24MI') - TO_DATE(STM,'YYYYMMDDHH24MI')) + 0.000694444444444444,'DAY') CHA_3
      FROM  TBL
    ) 

 

 

WITH TB AS (
SELECT MOD(TO_DATE('201211051100', 'YYYYMMDDHH24MI') - TO_DATE('201211051000', 'YYYYMMDDHH24MI'), 1) TIM FROM DUAL
UNION ALL
SELECT MOD(TO_DATE('201211051100', 'YYYYMMDDHH24MI') - TO_DATE('201211051000', 'YYYYMMDDHH24MI'), 1) TIM FROM DUAL
)
SELECT TIM
        , TO_CHAR(TO_DATE(ROUND(SUM(TIM) * 86400), 'SSSSS'), 'HH24:MI') BBB
  FROM TB

 

 

 

출처 : http://raltigue.tistory.com/m/11

 

LISTAGG 함수가 도입되기 전에 동일 기능을 구현하기 위해 다양한 기법들이 사용되었다. 정리해보자.

 

아래와 같이 데이터를 생성하자.

CREATE TABLE t1 (c1 NUMBER(1), c2 VARCHAR2(2));

INSERT INTO t1 VALUES (1, '01');
INSERT INTO t1 VALUES (2, '02');
INSERT INTO t1 VALUES (2, '03');
INSERT INTO t1 VALUES (3, '04');
INSERT INTO t1 VALUES (3, '04');
INSERT INTO t1 VALUES (3, '05');
INSERT INTO t1 VALUES (3, '06');

 

① 11g를 사용한다면 LISTAGG 함수를 사용하면 된다. 집계함수(1번)와 분석함수(2번) 형태로 사용이 가능하다.

-- 1
SELECT   a.c1,
         LISTAGG (a.c2, ',') WITHIN GROUP (ORDER BY a.c2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

-- 2
SELECT a.c1,
       LISTAGG (a.c2, ',') WITHIN GROUP (ORDER BY a.c2) OVER (PARTITION BY A.c1) AS c2
  FROM t1 a;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  2 02,03       
  3 04,04,05,06 
  3 04,04,05,06 
  3 04,04,05,06 
  3 04,04,05,06 

7 rows selected.

 

② WM_CONCAT 함수는 WMSYS 유저가 내부적으로 사용한다. (SQL Reference에 없다...--;) LISTAGG보다 성능은 떨어지지만 추가 기능(DISTINCT 구문, 분석함수 누적, KEEP 절)을 지원한다. 4번 방식을 이용하면 정렬도 가능하다. 

-- 1
SELECT   a.c1,
         wmsys.wm_concat (a.c2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,06,05,04 

3 rows selected.

-- 2
SELECT   a.c1,
         wmsys.wm_concat (DISTINCT a.c2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,05,06    

3 rows selected.

-- 3
SELECT a.c1,
       wmsys.wm_concat (a.c2) OVER (ORDER BY a.c2) AS c2
  FROM t1 a;

 C1 C2                    
--- ----------------------
  1 01                    
  2 01,02                 
  2 01,02,03              
  3 01,02,03,04,04        
  3 01,02,03,04,04        
  3 01,02,03,04,04,05     
  3 01,02,03,04,04,05,06  

7 rows selected.

-- 4
SELECT   a.c1,
         MAX (CAST (a.c2 AS VARCHAR2 (4000))) as c2
    FROM (SELECT a.c1,
                 wmsys.wm_concat (a.c2) OVER (PARTITION BY a.c1 ORDER BY a.c2) AS c2
            FROM t1 a) a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

-- 5
SELECT   a.c1,
         wmsys.wm_concat (a.c2) KEEP (DENSE_RANK FIRST ORDER BY a.c2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02          
  3 04,04       

3 rows selected.

 

③ 10g에서는 XMLAGG 함수를 사용해도 된다. 

SELECT   a.c1,
         SUBSTR (XMLAGG (XMLELEMENT (a, ',', a.c2) ORDER BY a.c2).EXTRACT ('//text()'), 2) AS c2
    FROM t1 a
GROUP BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

 

④ 후임자를 괴롭히고 싶다면 MODEL 절을 사용해도 된다...--;

SELECT   a.c1,
         RTRIM (a.c2, ',') as c2
    FROM (SELECT c1,
                 c2,
                 rn
            FROM t1 a
           MODEL PARTITION BY (a.c1)
                 DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY a.c1 ORDER BY a.c2) AS rn)
                 MEASURES (CAST (a.c2 AS VARCHAR2(4000)) AS c2)
                 RULES (c2[ANY] ORDER BY rn DESC = c2[CV()] || ',' || c2[CV()+1])) a
   WHERE a.rn = 1
ORDER BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

 

⑤ 9i에서는 전통적 방식인 ROW_NUMBER와 SYS_CONNECT_BY_PATH 조합을 사용하면 된다. 

SELECT     a.c1,
           SUBSTR (MAX (SYS_CONNECT_BY_PATH (a.c2, ',')), 2) AS c2
      FROM (SELECT a.c1,
                   a.c2,
                   ROW_NUMBER () OVER (PARTITION BY a.c1 ORDER BY a.c2) AS rn
              FROM t1 a) a
START WITH a.rn = 1
CONNECT BY a.c1 = PRIOR a.c1
       AND a.rn - 1 = PRIOR a.rn
  GROUP BY a.c1
  ORDER BY a.c1;

 C1 C2          
--- ------------
  1 01          
  2 02,03       
  3 04,04,05,06 

3 rows selected.

 

1. listagg function in 11g release 2

2. ORACLE-BASE - String Aggregation Techniques

출처 : http://database.sarang.net/?inc=read&aid=25686&criteria=oracle&subcrit=&id=&limit=20&page=3

 

MONTHS_BETWEEN 과 ADD_MONTHS 함수는 잘 이해하고 사용하셔야 합니다.

오라클 매뉴얼을 보시면 아시겠지만...

위의 경우를 놓고 설명하자면,

 

1. 첫번째 v_a_dt : 20020228

두번째 v_b_dt : 20011128

-> 위의 경우와 같이 뒤의 일자부분이 동일한 경우에, MONTHS_BETWEEN 함수는 두 일자의 월간격 즉, 3을 리턴합니다.

 

3. 첫번째 v_a_dt : 20020228

두번째 v_b_dt : 20011130

-> 이 경우는, 달력을 보시면 아시겠지만, 두 날짜 모두 그 날짜가 속한 달의 말일입니다. 이 경우에도 위의 1의 경우와 마찬가지로 두 일자의 월간격 3을 리턴합니다.

 

2. 첫번째 v_a_dt : 20020228

두번째 v_b_dt : 20011129

-> 마지막으로 1번도 3번도 해당하지 않는 경우에는 다음과 같이 계산됩니다.

2001년 11월 29일 + 1달 = 2001년 12월 29일

2001년 11월 29일 + 2달 = 2002년 01월 29일

2001년 11월 29일 + 3달 = 2002년 02월 29일 인데, 28일까지 밖에 없으므로 3달 보다는 적은 2.xxxxxxx 달이 될 것입니다.

그러면 뒤에 소수부분은 어떻게 계산할까요?

2002년 01월 29일과 2002년 02월 28일은 30일의 차이가 나므로, 30일을 무조건 31로 나눈 30/31이 됩니다.

따라서 2번의 경우에는 MONTHS_BETWEEN 함수는 2+30/31을 리턴합니다.

계산해 보시면 아시겠지만, 확실히 30/31=0.96774193548387096774193548387096774194....... 인 값이 나옵니다.

 

이해가 되셨는지 모르겠네요.

 

-- SQL 쿼리 질문은 SQL 까페에서... http://cafe.daum.net/oraclesqltuning

 

WITH T AS
(
SELECT TO_DATE('20120229', 'YYYYMMDD') SDT, TO_DATE('20120329', 'YYYYMMDD') EDT FROM DUAL
UNION ALL SELECT TO_DATE('20120229', 'YYYYMMDD') , TO_DATE('20120330', 'YYYYMMDD')  FROM DUAL
UNION ALL SELECT TO_DATE('20120129', 'YYYYMMDD'), TO_DATE('20120228', 'YYYYMMDD') FROM DUAL
UNION ALL SELECT TO_DATE('20120130', 'YYYYMMDD'), TO_DATE('20120228', 'YYYYMMDD') FROM DUAL
UNION ALL SELECT TO_DATE('20120201', 'YYYYMMDD'), TO_DATE('20120229', 'YYYYMMDD') FROM DUAL
UNION ALL SELECT TO_DATE('20120131', 'YYYYMMDD'), TO_DATE('20120229', 'YYYYMMDD') FROM DUAL
UNION ALL SELECT TO_DATE('20111130', 'YYYYMMDD'), TO_DATE('20120331', 'YYYYMMDD') FROM DUAL
)
SELECT SDT
   , EDT
   , SMM
   , EMM
   , MM
         , CEIL(MONTHS_BETWEEN (EDT+1, SDT)) MM2
         , CEIL(MONTHS_BETWEEN (EDT, SDT)) MM3
   , D1
   , D2
   , DD
   , MM + ROUND (DD / 30) RESULT
         , MM + CEIL (DD / 30) RESULT2
 FROM (SELECT SDT
       , EDT
       , SMM
       , EMM
       , MONTHS_BETWEEN (EMM, SMM) MM
       , (SMM - SDT) D1
       , (EDT - EMM + 1) D2
       , (SMM - SDT) + (EDT - EMM + 1) DD
     FROM (SELECT SDT
           , EDT
           , TRUNC (ADD_MONTHS (SDT - 1, 1), 'MM') SMM
           , TRUNC (EDT + 1, 'MM') EMM
         FROM T))

 

1. 한달하고도 하루가 더 지났기 떄문에 2개월 이라면.

SELECT
CEIL
(
MONTHS_BETWEEN
(
TO_DATE('20050201', 'YYYYMMDD') + 1,
TO_DATE('20050101', 'YYYYMMDD')
)
) MOM
FROM DUAL


 

2. 날짜가 두달에 걸쳐서 있기 때문에 2개월이라면

SELECT
MONTHS_BETWEEN
(
TRUNC(TO_DATE('20050201', 'YYYYMMDD'),'MM'),
TRUNC(TO_DATE('20050131', 'YYYYMMDD'),'MM')
) + 1 MOM
FROM DUAL

 

=======================================================================================================

오라클의 MONTHS_BETWEEN과 비슷하게 처리한 예제

출처 : http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=60334&sca=%BD%BA%C5%A9%B8%B3%C6%AE&page=17

오라클의 add_months, months_between 함수를 자바스크립트로 만들일이 있어서 구현한 것입니다.

처음엔 만들기 귀찮아서.. 스쿨에서 비슷한 함수를 찾아봤는데 제가 못찾아서 그런건지 원래부터 없어서 그런건지 결국 못찾았습니다


간단히 코드 동작에 대해서 설명하자면....

add_months 함수의 경우
2008.01.31에 한달을 더하면 2008.02.29 가 나옵니다.
2008.04.30에 한달을 더하면 2008.05.31 이 나오고요
한달 더 한다고 단순히 기존 날짜에 30일을 더한 것이 아니라
마지막 날짜 여부까지 체크해줍니다.

months_between 함수의 경우
몇개월 차이인가 구하는 함수인데 여기저기 검색해보니
시작날짜, 종료날짜가 몇일인지 구한 후 단순히 30일로 나눠서 몇개월인지 구합니다.
이 경우 몇년 정도의 날짜 차이를 구하면 괜찮지만 몇십년 단위로
날짜 차이를 두면 당연히 몇개월의 오차가 생깁니다.(31일 날도 있어서)
그래서 이런 오차를 없애고자 1개월씩 더해서 루프 돌렸습니다 -_-
자세한 건 코드 보심 될듯싶습니다.

 

<script language="javascript" type="text/javascript">
/*
    오라클의 개월수 더하는 함수와 동일
    ex) add_months('2008.04.04',10);
*/
function add_months(pdate, diff_m) {
    var add_m;
    var lastDay;    // 마지막 날(30,31..)
    var pyear, pmonth, pday;
   
    pdate = makeDateFormat(pdate); // javascript 날짜형변수로 변환
    if (pdate == "") return "";

    pyear = pdate.getYear();
    pmonth= pdate.getMonth() + 1;
    pday  = pdate.getDate();
   
    add_m = new Date(pyear, pmonth + diff_m, 1);    // 더해진 달의 첫날로 셋팅

    lastDay = new Date(pyear, pmonth, 0).getDate(); // 현재월의 마지막 날짜를 가져온다.
    if (lastDay == pday) {  // 현재월의 마지막 일자라면 더해진 월도 마지막 일자로
        pday = new Date(add_m.getYear(), add_m.getMonth(), 0).getDate();
    }

    add_m = new Date(add_m.getYear(), add_m.getMonth()-1, pday);

    return add_m;
}

/*
    오라클의 개월수 차이 구하는 함수와 동일
    ex) months_between('20080404','20060101');
*/
function months_between(edate, sdate) {
    var syear, smonth, sday;
    var eyear, emonth, eday;
    var diff_month = 1;

    sdate = makeDateFormat(sdate); // javascript 날짜형변수로 변환
    edate = makeDateFormat(edate); // javascript 날짜형변수로 변환

    if (sdate == "") return "";
    if (edate == "") return "";

    syear = sdate.getYear();
    eyear = edate.getYear();
    smonth= sdate.getMonth() + 1;
    emonth= edate.getMonth() + 1;
    sday  = sdate.getDate();
    eday  = edate.getDate();

    while (sdate < edate) { // 한달씩 더해서 몇개월 차이 생기는지 검사
        sdate = new Date(syear, smonth - 1 + diff_month, 0);
        diff_month++;
    }

    if (sday > eday) diff_month--;

    diff_month = diff_month - 2;
   
    return diff_month;
}

/* yyyymmdd, yyyy-mm-dd, yyyy.mm.dd 를 javascript 날짜형 변수로 변환 */
function makeDateFormat(pdate) {
    var yy, mm, dd, yymmdd;
    var ar;
    if (pdate.indexOf(".") > -1) {  // yyyy.mm.dd
        ar = pdate.split(".");
        yy = ar[0];
        mm = ar[1];
        dd = ar[2];

        if (mm < 10) mm = "0" + mm;
        if (dd < 10) dd = "0" + dd;
    } else if (pdate.indexOf("-") > -1) {// yyyy-mm-dd
        ar = pdate.split("-");
        yy = ar[0];
        mm = ar[1];
        dd = ar[2];

        if (mm < 10) mm = "0" + mm;
        if (dd < 10) dd = "0" + dd;
    } else if (pdate.length == 8) {
        yy = pdate.substr(0,4);
        mm = pdate.substr(4,2);
        dd = pdate.substr(6,2);
    }

    yymmdd = yy+"/"+mm+"/"+dd;

    yymmdd = new Date(yymmdd);
   
    if (isNaN(yymmdd)) {
        return false;
    }

    return yymmdd;
}

// 4월30일에 1개월 더하기 => 5월31일
alert(add_months('2008.04.30',1));

// 몇개월 차이인지 구하기 => 1104 개월 차이
alert(months_between('2100.01.01','2008.01.01'));


</script>

 


출처 : http://acidsound.blogspot.com/2007/10/oracle10g-xmlagg-group-by-query.html

C군의 질문

A | B
------
A | 가
B | 가
C | 가
a | 나
B | 나
C | 다

일때

가 | ABC
나 | aB
다 | C

를 뽑아낼 수 없을까?

라고 하여 요렇게 조렇게 실험해봤음.
일종의 String Aggregation 인데 http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php 이런 방법도 있지만 왠지 복잡하고 10g 기능을 사용하고 싶어서 뒤져보기 시작.

(10g 가 아니라면 sys_connect_by_path 를 사용하는 방법이 무난. 비용이 좀 든다는 단점이 있다고 한다.)
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM (SELECT deptno,
ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


XMLAGG 로 GROUP BY를 할 수 있다는 사실을 발견

SELECT B, XMLAGG(A) FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

하지만 이렇게 하면 오류가 난다.
타입이 맞지 않기 때문이다. 인자를 XML형으로 바꾸기 위해 XMLELEMENT를 사용한다.
컬럼에 C라는 Tag 명을 붙여서 보자.

SELECT B, XMLAGG(XMLELEMENT(C,A)) A FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

결과는 나오지만 충분하지 않다. 실제로 Application 에서 사용하려면 XML 타입이라 맞지 않기 때문이다.
GETSTRINGVAL()을 붙여서 형변환을 해준다.

SELECT B, XMLAGG(XMLELEMENT(C,A)).GETSTRINGVAL() A
FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

B | A
가 | ABC
나 | aB
다 | C

원하는 결과가 나왔다.
Tag 을 제거하자 기왕 xml도 건든 김에 정규표현식으로 제거해보자.


SELECT B, REGEXP_REPLACE(XMLAGG(XMLELEMENT(C,A)).GETSTRINGVAL(), '<[C/]+>', '') A
FROM (
SELECT 'A' A, '가' B FROM DUAL UNION ALL
SELECT 'B' , '가' FROM DUAL UNION ALL
SELECT 'C' , '가' FROM DUAL UNION ALL
SELECT 'a' , '나' FROM DUAL UNION ALL
SELECT 'B' , '나' FROM DUAL UNION ALL
SELECT 'C' , '다' FROM DUAL)
GROUP BY B

B | A
------
가 | ABC
나 | aB
다 | C


정규식을 사용하지 않고 태그 제거하기
WITH
TABLE AS (
SELECT 이름 FROM 유저목록
)
SELECT
LTRIM(SUBSTR(XMLAGG(XMLELEMENT(이름,'|'||이름 )).EXTRACT('//text()').GetStringVal(), 2))
FROM TABLE)

======================================================================================================


출처 : http://database.sarang.net/?inc=read&aid=38771&criteria=oracle&subcrit=&id=&limit=20&keyword=SYS_CONNECT_BY_PATH&page=1

 

with tbl as

(
select 1 seq , '20110801' ymd, 't' gubun, 'input1' title from dual
union select 2 seq , '20110802' ymd, 't' gubun, 'input2' title from dual
union select 3 seq , '20110802' ymd, 't' gubun, 'input3' title from dual
union select 4 seq , '20110802' ymd, 't' gubun, 'input4' title from dual
union select 5 seq , '20110802' ymd, 't' gubun, 'input5' title from dual
union select 6 seq , '20110804' ymd, 'c' gubun, 'input6' title from dual
)
select
ymd,wm_concat(seq||'||'||title) as seq_title
from
tbl
group by ymd

 

제가 할려는게 같은 날짜의 title 를 seq 순서대로 뽑아내는건대요

위에 쿼리를 돌려보면

20110801 1||input1
20110802 2||input2,4||input4,5||input5,3||input3 <== 여기가 문제임
20110804 6||input6

이런식으로 나옵니다. 즉 2,3,4,5 이런순으로 나와야 하는대 엉뚱하게 3이 젤 나중에 나와버립니다.

어떻게 해야 할까요? 도움 주시면 감사하겠습니다...

 

wm_concat 간단하면서도 강력한 기능 정말 좋은데...
한가지 아쉬운 점이 정렬 기능이 없다는 거죠...
XmlAgg(9i) 나 ListAgg(11g) 를 이용하시면 됩니다.

SELECT ymd
, SUBSTR(XMLAGG(XMLELEMENT(x, ',', seq, '||', title) ORDER BY seq)
.EXTRACT('//text()'), 2) v_9i
, wm_concat(seq||'||'||title) v_10g
, LISTAGG(seq||'||'||title, ',') WITHIN GROUP(ORDER BY seq) v_11g
FROM tbl
GROUP BY ymd
ORDER BY ymd
;

wm_concat를 이용해 정렬하고자 한다면..
인라인뷰에서 분석함수의 정렬기능을 이용하신후 밖에서 걸러내시면 됩니다.

SELECT ymd
, seq_title
FROM (SELECT ymd, seq
, wm_concat(seq||'||'||title)
OVER(PARTITION BY ymd ORDER BY seq) seq_title
, MAX(seq) OVER(PARTITION BY ymd) max_seq
FROM tbl
)
WHERE seq = max_seq
ORDER BY ymd
;

Connect_By_Path(9i)를 이용하는 방법도 있는데 더 복잡하고 성능도 안좋아요.

SELECT ymd
, SUBSTR(SYS_CONNECT_BY_PATH(seq||'||'||title, ','), 2) seq_title
FROM (SELECT ymd, seq, title
, ROW_NUMBER() OVER(PARTITION BY ymd ORDER BY seq) rn
, COUNT(*) OVER(PARTITION BY ymd) cnt
FROM tbl
)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR ymd = ymd
AND PRIOR rn + 1 = rn
; 

 

 

+ Recent posts

티스토리툴바