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 |
-실행계획 설명
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 |
-실행계획 설명
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 |
-실행계획 설명
participant 테이블을 1번만 full Scan하였음
update시 participant 테이블이 index scan되었으로 성능향상
SQL에 대한 가독성이 향상되어 유지보수에 용이함
즉 4.1방식과 4.2방식 모두 사용 가능함
5. 결론
전체를 UPDATE하는 질의문은 테이블 X_LOCK이 잡히지 않도록 SELECT한 대상 집합을 기준으로 하여 INDEX SCAN을 통한 업데이트를 해야 한다.
