클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
Interval-Reference Partition 0 0 12,437

by 구루비스터디 Interval-Reference Partition Partition Referenace partition [2023.09.03]


Reference partition 이란?

  • 11g 에서 등장한 파티션 방식
  • 부모-자식 관계의 테이블이 있을 때 자식 테이블에는 파티션 정의 없어도 부모 테이블의 파티션 구조를 따라 생성 됨


일반 Referenace partition

  • 11g/12c 에서 사용 가능

SQL> create table orders (
order_id number
,name varchar2(30)
,order_date date
)
partition by range (order_date)
(
partition p_1 values less than (to_date('2015/01/01','yyyy/mm/dd')),
partition p_2 values less than (to_date('2016/01/01','yyyy/mm/dd')),
partition p_3 values less than (to_date('2017/01/01','yyyy/mm/dd'))
);

Statement Processed.

SQL> alter table orders add constraint pk_orders primary key (order_id);

Statement Processed.

SQL> create table order_items (
order_id number not null
,product_id number
,sales_cmount number
,constraint order_items_fk foreign key (order_id) references orders (order_id)  on delete cascade
)
partition by reference (order_items_fk);

Statement Processed.

SQL> insert into orders select object_id, object_name, created from dba_objects;

90816 rows Inserted.

SQL> commit;

Commit complete.

SQL> insert into order_items select object_id, namespace, data_object_id from dba_objects;

90812 rows Inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_Table_stats('TESTUSER','ORDERS')

PL/SQL executed.

SQL> exec dbms_stats.gather_Table_stats('TESTUSER','ORDER_ITEMS')

PL/SQL executed.


SQL> select table_name, partition_name, high_value, num_rows
from user_tab_partitions where table_name in ('ORDERS','ORDER_ITEMS');

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDER_ITEMS                    P_3                                                                                                                     50
ORDER_ITEMS                    P_2                                                                                                                      9
ORDER_ITEMS                    P_1                                                                                                                  90753
ORDERS                         P_3                            TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         54
ORDERS                         P_2                            TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA          9
ORDERS                         P_1                            TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      90753

6 rows selected.

SQL>
SQL> SELECT
TABLE_NAME, PARTITIONING_TYPE, REF_PTN_CONSTRAINT_NAME, INTERVAL
FROM USER_PART_TABLES
WHERE TABLE_NAME IN ('ORDERS','ORDER_ITEMS')
;

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME        INTERVAL
------------------------------ --------- ------------------------------ --------------------------------------------------
ORDERS                         RANGE
ORDER_ITEMS                    REFERENCE ORDER_ITEMS_FK

SQL>


부모 테이블이 Interval partition 일 경우의 Referenace partition

  • 12c 에서만 사용 가능

SQL> create table orders (
order_id number
,name varchar2(30)
,order_date date
)
partition by range (order_date) interval (numtodsinterval(7,'DAY'))
(partition p_1 values less than (to_date('2015/01/01','yyyy/mm/dd'))
);

Statement Processed.

SQL> alter table orders add constraint pk_orders primary key (order_id);

Statement Processed.

SQL> create table order_items (
order_id number not null
,product_id number
,sales_cmount number
,constraint order_items_fk foreign key (order_id) references orders (order_id)  on delete cascade
)
partition by reference (order_items_fk);

Statement Processed.

SQL> insert into orders select object_id, object_name, created from dba_objects where owner != 'TESTUSER';

90793 rows Inserted.

SQL> commit;

Commit complete.

SQL> insert into order_items select object_id, namespace, data_object_id from dba_objects where owner != 'TESTUSER';

90793 rows Inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_Table_stats('TESTUSER','ORDERS')

PL/SQL executed.

SQL> exec dbms_stats.gather_Table_stats('TESTUSER','ORDER_ITEMS')

PL/SQL executed.

SQL> select
table_name, partition_name, high_value, num_rows
from user_tab_partitions where table_name in ('ORDERS','ORDER_ITEMS');  2    3

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDER_ITEMS                    SYS_P523                                                                                                                14
ORDER_ITEMS                    SYS_P522                                                                                                                 9
ORDER_ITEMS                    SYS_P521                                                                                                                17
ORDER_ITEMS                    P_1                                                                                                                  90753
ORDERS                         SYS_P523                       TO_DATE(' 2016-06-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         14
ORDERS                         SYS_P522                       TO_DATE(' 2015-04-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA          9
ORDERS                         SYS_P521                       TO_DATE(' 2016-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         17
ORDERS                         P_1                            TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      90753

8 rows selected.

SQL> SELECT
TABLE_NAME, PARTITIONING_TYPE, REF_PTN_CONSTRAINT_NAME, INTERVAL
FROM USER_PART_TABLES
WHERE TABLE_NAME IN ('ORDERS','ORDER_ITEMS')
;

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME        INTERVAL
------------------------------ --------- ------------------------------ --------------------------------------------------
ORDERS                         RANGE                                    NUMTODSINTERVAL(7,'DAY')
ORDER_ITEMS                    REFERENCE ORDER_ITEMS_FK                 YES

SQL>


  • 이 경우 11g 에서는 아래와 같은 에러 발생
SQL> create table order_items (
order_id number not null
,product_id number
,sales_cmount number
,constraint order_items_fk foreign key (order_id) references orders (order_id)  on delete cascade
)
partition by reference (order_items_fk)
;

ORA-14659: Partitioning method of the parent table is not supported


"구루비 데이터베이스 스터디모임" 에서 2016년에 "클라우드 데이터베이스 Oracle 12c 가이드" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4261

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

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