oracle

Oracle レンジパーティションサンプル

レンジパーティションテーブルの作成

レンジパーティションテーブル作成DDL
CREATE TABLE PTRENGEEX01
   (    
    SID      NUMBER(19,0),
    SNAME    VARCHAR2(32 CHAR),
    BIRTHDAY DATE,
    NOTE     VARCHAR2(256 CHAR)
   )
PARTITION BY RANGE (BIRTHDAY)
(
 PARTITION DAY1   VALUES LESS THAN ( TO_DATE('1990/12/31','YYYY/MM/DD') )
,PARTITION DAY2   VALUES LESS THAN ( TO_DATE('2000/12/31','YYYY/MM/DD') )
,PARTITION DAY3   VALUES LESS THAN ( TO_DATE('2010/12/31','YYYY/MM/DD') )
,PARTITION DAY4   VALUES LESS THAN ( maxvalue                           )
) 
 NOLOGGING
;
-- PKを作成
ALTER TABLE PTRENGEEX01 ADD CONSTRAINT "PTRENGEEX01_PK" PRIMARY KEY ("SID")
;
作成確認SQL

以下はSOONIユーザでテーブル作成した場合の例です。

select t.TABLE_OWNER,t.TABLE_NAME,t.PARTITION_NAME,t.HIGH_VALUE,t.LOGGING from DBA_TAB_PARTITIONS
t where t.TABLE_NAME = 'PTRENGEEX01'
and t.TABLE_OWNER = 'SOONI'
;
作成確認SQL実行例
                          PARTITIO
TABLE_OW TABLE_NAME       NAME     HIGH_VALUE                                               LOGGIN
-------- ---------------- -------- -------------------------------------------------------- ------
SOONI    PTRENGEEX01      DAY1     TO_DATE(' 1990-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NO
                                   , 'NLS_CALENDAR=GREGORIAN')

SOONI    PTRENGEEX01      DAY2     TO_DATE(' 2000-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NO
                                   , 'NLS_CALENDAR=GREGORIAN')

SOONI    PTRENGEEX01      DAY3     TO_DATE(' 2010-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NO
                                   , 'NLS_CALENDAR=GREGORIAN')

SOONI    PTRENGEEX01      DAY4     MAXVALUE                                                 NO
set lin 200
COLUMN TABLE_OWNER FORMAT A8
COLUMN TABLE_NAME FORMAT A16
COLUMN PARTITION_NAME FORMAT A8
COLUMN PARTITION_NAME HEADING 'PARTITION|NAME'
COLUMN LOGGING FORMAT A6
COLUMN HIGH_VALUE FORMAT A56

動作検証用データ準備

--
-- テストデータ投入
insert into PTRENGEEX01 (SID, SNAME, BIRTHDAY, NOTE)
values(1,'松本',to_date('1990/10/31','YYYY/MM/DD'),'お人よしで小心ものですが案外ドライ');
insert into PTRENGEEX01 (SID, SNAME, BIRTHDAY, NOTE)
values(2,'竹内',to_date('1990/12/31','YYYY/MM/DD'),'頭も良いしスポーツもできる');
insert into PTRENGEEX01 (SID, SNAME, BIRTHDAY, NOTE)
values(3,'梅田',to_date('2000/12/31','YYYY/MM/DD'),'堅実ですが打算的すぎるところあり');
insert into PTRENGEEX01 (SID, SNAME, BIRTHDAY, NOTE)
values(4,'東根',to_date('2010/12/31','YYYY/MM/DD'),'ビジュアル素晴らしくスポーツ万能');
insert into PTRENGEEX01 (SID, SNAME, BIRTHDAY, NOTE)
values(5,'西川',to_date('2011/12/31','YYYY/MM/DD'),'スポーツは少し苦手ですが美術の才能高い');
insert into PTRENGEEX01 (SID, SNAME, BIRTHDAY, NOTE)
values(6,'南田',to_date('2012/01/01','YYYY/MM/DD'),'非常に賢いが常識に欠けるところあり');
commit;
--
-- 投入データ確認
SQL> select sid,sname,to_char(birthday,'YYYY-MM-DD') birthday,note from PTRENGEEX01 order by 1;

       SID SNAME      BIRTHDAY             NOTE
---------- ---------- -------------------- ----------------------------------------
         1 松本       1990-10-31           お人よしで小心ものですが案外ドライ
         2 竹内       1990-12-31           頭も良いしスポーツもできる
         3 梅田       2000-12-31           堅実ですが打算的すぎるところあり
         4 東根       2010-12-31           ビジュアル素晴らしくスポーツ万能
         5 西川       2011-12-31           スポーツは少し苦手ですが美術の才能高い
         6 南田       2012-01-01           非常に賢いが常識に欠けるところあり

6行が選択されました。

SQL> 
set lin 200
COLUMN SNAME FORMAT A10
COLUMN NOTE FORMAT A40

パーティション名で条件を指定可能

案外知らない方いますが、パーティション名で条件を絞る事も可能です。

SELECT * FROM PTRENGEEX01 PARTITION (DAY3)
;
SQL> SELECT * FROM PTRENGEEX01 PARTITION (DAY3);

       SID SNAME      BIRTHDAY            NOTE
---------- ---------- ------------------- ----------------------------------------
         3 梅田       2000-12-31 00:00:00 堅実ですが打算的すぎるところあり

SQL>

パーティション単位でTRUNCATE可能

パーティションを利用するとパーティション単位でTRUNCATEできます。TRUNCATE対象以外のパーティションについては通常通り利用し続ける事できる点非常に便利です。このあたり設計者が強く意識しておかないといけない点と感じています。

パーティション単位でTRUNCATE実行
ALTER TABLE PTRENGEEX01 TRUNCATE PARTITION DAY1 UPDATE GLOBAL INDEXES
;
--
-- DAY3 パーティションのみをtruncate
--
SQL> ALTER TABLE PTRENGEEX01 TRUNCATE PARTITION DAY3 UPDATE GLOBAL INDEXES
  2  ;

表が切り捨てられました。

--
-- DAY3パーティションのみtruncateされた事を確認
--
SQL> select sid,sname,to_char(birthday,'YYYY-MM-DD') birthday,note from PTRENGEEX01 order by 1
  2  ;

       SID SNAME    BIRTHDAY             NOTE
---------- -------- -------------------- ----------------------------------------
         1 松本     1990-10-31           お人よしで小心ものですが案外ドライ
         2 竹内     1990-12-31           頭も良いしスポーツもできる
         4 東根     2010-12-31           ビジュアル素晴らしくスポーツ万能
         5 西川     2011-12-31           スポーツは少し苦手ですが美術の才能高い
         6 南田     2012-01-01           非常に賢いが常識に欠けるところあり

SQL>
スポンサーリンク