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	질의작성	각 테이블 PK 유무 확인 쿼리문	엄기호	2016.01.01	9012
53	질의작성	ORACLE TRIGGER를 CUBRID TRIGGER로 변환하기	김창휘	2015.12.31	6460
52	질의작성	Cubrid는 어떤 쿼리를 동일한 쿼리로 판단할까?	이상신	2015.12.30	4010
51	질의작성	일정 범위 임의의 정수 생성 방법(random 함수 응용)	손승일	2015.12.22	6472
50	질의작성	오라클의 DBMS_RANDOM.STRING() 함수를 CUBRID 식으로 구현하는 방법	이경오	2015.12.08	7973
49	질의작성	테이블 용량 산정 쿼리	성진	2015.12.08	7398
»	질의작성	전체 테이블 UPDATE시 SELECT, UPDATE를 통한 INDEX SCAN방식으로 테이블 X_LOCK을 방지하자!	이경오	2015.08.13	13794
47	질의작성	INSERT수행 시, 현재날짜,현재시각으로 자동 입력하는 방법	주현	2015.07.08	25201
46	질의작성	CUBRID dummy data생성-간단편	주현	2015.07.01	7858
45	질의작성	카디시안(한 Row를 여러 Row) 쿼리문 만들기 1	엄기호	2015.06.30	10927
44	질의작성	SQL튜닝 - 인덱스 활용 사례	권호일	2015.06.04	12013
43	질의작성	A테이블의 다수의 컬럼을 B테이블로 UPDATE 하는 SQL	김승훈	2015.05.07	7411
42	질의작성	MERGE INTO 사용 예제 ( INSERT, UPDATE문을 1개의 쿼리로 실행 )	권호일	2015.05.01	21252
41	질의작성	오류데이타검증 - 숫자 이외의 데이타 확인하는 방법	권호일	2013.01.17	21121
40	질의작성	join update 처리방법입니다.(연관성 있는 테이블을 조인하여 처리하는 UPDATE 구문)	권호일	2012.11.30	20392
39	질의작성	게시판 응용 중 조회수로 정렬하는 경우 인덱스 생성 방법 1	손승일	2012.06.23	13905
38	질의작성	문자(char, varchar)로 설계한 날짜데이타 검증하기	권호일	2012.04.27	17829
37	질의작성	세부내역과 소계를 한개의 쿼리문장으로 수행하는 SQL	권호일	2012.03.31	14092
36	질의작성	한건의 데이타를 여러건으로 조회하는 쿼리입니다.	권호일	2012.03.30	9419
35	질의작성	여러건의 코드명을 한건으로 조회하는 쿼리입니다. 1	권호일	2012.03.30	10755

분류

글쓴이

질의작성

각 테이블 PK 유무 확인 쿼리문

엄기호

2016.01.01

9012

질의작성