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을 통한 업데이트를 해야 한다.

DOCUMENTS

전체 테이블 UPDATE시 SELECT, UPDATE를 통한 INDEX SCAN방식으로 테이블 X_LOCK을 방지하자!

단축키

단축키

MySQL 테이블 크기 조사하기-공유

CUBRID BACKUP (linux, window)

Cubrid는 어떤 쿼리를 동일한 쿼리로 판단할까?

브로커와 DB 간 연결 테스트

CUBRID HA 제약 사항 및 확인 방법

cubrid plandump ( plan cache정보확인)

CUBRID와 Oracle의 NULL과 '' (empty string)의 처리 차이점

CM(CUBRID Manager)을 이용하여 행정표준코드시스템의 기관코드 가져오기

테이블 명세서 한 시트로 합치기

일정 범위 임의의 정수 생성 방법(random 함수 응용)

시스템 부하가 많지 않음에도 불구하고 질의 수행이 느린 경우 THP 기능 설정 확인 및 해제

큐브리드 서비스 재구동 시 cubrid manager server start: fail 오류 해결 방법

maven에서 CUBRID JDBC Driver 추가하기

오라클의 DBMS_RANDOM.STRING() 함수를 CUBRID 식으로 구현하는 방법

테이블 용량 산정 쿼리

WHERE 조건에서 다중 컬럼 IN절 처리 최적화 방법 (cubrid + ibatis)

브로커 동적 파라미터 변경 방법

전체 테이블 UPDATE시 SELECT, UPDATE를 통한 INDEX SCAN방식으로 테이블 X_LOCK을 방지하자!

CUBRID HA 구성 후 구동중인 slave/replica node에서 write 하기

INSERT수행 시, 현재날짜,현재시각으로 자동 입력하는 방법

Contact Cubrid

대표전화 070-4077-2110 / 기술문의 070-4077-2113 / 영업문의 070-4077-2112 / Email. contact_at_cubrid.com

Site Map