CUBRID | FAQ & TIP - 전체 테이블 UPDATE시 SELECT, UPDATE를 통한 INDEX SCAN방식으로 테이블 X

1. 요구사항 예시

CUBRID의 초기 설치 데이터베이스인 demodb의 participant테이블에 rank칼럼을 추가하여 개최 년도 별 국가의 순위를 일괄 update하는 문을 작성하시오.

AS-IS

host_year

nation_code

gold

silver

bronze

rank

2004

USA

(NULL)

2004

CHN

(NULL)

2004

RUS

(NULL)

2004

AUS

(NULL)

2004

JPN

(NULL)

TO-BE

host_year

nation_code

gold

silver

bronze

rank

2004

USA

2004

CHN

2004

RUS

2004

AUS

2004

JPN

2. 칼럼 추가

1
2
ALTER TABLE participant 
ADD COLUMN RANK INTEGER;
cs

3. AS-IS SQL

실제 업무에서 아래와 같이 구현된 SQL로 인해 성능부하가 일어남(테이블 X_LOCK 회피 불가)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
UPDATE participant a
SET a.rank = (
                    SELECT c.rank
                    FROM 
                    (                
                        SELECT 
                        b.host_year, b.nation_code,
                        RANK() OVER(PARTITION BY HOST_YEAR ORDER BY GOLD DESC, SILVER DESC, bronze DESC) rank
                    FROM participant b
                    ) c
                    WHERE c.host_year = a.host_year 
                    AND c.nation_code = a.nation_code
                    )
WHERE EXISTS (
                        SELECT '1'
                        FROM 
                        (
                            SELECT 
                                d.host_year, d.nation_code
                            FROM participant     d
                        ) e
                        WHERE a.host_year = e.host_year
                        AND a.nation_code = e.nation_code
                    );
 
Colored by Color Scripter
cs

-실행계획 설명

participant 테이블을 3번이나 full scan하였음 -> i/o 증가, 비효율

update시에 participant테이블이 full scan되었으로 테이블 전체 X_LOCK이 걸림 -> 동시성 저하

4. TO-BE SQL

4.1 UPDATE 조인문 활용

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
UPDATE 
participant a, 
(
SELECT
            b.host_year,
            b.nation_code,
            RANK() OVER(PARTITION BY HOST_YEAR ORDER BY GOLD DESC, SILVER DESC, bronze DESC) RANK
        FROM
            participant b
 
) b
SET a.rank = b.rank
WHERE a.host_year = b.host_year
AND a.nation_code = b.nation_code
;
Colored by Color Scripter
cs

-실행계획 설명

participant 테이블을 1번만 full Scan하였음

update시 participant 테이블이 index scan되었으로 성능향상

SQL에 대한 가독성이 향상되어 유지보수에 용이함

4.2 MERGE INTO문 활용

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MERGE
INTO
    participant A
USING
    (
        SELECT
            b.host_year,
            b.nation_code,
            RANK() OVER(PARTITION BY HOST_YEAR ORDER BY GOLD DESC, SILVER DESC, bronze DESC) RANK
        FROM
            participant b
    ) B ON (
        A.HOST_YEAR = B.HOST_YEAR
        AND A.NATION_CODE = B.NATION_CODE
    )
    WHEN MATCHED THEN
        UPDATE
        SET
            A.RANK = B.RANK;
 
Colored by Color Scripter
cs

-실행계획 설명

participant 테이블을 1번만 full Scan하였음

update시 participant 테이블이 index scan되었으로 성능향상

SQL에 대한 가독성이 향상되어 유지보수에 용이함

즉 4.1방식과 4.2방식 모두 사용 가능함

5. 결론

전체를 UPDATE하는 질의문은 테이블 X_LOCK이 잡히지 않도록 SELECT한 대상 집합을 기준으로 하여 INDEX SCAN을 통한 업데이트를 해야 한다.

Atachment
첨부 '3'	1.jpg, 2.jpg, 3.jpg,

List of Articles
번호	분류	제목	글쓴이	날짜	조회 수
119	운영관리	CUBRID JAVA Stored Procedure의 GC로그 출력방법	janus	2009.12.01	15315
118	운영관리	CUBRID 2008 삭제시 주의사항	seongjoon	2009.04.01	15288
117	질의작성	시스템 테이블을 이용한 인덱스 생성 구문 만들기	janus	2009.12.02	15143
116	응용개발	jdbc에서 bit 데이터 타입 사용하기	손승일	2009.04.11	15013
115	운영관리	CUBRID 2008 R2.0 RPM 설치후 PHP모듈 로딩에 실패할 경우	Prototype	2009.12.10	14918
114	응용개발	PHP에서 prepared statement 사용시 BIND 관련 팁	Prototype	2009.06.30	14843
113	응용개발	전체 레코드 개수 확인하는 SP	김승훈	2015.05.07	14726
112	CUBRID 매니저	CUBRID Manager의 host 및 질의편집기 설정 저장 위치	seongjoon	2010.04.01	14709
111	기타	2008.11.18. 최신 우편번호부 with CUBRID	Prototype	2009.03.17	14482
110	CUBRID 매니저	CUBRID에서 여러개의 질의 결과 확인하는 방법	seongjoon	2010.01.02	14440
109	운영관리	windows 환경에서 Manager 접속이 되지 않는 경우 처리 방안	남재우	2010.11.26	14423
108	운영관리	데이터 입력 중 디스크 공간 부족 오류가 발생하였을 때, 복구 방법	이용미	2012.06.30	14331
107	기타	windows에서의 Build 오류 발생 시 조치방법	cubebridge	2009.05.19	14255
106	질의작성	세부내역과 소계를 한개의 쿼리문장으로 수행하는 SQL	권호일	2012.03.31	14092
105	응용개발	cubrid-php module r2.2 이상 버젼에서 configure시에 주의사항	seongjoon	2010.07.20	14007
104	질의작성	게시판 응용 중 조회수로 정렬하는 경우 인덱스 생성 방법 1	손승일	2012.06.23	13905
»	질의작성	전체 테이블 UPDATE시 SELECT, UPDATE를 통한 INDEX SCAN방식으로 테이블 X_LOCK을 방지하자!	이경오	2015.08.13	13794
102	응용개발	PHP 프로그램을 작성할때 주의할 점	Prototype	2009.07.01	13728
101	CUBRID 매니저	CUBRID Manager 메모리 설정하기(cubridmanager.ini)	성진	2015.04.24	13707
100	운영관리	리눅스 쉘프롬프트 상에서 질의 바로 수행하기	Prototype	2009.12.10	13640

분류

글쓴이

119

운영관리

CUBRID JAVA Stored Procedure의 GC로그 출력방법

janus

2009.12.01

15315