6장. DML 튜닝

6.3 파티션을 활용한 DML 튜닝

테이블 파티션

오라클 파티션테이블은 하나의 큰 테이블을 물리적으로 나눠놓은 것입니다.

물리적으로 나눴지만 논리적으로는 하나의 테이블로 간주됩니다.

왼쪽 그림처럼 Sales 테이블에 1월데이터부터 5월데이터까지 하나의 통테이블에 몰아서 넣을수 있습니다.

하지만, 파티션테이블을 사용하게되면 오른쪽 그림처럼 월마다 다른 세그먼트에 Sales 데이터를 나눠서 넣을 수 있습니다.

이렇게 나눠서 넣어놔도 사용자는 1월~5월치 데이터가 마치 하나의 Sales 테이블에 들어있는 것처럼 사용할 수 있습니다.

 

Range Partition 

Range Partition 은 범위로 구분되는 파티션 테이블, 범위(Range)에는 숫자, 날짜, 문자가 모두 가능

참조 : https://jack-of-all-trades.tistory.com/65?category=831949 

오라클 Range Partition (범위로 구분되는 파티션 테이블)

 

List Partition

List Partition 은 범위가 아닌 특정한 값으로 구분되는 파티션 테이블. 주로 특정 구분자로 데이터의 구분이 가능한 경우 사용

참조 : https://jack-of-all-trades.tistory.com/66?category=831949 

오라클 List Partition (특정한 값으로 구분되는 파티션 테이블)

 

Hash Partition

Hash Partition 은 해시함수에 의해 자동으로 데이터가 분할되는 파티션 테이블. 숫자, 문자, 날짜 타입 모두 다 가능

https://jack-of-all-trades.tistory.com/67?category=831949 

오라클 Hash Partition (해쉬함수에 의한 파티션 테이블)

 

인덱스 파티션

• 비파티션 테이블(Non-Partitioned Table)

• 파티션 테이블(Partitioned Table)

비파티션 인덱스(Non-Partitioned Index) = 글로벌 비파티션 인덱스

-파티셔닝하지 않은 인덱스

-1:M 관계

-하나의 인덱스 세그먼트가 여러 테이블 파티션 세그먼트와 관계

-기준 테이블의 파티션 구성에 변경이 생길 때마다 인덱스가 unusable 상태로 바뀌고 그때마다 인덱스를 재생성 필요

 

글로벌 파티션 인덱스(Global Partitioned Index) = 글로벌 인덱스

-테이블 파티션과 독립적인 구성을 갖도록 파티셔닝하는 것

-테이블은 파티셔닝돼 있지 않을 수도 있다.

-기준 테이블의 파티션 구성에 변경이 생길 때마다 인덱스가 unusable 상태로 바뀌고 그때마다 인덱스를 재생성 필요

 

로컬 파티션 인덱스(Local Partitioned Index) = 로컬 인덱스

-각 인덱스 파티션이 테이블 파티션과 1:1 대응 관계를 가지며, 테이블 파티션 속성을 그대로 상속받는다. 

-파티션 키를 사용자가 따로 정의하지 않아도 오라클이 자동으로 관리

-테이블 파티션 구성에 변경이 생기더라도 인덱스를 재생성할 필요가 없다.

-관리편의성

Prefixed vs. Nonprefixed

인덱스 파티션 키 컬럼이 인덱스 구성상 왼쪽 선두 컬럼에 위치하는지에 따른 구분

Prefixed       : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것. 

Nonprefixed : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 주지 않는 것.

 

--주문일자 기준으로 분기별 Range partition table  생성 스크립트
create table 주문 (주문번호 number, 주문일자 varchar(20), 고객ID varchar(5), ...)
partition by range(주문일자)(
     partition P2017_Q1 values less than ('20170401') 
    ,partition P2017_Q2 values less than ('20170401')
    ,partition P2017_Q3 values less than ('20171001')
    ,partition P2017_Q4 values less than ('20180101')
    ,partition P9999_MX values less than ( MAXVALUE )
);

--파티션인덱스생성 스크립트 (파티션키=주문일자)
create index 주문_x01 on 주문 (주문일자, 주문금액) local; --Prefixed 

