데이터베이스 질문과 답변

오라클 RANGE + HASH 파티션 문법 0 1 1,796

by webma [Oracle Admin] [2020.01.13 23:40:29]

안녕하세요..

요즘 파티션 관련해서 질문을 많이 하면서 공부를 하게 되네요..

도움 주신분들 모두 감사 드립니다..

테이블을 RANGE 파티션을 하고 SUB파티션으로 HASH 로 나눌려고 하는데요

도서와 온라인을 검색해보니 다들 아래와 같은 방법으로 나누어져 있더라고요..

CREATE TABLE TEST
(
    REG_DT VARCHAR2(14) NOT NULL,
    SRNO    NUMBER(13) NOT NULL
)
TABLESPACE USER
PARTITION BY RANGE (REG_DT)
SUBPARTITION BY HASH(SRNO) SUBPARTITIONS 8
(
    PARTITION P_20200101 VALUES LESS THAN ('20200102000000') TABLESPACE USER,
    PARTITION P_20200102 VALUES LESS THAN ('20200103000000') TABLESPACE USER,
    PARTITION P_20200103 VALUES LESS THAN ('20200104000000') TABLESPACE USER,
    PARTITION P_20200104 VALUES LESS THAN ('20200105000000') TABLESPACE USER,
    PARTITION P_20200105 VALUES LESS THAN ('20200106000000') TABLESPACE USER,
    PARTITION P_20200106 VALUES LESS THAN ('20200107000000') TABLESPACE USER,
    PARTITION P_20200107 VALUES LESS THAN ('20200108000000') TABLESPACE USER,
    PARTITION P_20200108 VALUES LESS THAN ('20200109000000') TABLESPACE USER,
    PARTITION P_20200109 VALUES LESS THAN ('20200110000000') TABLESPACE USER,
    PARTITION P_20200110 VALUES LESS THAN ('20200111000000') TABLESPACE USER
);

그런데 검색을 해 보니 아래와 같은 문법도 있던데 차이점이 뭔지 궁금해서요..

