Background Image
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄 첨부

1. 요구사항 예시


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


AS-IS

host_year

nation_code

gold

silver

bronze

rank

2004

USA

36

39

27

(NULL)

2004

CHN

32

17

14

(NULL)

2004

RUS

27

27

38

(NULL)

2004

AUS

17

16

16

(NULL)

2004

JPN

16

9

12

(NULL)


TO-BE

host_year

nation_code

gold

silver

bronze

rank

2004

USA

36

39

27

1

2004

CHN

32

17

14

2

2004

RUS

27

27

38

3

2004

AUS

17

16

16

4

2004

JPN

16

9

12

5


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
                    );
 
cs


1.jpg


-실행계획 설명

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
;
cs


2.jpg


-실행계획 설명

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;
 
cs


3.jpg


-실행계획 설명

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

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

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


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


5. 결론


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







TAG •

List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
239 마이그레이션 MySQL 테이블 크기 조사하기-공유 주현 2015.12.31 6197
238 운영관리 CUBRID BACKUP (linux, window) file 박동윤 2015.12.30 6218
237 질의작성 Cubrid는 어떤 쿼리를 동일한 쿼리로 판단할까? 이상신 2015.12.30 4006
236 운영관리 브로커와 DB 간 연결 테스트 박동윤 2015.12.29 4924
235 응용개발 CUBRID HA 제약 사항 및 확인 방법 손승일 2015.12.29 5293
234 운영관리 cubrid plandump ( plan cache정보확인) file 주현 2015.12.29 4292
233 마이그레이션 CUBRID와 Oracle의 NULL과 '' (empty string)의 처리 차이점 1 권호일 2015.12.29 15340
232 마이그레이션 CM(CUBRID Manager)을 이용하여 행정표준코드시스템의 기관코드 가져오기 file 권호일 2015.12.28 6325
231 CUBRID 매니저 테이블 명세서 한 시트로 합치기 file 성진 2015.12.24 16475
230 질의작성 일정 범위 임의의 정수 생성 방법(random 함수 응용) 손승일 2015.12.22 6454
229 운영관리 시스템 부하가 많지 않음에도 불구하고 질의 수행이 느린 경우 THP 기능 설정 확인 및 해제 진우진 2015.12.16 7507
228 운영관리 큐브리드 서비스 재구동 시 cubrid manager server start: fail 오류 해결 방법 진우진 2015.12.16 7691
227 응용개발 maven에서 CUBRID JDBC Driver 추가하기 file 김승훈 2015.12.09 8902
226 질의작성 오라클의 DBMS_RANDOM.STRING() 함수를 CUBRID 식으로 구현하는 방법 이경오 2015.12.08 7959
225 질의작성 테이블 용량 산정 쿼리 성진 2015.12.08 7371
224 응용개발 WHERE 조건에서 다중 컬럼 IN절 처리 최적화 방법 (cubrid + ibatis) 1 이상신 2015.08.21 55065
223 운영관리 브로커 동적 파라미터 변경 방법 정만영 2015.08.13 7555
» 질의작성 전체 테이블 UPDATE시 SELECT, UPDATE를 통한 INDEX SCAN방식으로 테이블 X_LOCK을 방지하자! file 이경오 2015.08.13 13770
221 운영관리 CUBRID HA 구성 후 구동중인 slave/replica node에서 write 하기 정만영 2015.07.15 9032
220 질의작성 INSERT수행 시, 현재날짜,현재시각으로 자동 입력하는 방법 주현 2015.07.08 25102
Board Pagination Prev 1 2 3 4 5 6 7 8 9 10 ... 14 Next
/ 14

Contact Cubrid

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