SAP HANA 테이블 파티셔닝

파티셔닝 관련 유용한 쿼리

해당 테이블의 파티션 정보 확인

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

 

HASH 파티션 하기

alter table SAPHANADB.PRCD_ELEMENTS PARTITION BY HASH (KNUMV) PARTITIONS 12;

alter table SAPHANADB.MBEWH PARTITION BY HASH (MATNR) PARTITIONS 12;

alter table SAPHANADB.ACCTCR PARTITION BY HASH (AWREF) PARTITIONS 12;

소요시간

Statement 'alter table SAPHANADB.PRCD_ELEMENTS PARTITION BY HASH (KNUMV) PARTITIONS 12'
successfully executed in 10:06.782 minutes  (server processing time: 10:06.779 minutes) - Rows Affected: 0
약 4억건

Statement 'alter table SAPHANADB.MBEWH PARTITION BY HASH (MATNR) PARTITIONS 12' 
successfully executed in 24:53.557 minutes  (server processing time: 24:53.560 minutes) - Rows Affected: 0 
약 10억건

Statement 'alter table SAPHANADB.ACCTCR PARTITION BY HASH (AWREF) PARTITIONS 12' 
successfully executed in 11:51.099 minutes  (server processing time: 11:51.057 minutes) - Rows Affected: 0 
약 5억 6천만건

RANGE 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억건

 

JOB PROGRESS

SELECT * FROM sys.M_JOB_PROGRESS