CREATE TABLE TEST
(
    REG_DT VARCHAR2(14) NOT NULL,
    SRNO    NUMBER(13) NOT NULL
)
TABLESPACE USER
COMPRESS FOR OLTP
PARTITION BY RANGE (REG_DT)
SUBPARTITION BY HASH(SRNO) SUBPARTITIONS 8
(
    PARTITION P_20200101 VALUES LESS THAN ('20200102000000')
    TABLESPACE USER
    (
        SUBPARTITION P_20200101_H_01 TABLESPACE USER,
        SUBPARTITION P_20200101_H_02 TABLESPACE USER,
        SUBPARTITION P_20200101_H_03 TABLESPACE USER,
        SUBPARTITION P_20200101_H_04 TABLESPACE USER,
        SUBPARTITION P_20200101_H_05 TABLESPACE USER,
        SUBPARTITION P_20200101_H_06 TABLESPACE USER,
        SUBPARTITION P_20200101_H_07 TABLESPACE USER,
        SUBPARTITION P_20200101_H_08 TABLESPACE USER,
        SUBPARTITION P_20200101_H_09 TABLESPACE USER,
        SUBPARTITION P_20200101_H_10 TABLESPACE USER
    ),
    PARTITION P_20200102 VALUES LESS THAN ('20200103000000')
    TABLESPACE USER
    (
        SUBPARTITION P_20200102_H_01 TABLESPACE USER,
        SUBPARTITION P_20200102_H_02 TABLESPACE USER,
        SUBPARTITION P_20200102_H_03 TABLESPACE USER,
        SUBPARTITION P_20200102_H_04 TABLESPACE USER,
        SUBPARTITION P_20200102_H_05 TABLESPACE USER,
        SUBPARTITION P_20200102_H_06 TABLESPACE USER,
        SUBPARTITION P_20200102_H_07 TABLESPACE USER,
        SUBPARTITION P_20200102_H_08 TABLESPACE USER,
        SUBPARTITION P_20200102_H_09 TABLESPACE USER,
        SUBPARTITION P_20200102_H_10 TABLESPACE USER
    ),
    PARTITION P_20200103 VALUES LESS THAN ('20200104000000')
    TABLESPACE USER
    (
        SUBPARTITION P_20200103_H_01 TABLESPACE USER,
        SUBPARTITION P_20200103_H_02 TABLESPACE USER,
        SUBPARTITION P_20200103_H_03 TABLESPACE USER,
        SUBPARTITION P_20200103_H_04 TABLESPACE USER,
        SUBPARTITION P_20200103_H_05 TABLESPACE USER,
        SUBPARTITION P_20200103_H_06 TABLESPACE USER,
        SUBPARTITION P_20200103_H_07 TABLESPACE USER,
        SUBPARTITION P_20200103_H_08 TABLESPACE USER,
        SUBPARTITION P_20200103_H_09 TABLESPACE USER,
        SUBPARTITION P_20200103_H_10 TABLESPACE USER
    ),
    PARTITION P_20200104 VALUES LESS THAN ('20200105000000')
    TABLESPACE USER
    (
        SUBPARTITION P_20200104_H_01 TABLESPACE USER,
        SUBPARTITION P_20200104_H_02 TABLESPACE USER,
        SUBPARTITION P_20200104_H_03 TABLESPACE USER,
        SUBPARTITION P_20200104_H_04 TABLESPACE USER,
        SUBPARTITION P_20200104_H_05 TABLESPACE USER,
        SUBPARTITION P_20200104_H_06 TABLESPACE USER,
        SUBPARTITION P_20200104_H_07 TABLESPACE USER,
        SUBPARTITION P_20200104_H_08 TABLESPACE USER,
        SUBPARTITION P_20200104_H_09 TABLESPACE USER,
        SUBPARTITION P_20200104_H_10 TABLESPACE USER
    ),
    PARTITION P_20200105 VALUES LESS THAN ('20200106000000')
    TABLESPACE USER
    (
        SUBPARTITION P_20200105_H_01 TABLESPACE USER,
        SUBPARTITION P_20200105_H_02 TABLESPACE USER,
        SUBPARTITION P_20200105_H_03 TABLESPACE USER,
        SUBPARTITION P_20200105_H_04 TABLESPACE USER,
        SUBPARTITION P_20200105_H_05 TABLESPACE USER,
        SUBPARTITION P_20200105_H_06 TABLESPACE USER,
        SUBPARTITION P_20200105_H_07 TABLESPACE USER,
        SUBPARTITION P_20200105_H_08 TABLESPACE USER,
        SUBPARTITION P_20200105_H_09 TABLESPACE USER,
        SUBPARTITION P_20200105_H_10 TABLESPACE USER
    ),
     PARTITION P_20200106 VALUES LESS THAN ('20200107000000')
    TABLESPACE USER
    (
        SUBPARTITION P_20200106_H_01 TABLESPACE USER,
        SUBPARTITION P_20200106_H_02 TABLESPACE USER,
        SUBPARTITION P_20200106_H_03 TABLESPACE USER,
        SUBPARTITION P_20200106_H_04 TABLESPACE USER,
        SUBPARTITION P_20200106_H_05 TABLESPACE USER,
        SUBPARTITION P_20200106_H_06 TABLESPACE USER,
        SUBPARTITION P_20200106_H_07 TABLESPACE USER,
        SUBPARTITION P_20200106_H_08 TABLESPACE USER,
        SUBPARTITION P_20200106_H_09 TABLESPACE USER,
        SUBPARTITION P_20200106_H_10 TABLESPACE USER
    ),
    PARTITION P_20200107 VALUES LESS THAN ('20200108000000')
    TABLESPACE USER
    (
        SUBPARTITION P_20200107_H_01 TABLESPACE USER,
        SUBPARTITION P_20200107_H_02 TABLESPACE USER,
        SUBPARTITION P_20200107_H_03 TABLESPACE USER,
        SUBPARTITION P_20200107_H_04 TABLESPACE USER,
        SUBPARTITION P_20200107_H_05 TABLESPACE USER,
        SUBPARTITION P_20200107_H_06 TABLESPACE USER,
        SUBPARTITION P_20200107_H_07 TABLESPACE USER,
        SUBPARTITION P_20200107_H_08 TABLESPACE USER,
        SUBPARTITION P_20200107_H_09 TABLESPACE USER,
        SUBPARTITION P_20200107_H_10 TABLESPACE USER
    ),
    PARTITION P_20200108 VALUES LESS THAN ('20200109000000')
    TABLESPACE USER
    (
        SUBPARTITION P_20200108_H_01 TABLESPACE USER,
        SUBPARTITION P_20200108_H_02 TABLESPACE USER,
        SUBPARTITION P_20200108_H_03 TABLESPACE USER,
        SUBPARTITION P_20200108_H_04 TABLESPACE USER,
        SUBPARTITION P_20200108_H_05 TABLESPACE USER,
        SUBPARTITION P_20200108_H_06 TABLESPACE USER,
        SUBPARTITION P_20200108_H_07 TABLESPACE USER,
        SUBPARTITION P_20200108_H_08 TABLESPACE USER,
        SUBPARTITION P_20200108_H_09 TABLESPACE USER,
        SUBPARTITION P_20200108_H_10 TABLESPACE USER
    ),
    PARTITION P_20200109 VALUES LESS THAN ('20200110000000')
    TABLESPACE USER
    (
        SUBPARTITION P_20200109_H_01 TABLESPACE USER,
        SUBPARTITION P_20200109_H_02 TABLESPACE USER,
        SUBPARTITION P_20200109_H_03 TABLESPACE USER,
        SUBPARTITION P_20200109_H_04 TABLESPACE USER,
        SUBPARTITION P_20200109_H_05 TABLESPACE USER,
        SUBPARTITION P_20200109_H_06 TABLESPACE USER,
        SUBPARTITION P_20200109_H_07 TABLESPACE USER,
        SUBPARTITION P_20200109_H_08 TABLESPACE USER,
        SUBPARTITION P_20200109_H_09 TABLESPACE USER,
        SUBPARTITION P_20200109_H_10 TABLESPACE USER
    )

);    

파티션을 조회해 보니 PATITION_NAME과 데이터는 같은거 같은데 두개의 차이점이 무엇이고, 성능상 어떤것이 더 좋은지 궁금해서 이렇게 질문을 드립니다.

현업에서는 어떤걸 더 쓰고 있는지도 궁금하네요..

 

by chrome [2020.01.14 14:28:32]

원하는 이름을 지정할 수 있습니다.

서브 파티션까지 명명규칙을 지정한곳도 있지만, 11g이하에선 이름 길이 제한 때문에 

제약되는 경우가 많기 때문에

자동지정을 쓰기도 합니다.(이걸 선호)

 

굳이 파티션과 서브 파티션에 테이블 이름까지 붙여가며 파티션 단독으로 쓰는 DDL이 없기 때문에

(원테이블이 지정되고 파티션명이 지정됨)

자동지정을 선호하죠.(개인적)

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입