클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
Interval-Reference Partition
0
0
35,975
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
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.