1.参照パーティション表をつくる
親子関係を持つ表は、親表のパーティションキーを継承して論理的にパーティション化してくれる。
キー列を複製しなくてもいい。
＜例＞
・（親）orders表
・（子）order_items表
・orders表にあるorder_date列をorder_items表が外部参照している。
→orders表がorder_date列をキーとしてパーティション化されている場合、参照パーティション表を使えばorder_items表も親と同じ単位でパーティション化できる。
#準備
SYS@PROD1 > show parameter NLS_DATE_LANGUAGE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_language string JAPANESE
#親表の作成
SH@PROD1 > conn hr/hr
接続されました。
HR@PROD1 > CREATE TABLE orders2
2 ( order_id NUMBER(12),
3 order_date date,
4 customer_id NUMBER(6),
5 order_total NUMBER(8,2),
6 sales_rep_id NUMBER(6),
7 CONSTRAINT orders_pk PRIMARY KEY(order_id)
8 )
9 PARTITION BY RANGE(order_date)
10 ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-4月-2013','dd-MON-yyyy')),
11 PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-7月-2013','dd-MON-yyyy')),
12 PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-10月-2013','dd-MON-yyyy')),
13 PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-1月-2014','dd-MON-yyyy'))
14 );
表が作成されました。
#子表の作成。参照パーティション化のため「PARTITION BY REFERENCE」が入る。
CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);
#確認。
HR@PROD1 > set linesize 300
HR@PROD1 > SELECT table_name, partitioning_type, ref_ptn_constraint_name FROM user_part_tables;
TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- ------------------------------
ORDERS RANGE
ORDER_ITEMS REFERENCE ORDER_ITEMS_FK
set linesize 400
HR@PROD1 > r
1 SELECT table_name, partition_name, high_value,tablespace_name FROM user_tab_partitions;
2*
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ORDER_ITEMS★ Q4_2005
ORDER_ITEMS★ Q3_2005
ORDER_ITEMS★ Q2_2005
ORDER_ITEMS★ Q1_2005
ORDERS Q4_2005 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS Q3_2005 TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS Q2_2005 TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS Q1_2005 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
8行が選択されました。
#削除。
HR@PROD1 > drop table order_items purge;
表が削除されました。
#明示的に表領域が指定されない場合は同じ表領域。明示的に指定するとちゃんと変わることを確認する。
#また、パーティション名を明示的に指定しない場合は、親と同じパーティション名になる。明示的に指定した場合は、その指定したパーティション名になることを確認する。
CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
tablespace par_tmp★
PARTITION BY REFERENCE(order_items_fk);
HR@PROD1 > SELECT table_name, partition_name, high_value,tablespace_name FROM user_tab_partitions;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------
ORDER_ITEMS Q4_2005 PAR_TMP★
ORDER_ITEMS Q3_2005 PAR_TMP★
ORDER_ITEMS Q2_2005 PAR_TMP★
ORDER_ITEMS Q1_2005 PAR_TMP★
ORDERS Q4_2005 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS◎
ORDERS Q3_2005 TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS◎
ORDERS Q2_2005 TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS◎
ORDERS Q1_2005 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS◎
8行が選択されました。
→元表はデフォルト表領域のUSERS,参照パーティションは指定したPAR_TMP表領域に乗っかっていることが確認できた。
HR@PROD1 > CREATE TABLE order_items
2 ( order_id NUMBER(12) NOT NULL,
3 line_item_id NUMBER(3) NOT NULL,
4 product_id NUMBER(6) NOT NULL,
5 unit_price NUMBER(8,2),
6 CONSTRAINT order_items_fk
7 FOREIGN KEY(order_id) REFERENCES orders(order_id)
8 )
9 PARTITION BY REFERENCE(order_items_fk)
10 (PARTITION hoge1,PARTITION hoge2,PARTITION hoge3,PARTITION hoge4);★
表が作成されました。
HR@PROD1 > SELECT table_name, partition_name, high_value,tablespace_name FROM user_tab_partitions;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------
ORDER_ITEMS HOGE4★ USERS
ORDER_ITEMS HOGE3★ USERS
ORDER_ITEMS HOGE2★ USERS
ORDER_ITEMS HOGE1★ USERS
ORDERS Q4_2005 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
ORDERS Q3_2005 TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
ORDERS Q2_2005 TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
ORDERS Q1_2005 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA USERS
8行が選択されました。
→パーティション名が「HOGE」になっていることを確認できた。
2. 時間隔パーティションの作成
レンジパーティションの一種。完全自動化でレンジパーティションを作ってくれる。操作の自動化という意味で楽ちん。例えば月単位でパーティションします という定義をした時間隔パーティション表を作成すると、
パーティションを明示的に作成しなくても自動で必要な月のパーティションをデータ insert 時に作成してくれる。
#作成
CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
※参照パーティションのときは、親を PARTITION BY RANGE(order_date)でふつうにつくって子でPARTITION BY REFERENCE(order_items_fk);だった。
今度は、親とか子とかそういうのではないので、
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))でOK。
#確認
SH@PROD1 > SELECT table_name, partitioning_type FROM user_part_tables;
TABLE_NAME PARTITION
------------------------------ ---------
COSTS RANGE
INTERVAL_SALES RANGE
SALES RANGE★
SELECT table_name, partition_name, high_value FROM user_tab_partitions ORDER BY table_name, partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
INTERVAL_SALES P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
#データを1件入れて、パーティションが自動で追加されるかを確認
SH@PROD1 > INSERT INTO interval_sales(prod_id, time_id) VALUES(1, TO_DATE('2013-10-07', 'YYYY-MM-DD'));
1行が作成されました。
SELECT table_name, partition_name, high_value FROM user_tab_partitions ORDER BY table_name, partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
INTERVAL_SALES P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES SYS_P41★ TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
#先ほどは「MONTH」の単位でやったので、今度は「YEAR」でやる。
CREATE TABLE interval_sales2
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'★))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
#データを挿入し年単位でパーティションが自動作成されるかを確認する
INSERT INTO interval_sales2(prod_id, time_id) VALUES(1, TO_DATE('2013-10-07', 'YYYY-MM-DD'));
INSERT INTO interval_sales2(prod_id, time_id) VALUES(1, TO_DATE('2013-12-07', 'YYYY-MM-DD'));
INSERT INTO interval_sales2(prod_id, time_id) VALUES(1, TO_DATE('2014-03-07', 'YYYY-MM-DD'));
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
INTERVAL_SALES2 P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 SYS_P42 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_SALES2 SYS_P43 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
＜参考マニュアル＞
Oracle® Database VLDBおよびパーティショニング・ガイド
11g リリース2 (11.2)
http://docs.oracle.com/cd/E16338_01/server.112/b56316/part_admin001.htm#BAJHFFBE
0 件のコメント:
コメントを投稿