create index 주문_x02 on 주문 (고객ID,   주문일자) local; --NonPrefixed 

create index 주문_x03 on 주문 (주문금액, 주문일자) global --Prefixed (global partition index는 Prefiexd 파티션만 지원)
partition by range(주문금액)(
     partition P_Q1 values less than ( 10000000 )
    ,partition P_MX values less than ( MAXVALUE )
);

create index 주문_x04 on 주문 (주문ID, 배송일자); --N/A

 

인덱스 파티션 제약

Unique 인덱스를 파티셔닝 하려면, 파티션 키가 모두 인덱스 구성 컬럼이어야 한다.
DML 성능 보장을 위해 당연히 있어야 할 제약조건이다.

 

파티션을 활용한 대량 UPDATE 튜닝

 

파티션을 활용한 대량 DELETE 튜닝

 

파티션을 활용한 대량 INSERT 튜닝

 

 

---

lock 메커니즘

Exclusive lock (배타적 잠금) = LX

쓰기 잠금(Write lock)이라고도 불린다.

어떤 트랜잭션에서 데이터를 변경하고자 할 때(ex . 쓰고자 할 때) 해당 트랜잭션이 완료될 때까지 해당 테이블 혹은 레코드(row)를 다른 트랜잭션에서 읽거나 쓰지 못하게 하기 위해 Exclusive lock을 걸고 트랜잭션을 진행시키는  것이다.

=> exclusive lock에 걸리면 shared lock을 걸 수 없다. 

=> exclusive lock에 걸린 테이블,레코드등의 자원에 대해 다른 트랜잭션이 exclusive lock을 걸 수 없다.

=> Exclusive Lock, 공유할 수 없는 락이다.

=> 무조건 하나만 걸 수 있다.

=> LX는 LS, LX 어떠한 락이라도 허용하지 않는다.

=> 쓰기에 사용된다.

Shared lock (공유 잠금) = LS

읽기 잠금(Read lock)이라고도 불린다.

어떤 트랜잭션에서 데이터를 읽고자 할 때 다른 shared lock은 허용이 되지만 exclusive lock은 불가하다.

쉽게 말해 리소스를 다른 사용자가 동시에 읽을 수 있게 하되 변경은 불가하게 하는 것이다.

=> 어떤 자원에 shared lock이 동시에 여러개 적용될 수 있다.

=> 어떤 자원에 shared lock이 하나라도 걸려있으면 exclusive lock을 걸 수 없다.

=> Shared Lock, 공유할 수 있는 락이다.

=> 즉 LS가 걸려있을 때 다른 LS를 걸 수 있다.

=> 바꿔말하면 LS끼리는 Block 되지 않는다.

=> 읽기에 사용된다.

 

Lock의 설정 범위(Level)

Lock의 설정 범위(Level)에 대해서 알아보도록 하겠습니다.

1. 데이터베이스 - 전체 데이터베이스를 기준으로 Lock이 걸립니다. 1개의 세션이 하나의 데이터베이스의 데이터에 접근할 수 있습니다. DB 전체에 영향이 있는 DB 업데이트와 같은 작업에서만 사용합니다.
2. 파일 - 데이터베이스 파일을 기준으로 Lock이 걸립니다. 데이터베이스에서 파일이란, 테이블 등과 같이 실제 데이터가 쓰여지는 물리적인 저장소를 뜻합니다. 파일 전체를 백업할 때 사용합니다.
3. 테이블 - 테이블 기준으로 Lock이 걸립니다. 전체 테이블의 대한 데이터 변경이 있을 경우 사용합니다. 테이블을 제어하는 DDL 구문을 사용할 때 Lock이 걸린다고 DDL Lock이라고도 합니다.
4. 페이지와 블럭 - 파일을 구성하는 페이지와 블록을 기준으로 Lock이 걸립니다.
5. 컬럼(Column) - 컬럼 기준으로 Lock이 걸립니다. 다만 Lock 설정 및 해제 시 리소스가 많이 듭니다. 그래서 잘 사용하지 않습니다.
6. 행(Row) - 행 수준의 Lock을 겁니다. 가장 많이 사용되는 Lock 입니다.