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
번호	분류	제목	글쓴이	날짜	조회 수
54	질의작성	join update 처리방법입니다.(연관성 있는 테이블을 조인하여 처리하는 UPDATE 구문)	권호일	2012.11.30	20395
53	질의작성	오라클의 order by 시 first와 last 대체 사용법	이용미	2011.12.22	20179
52	질의작성	다중 row를 하나의 column에 넣기	정만영	2009.03.13	20089
51	질의작성	select 결과중 몇개만 가져오는 방법	admin	2008.11.21	19921
50	질의작성	CUBRID에서 대소문자 관리	남재우	2010.01.30	19510
49	질의작성	예제를 이용한 중복데이터 삭제	janus	2009.12.02	18541
48	질의작성	데이터 필드의 타입 변경하기	admin	2008.11.21	18420
47	질의작성	문자(char, varchar)로 설계한 날짜데이타 검증하기	권호일	2012.04.27	17831
46	질의작성	중복된 데이터에 대하여 일련번호를 붙이는 방법	admin	2008.11.21	17363
45	질의작성	중복된 데이터를 1개만 남기고 지우는 방법	admin	2008.11.21	17307
44	질의작성	CUBRID 예약어 사용방법	정만영	2009.12.09	17183
43	질의작성	요일 값을 반환 받을 수 있는 질의 형태	정만영	2009.03.13	17108
42	질의작성	CUBRID설치 후 default설정 상태에서 Query Plan보기(CSQL모드)	cubebridge	2009.12.31	16507
41	질의작성	CUBRID에서 응용프로그램 쿼리 binding 구문이 있을 경우 plan의 차이	cubebridge	2009.11.06	15701
40	질의작성	스키마에서 한글 사용하는 방법	seongjoon	2009.04.14	15362
39	질의작성	시스템 테이블을 이용한 인덱스 생성 구문 만들기	janus	2009.12.02	15143
38	질의작성	세부내역과 소계를 한개의 쿼리문장으로 수행하는 SQL	권호일	2012.03.31	14092
37	질의작성	게시판 응용 중 조회수로 정렬하는 경우 인덱스 생성 방법 1	손승일	2012.06.23	13907
»	질의작성	전체 테이블 UPDATE시 SELECT, UPDATE를 통한 INDEX SCAN방식으로 테이블 X_LOCK을 방지하자!	이경오	2015.08.13	13802
35	질의작성	Integer 컬럼의 결과에 + or – 연산을 할 때 주의해야 할 점	웁쓰	2009.12.31	13493

분류

글쓴이

질의작성

join update 처리방법입니다.(연관성 있는 테이블을 조인하여 처리하는 UPDATE 구문)

권호일

2012.11.30

20395

질의작성