[SAPHANA] SAP HANA DB 테이블 파티셔닝

SAP HANA DB 파티셔닝 쿼리

 *** HANA DB Alert 에서 Partition 대상 정보 확인.

[Schema Name].BSEG partition (0) contains 1859007519 records. 
A table partition cannot contain more than 2,147,483,648 (2 bilion) rows.

 

1) 파티션 테이블 정보 확인

SELECT * FROM M_TABLE_PARTITIONS WHERE TABLE_NAME='[테이블명]'

 

2) HASH 파티션

ALTER TABLE SAPHANADB.[테이블명] PARTITION BY HASH ([기준컬럼]) PARTITIONS 12;

 

3) 진행 상황 조회 (Job Progress)

SELECT * FROM SYS.M_JOB_PROGRESS

 

4) 결과 조회

 

------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------

2-1) RANGE 파티션

ALTER TABLE SAPHANADB.ACDOCA PARTITION BY
RANGE (RBUKRS)
(PARTITION VALUE= 'G111',
 PARTITION VALUE= 'R100',
 PARTITION VALUE= 'R103',
 PARTITION VALUE= 'R106',
 PARTITION VALUE= 'R107',
 PARTITION OTHERS),
RANGE (GJAHR)
(PARTITION VALUE = '2020',
 PARTITION VALUE = '2021',
 PARTITION VALUE = '2022',
 PARTITION VALUE = '2023',
 PARTITION VALUE = '2024',
 PARTITION VALUE = '2025',
 PARTITION VALUE = '2026',
 PARTITION VALUE = '2027',
 PARTITION VALUE = '2028',
 PARTITION VALUE = '2029',
 PARTITION VALUE = '2030',
 PARTITION OTHERS)

ALTER TABLE SAPHANADB.CKMLCR PARTITION BY
RANGE (BDATJ)
(PARTITION VALUE = '2019',
PARTITION VALUE = '2020',
PARTITION VALUE = '2021',
PARTITION VALUE = '2022',
PARTITION VALUE = '2023',
PARTITION VALUE = '2024',
PARTITION VALUE = '2025',
PARTITION VALUE = '2026',
PARTITION VALUE = '2027',
PARTITION VALUE = '2028',
PARTITION VALUE = '2029',
PARTITION VALUE = '2030',
PARTITION OTHERS),
RANGE (POPER)
(PARTITION VALUE='001',
PARTITION VALUE='002',
PARTITION VALUE='003',
PARTITION VALUE='004',
PARTITION VALUE='005',
PARTITION VALUE='006',
PARTITION VALUE='007',
PARTITION VALUE='008',
PARTITION VALUE='009',
PARTITION VALUE='010',
PARTITION VALUE='011',
PARTITION VALUE='012',
PARTITION OTHERS)

ALTER TABLE SAPHANADB.CKMLPP PARTITION BY
RANGE (BDATJ)
(PARTITION VALUE = '2019',
PARTITION VALUE = '2020',
PARTITION VALUE = '2021',
PARTITION VALUE = '2022',
PARTITION VALUE = '2023',
PARTITION VALUE = '2024',
PARTITION VALUE = '2025',
PARTITION VALUE = '2026',
PARTITION VALUE = '2027',
PARTITION VALUE = '2028',
PARTITION VALUE = '2029',
PARTITION VALUE = '2030',
PARTITION OTHERS),
RANGE (POPER)
(PARTITION VALUE='001',
PARTITION VALUE='002',
PARTITION VALUE='003',
PARTITION VALUE='004',
PARTITION VALUE='005',
PARTITION VALUE='006',
PARTITION VALUE='007',
PARTITION VALUE='008',
PARTITION VALUE='009',
PARTITION VALUE='010',
PARTITION VALUE='011',
PARTITION VALUE='012',
PARTITION OTHERS)

 

  * 소요시간 

Statement 'ALTER TABLE SAPHANADB.ACDOCA PARTITION BY RANGE (RBUKRS) 
(PARTITION VALUE= 'G111', PARTITION VALUE= ...' 
successfully executed in 21:00.248 minutes  (server processing time: 21:00.110 minutes) 
-- Rows Affected: 0 (4억 7천만)

Statement 'ALTER TABLE SAPHANADB.CKMLCR PARTITION BY RANGE (BDATJ) 
(PARTITION VALUE = '2019', PARTITION VALUE ...' 
successfully executed in 44:35.501 minutes  (server processing time: 44:35.510 minutes) 
-- Rows Affected: 0 (11억건)

Statement 'ALTER TABLE SAPHANADB.CKMLPP PARTITION BY RANGE (BDATJ) 
(PARTITION VALUE = '2019', PARTITION VALUE ...' 
successfully executed in 39:36.768 minutes  (server processing time: 39:36.776 minutes) 
-- Rows Affected: 0 (11억건)

 

작업 진행 상황 확인

SELECT * FROM sys.M_JOB_PROGRESS