Oracle Partition Interval 자동추가 테스트

반응형

-- Partition Interval 자동추가 테스트

-- 테이블추가

CREATE TABLE sales_data_test2

(

    sale_id NUMBER,

    sale_date DATE,

    amount NUMBER

)

PARTITION BY RANGE (sale_date)

INTERVAL (INTERVAL '1' MONTH)

(

    PARTITION sales_jan VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')),

    PARTITION sales_feb VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD'))

);



-- 인덱스생성

CREATE INDEX IDX_1 ON  sales_data_test2(sale_id);



-- SALES_DATA_TEST2 테이터 삽입

INSERT INTO sales_data_test2 (sale_id,sale_date,amount) VALUES (2,TO_DATE('2024-02-02', 'YYYY-MM-DD'), 1500);

INSERT INTO sales_data_test2 (sale_id,sale_date,amount) VALUES (3,TO_DATE('2024-03-03', 'YYYY-MM-DD'), 2000);

INSERT INTO sales_data_test2 (sale_id,sale_date,amount) VALUES (4,TO_DATE('2024-05-05', 'YYYY-MM-DD'), 1200);

commit;

-- 4월데이터를 제외하고 5월 데이터를 삽입

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, TABLESPACE_NAME

FROM user_tab_partitions

WHERE table_name = 'SALES_DATA_TEST2';



TABLE_NAME    PARTITION_NAME    HIGH_VALUE    PARTITION_POSITION    TABLESPACE_NAME

SALES_DATA_TEST2    SALES_JAN    TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    1    USERS

SALES_DATA_TEST2    SALES_FEB    TO_DATE(' 2024-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    2    USERS

SALES_DATA_TEST2    SYS_P454    TO_DATE(' 2024-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    3    USERS

SALES_DATA_TEST2    SYS_P455    TO_DATE(' 2024-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    4    USERS

-- 5월 파티션이 생성되지 않는것을 확인할수 있습니다.



SELECT OWNER, INDEX_NAME , STATUS  FROM DBA_INDEXES

WHERE INDEX_NAME='IDX_1';

--  글로벌인덱스 상태확인 VALID 상태입니다.

-- OWNER    INDEX_NAME    STATUS

TUNING    IDX_1    VALID





-- 4월데이터 삽입

INSERT INTO sales_data_test2 (sale_id,sale_date,amount) VALUES (4,TO_DATE('2024-04-02', 'YYYY-MM-DD'), 1200);

commit;

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, TABLESPACE_NAME

FROM user_tab_partitions

WHERE table_name = 'SALES_DATA_TEST2';



--  글로벌인덱스 상태확인 VALID

OWNER    INDEX_NAME    STATUS

TUNING    IDX_1    VALID



OWNER    INDEX_NAME    STATUS

TUNING    IDX_1    VALID



TABLE_NAME    PARTITION_NAME    HIGH_VALUE    PARTITION_POSITION    TABLESPACE_NAME

SALES_DATA_TEST2    SALES_JAN    TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    1    USERS

SALES_DATA_TEST2    SALES_FEB    TO_DATE(' 2024-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    2    USERS

SALES_DATA_TEST2    SYS_P454    TO_DATE(' 2024-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    3    USERS

SALES_DATA_TEST2    SYS_P456    TO_DATE(' 2024-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    4    USERS

SALES_DATA_TEST2    SYS_P455    TO_DATE(' 2024-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    5    USERS



--글로벌 인덱스 안깨지며  중간에 들어온 4월데이터에 대해서 파티션이 추가된다.





-- 내부적으로 생성순서 확인



select * from dba_objects

where object_name='SALES_DATA_TEST2';



OWNER    OBJECT_NAME        SUBOBJECT_NAME    OBJECT_ID    DATA_OBJECT_ID    OBJECT_TYPE    CREATED

TUNING    SALES_DATA_TEST2    SALES_FEB    77334        77334            TABLE PARTITION    2024/04/25 11:24:41

TUNING    SALES_DATA_TEST2    SALES_JAN    77333        77333            TABLE PARTITION    2024/04/25 11:24:41

TUNING    SALES_DATA_TEST2    SYS_P454    77336        77336            TABLE PARTITION    2024/04/25 11:25:00

TUNING    SALES_DATA_TEST2    SYS_P455    77337        77337            TABLE PARTITION    2024/04/25 11:25:00 -- 2024-06-01 00:00:00 파티션

TUNING    SALES_DATA_TEST2    SYS_P456    77338        77338            TABLE PARTITION    2024/04/25 11:27:43 -- 2024-05-01 00:00:00 파티션