[ORACLE] USER_TAB_SUBPARTITIONS : 서브 파티션 구조 완전 정복

[ORACLE] USER_TAB_SUBPARTITIONS

1. USER_TAB_SUBPARTITIONS란?

USER_TAB_SUBPARTITIONS는 오라클 데이터베이스에서 **복합 파티셔닝(composite partitioning)**을 사용하는 테이블의 서브파티션 정보를 확인할 수 있는 뷰입니다. 이 뷰는 사용자 스키마에서 소유한 테이블 중 **서브파티션이 정의된 테이블의 구조를 분석하고 최적화하는 데 매우 중요한 역할**을 합니다. 일반적으로 Range-Hash 또는 List-Hash 파티셔닝과 같은 복합 구조를 사용하는 경우, 성능 개선을 위해 서브파티션을 도입하며, 이 구조의 관리와 진단에 USER_TAB_SUBPARTITIONS 뷰가 사용됩니다.

2. 주요 컬럼 설명

컬럼명	설명
TABLE_NAME	서브파티션이 속한 테이블 이름
PARTITION_NAME	서브파티션이 속한 상위 파티션 이름
SUBPARTITION_NAME	서브파티션 이름
SUBPARTITION_POSITION	서브파티션 순서 (정렬 기준)
TABLESPACE_NAME	서브파티션이 저장된 테이블스페이스
NUM_ROWS	서브파티션 내 행 수 (통계 수집 후에만 정확)

3. 실전 활용 예제

3.1. 특정 테이블의 서브파티션 구조 확인

SELECT 
  SUBPARTITION_NAME, 
  PARTITION_NAME, 
  TABLESPACE_NAME 
FROM 
  USER_TAB_SUBPARTITIONS 
WHERE 
  TABLE_NAME = 'SALES_DATA';
    

3.2. 서브파티션별 행 수가 많은 순 정렬

SELECT 
  SUBPARTITION_NAME, 
  NUM_ROWS 
FROM 
  USER_TAB_SUBPARTITIONS 
WHERE 
  TABLE_NAME = 'SALES_DATA' 
ORDER BY 
  NUM_ROWS DESC;
    

3.3. 테이블스페이스 분산도 분석

SELECT 
  TABLESPACE_NAME, 
  COUNT(*) AS SUBPARTITION_COUNT 
FROM 
  USER_TAB_SUBPARTITIONS 
GROUP BY 
  TABLESPACE_NAME;
    

4. 파티셔닝 계층 구조 비교

구성 요소	설명	관련 뷰
Table	파티셔닝이 적용된 기본 테이블	USER_TABLES
Partition	첫 번째 계층의 파티션	USER_TAB_PARTITIONS
Subpartition	두 번째 계층의 세분화된 파티션	USER_TAB_SUBPARTITIONS

5. USER_TAB_SUBPARTITIONS 활용 전략

• 성능 모니터링: NUM_ROWS를 이용한 서브파티션별 데이터 집중도 확인
• 스토리지 최적화: 테이블스페이스 분산 여부를 진단하여 I/O 병목 해결
• 자동화 리포트: 서브파티션 구조를 주기적으로 CSV 또는 시각화하여 분석팀 공유
• 문제 진단: 특정 서브파티션에서만 발생하는 쿼리 지연 현상을 분석할 수 있음

6. 주의사항 및 팁

• 통계 갱신 필수: NUM_ROWS 데이터는 DBMS_STATS.GATHER_TABLE_STATS 실행 후 신뢰 가능
• 테이블 이름 대소문자 주의: 조회 시 모두 대문자로 사용
• 서브파티션 없는 테이블 제외: 단일 또는 단일 파티션 테이블은 이 뷰에서 조회되지 않음

7. 결론

USER_TAB_SUBPARTITIONS는 오라클 복합 파티셔닝 구조를 효율적으로 설계하고 성능을 진단하는 데 핵심적인 역할을 합니다. 특히 대규모 데이터 환경에서는 서브파티션 단위의 최적화가 전체 쿼리 성능에 큰 영향을 미치므로, 이 뷰를 활용한 정기적인 점검과 통계 기반 분석이 매우 중요합니다.

8. 출처

• Oracle Corporation. Oracle Database Reference
• Richard Niemiec, 『Oracle Performance Tuning Tips & Techniques』