Go to Qiita Advent Calendar Top
LoginSignup
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@500InternalServerErrorin日本オラクル株式会社

[OCI]Autonomous Databaseでハイブリッド・パーティション表を作成して、内部パーティションを外部パーティションに移行してみた

Last updated at Posted at 2022-03-03

はじめに

Autonomous Database内の既存の表をハイブリッド・パーティション表に移行し、さらに、内部パーティションを外部パーティション化する手順を検証してみました。
例えば、時系列のデータを格納する表をハイブリッド・パーティション化することで、参照頻度の低いデータを安価なObject Storageに移動しつつ、これまで同様に単一の表として検索対象に含めることができるようになります。

なお、今回検証した手順では OCIのリソース・プリンシパルを使用していますので、こちらのドキュメントを参考にして、あらかじめユーザーに対して、Autonomous Databaseインスタンスのリソース・プリンシパルを有効にしておく必要があります。

1. 既存の表の確認

今回は以下のような構成のlineorder表をハイブリッド・パーティション表にします。

SQL> desc lineorder
 Name					                   Null?    Type
 ----------------------------------------- -------- ----------------------------
 LO_ORDERDATE					                    DATE
 LO_REVENUE					                        NUMBER

lineorder表には360,000レコードが格納されており、2019年、2020年、2021年のデータがそれぞれ120,000レコードあります。

SQL> SELECT COUNT(*) FROM lineorder;

  COUNT(*)
----------
    360000

SQL> SELECT TO_CHAR(lo_orderdate,'YYYY') year,count(lo_orderdate)
  2  FROM lineorder
  3  GROUP BY TO_CHAR(lo_orderdate,'YYYY');

YEAR COUNT(LO_ORDERDATE)
---- -------------------
2019		  120000
2020		  120000
2021		  120000

SQL>

2.ハイブリッド・パーティション表作成の準備

DBMS_CLOUD.EXPORT_DATAでlineorder表内の2019年のデータをCSV形式でObject Storageに出力します。
（formatで日付フォーマットを指定しない場合、日付データの出力形式ははNLS_DATE_FORMATによって決まります。）

SQL> BEGIN
  2    DBMS_CLOUD.EXPORT_DATA(
  3      credential_name => 'OCI$RESOURCE_PRINCIPAL',
  4      file_uri_list   => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/ネームスペース/b/BucketForPartition/o/p2019.csv',
  5      query           => 'SELECT * FROM lineorder WHERE TO_CHAR(lo_orderdate,''YYYY'') = ''2019''',
  6      format          => JSON_OBJECT('type' value 'csv', 'delimiter' value ',')
  7    );
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>

Object StorageにCSVファイルが出力されました。

DBMS_CLOUD.LIST_OBJECTSでObject Storageに出力されたCSVファイルのファイル名を確認します。

SQL> col object_name for a50
SQL> SELECT object_name
  2  FROM DBMS_CLOUD.LIST_OBJECTS(
  3  	'OCI$RESOURCE_PRINCIPAL', 
  4      'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/ネームスペース/b/BucketForPartition/o/'
  5  )
  6  WHERE object_name like 'p2019%';

OBJECT_NAME
--------------------------------------------------
p2019_1_20220302T231102Z.csv

SQL>

出力されたファイル名は、DBMS_CLOUD.EXPORT_DATA実行時に指定したファイル名と異なる（UTCでのタイムスタンプが付与される）ことに注意が必要です。

3.ハイブリッド・パーティション表の作成

lineorderをlo_orderdateでレンジパーティション化した、以下のようなパーティション構成のハイブリッド・パーティション表lineorder_hyb_partを作成します。
　p2019：2019年のデータを格納する外部パーティション（Object Storage内のCSVファイルを参照）
　p2020：2020年のデータを格納する内部パーティション
　p2021：2021年のデータを格納する内部パーティション
　pmax：2022年以降のデータを格納する内部パーティション

SQL> BEGIN
  2    DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
  3        table_name =>'lineorder_hyb_part',  
  4        credential_name =>'OCI$RESOURCE_PRINCIPAL',  
  5        format => json_object('delimiter' value ',', 'recorddelimiter' value 'newline', 'dateformat' value 'DD-MON-YY'),  
  6        column_list => 'lo_orderdate date, lo_revenue number',
  7  	   field_list  => 'lo_orderdate, lo_revenue',
  8        partitioning_clause => 'partition by range (lo_orderdate)
  9  	   (
 10  			partition p2019 values less than (TO_DATE(''2020-01-01'',''YYYY-MM-DD'')) external location
 11                (''https://objectstorage.ap-tokyo-1.oraclecloud.com/n/ネームスペース/b/BucketForPartition/o/p2019_1_20220302T231102Z.csv'') ,
 12              partition p2020 values less than (TO_DATE(''2021-01-01'',''YYYY-MM-DD'')) ,
 13              partition p2021 values less than (TO_DATE(''2022-01-01'',''YYYY-MM-DD'')) ,
 14  		     partition pmax  values less than (MAXVALUE)
 15  	   )'
 16    );
 17  END;
 18  /

PL/SQL procedure successfully completed.

SQL>

ハイブリッド・パーティション表lineorder_hyb_partが作成できました。

lineorder_hyb_partの内容を確認してみます。

SQL> SELECT COUNT(*) FROM lineorder_hyb_part;

  COUNT(*)
----------
    120000

SQL> SELECT COUNT(*) FROM lineorder_hyb_part PARTITION(p2019);

  COUNT(*)
----------
    120000

SQL> SELECT COUNT(*) FROM lineorder_hyb_part PARTITION(p2020);

  COUNT(*)
----------
	 0

SQL> SELECT COUNT(*) FROM lineorder_hyb_part PARTITION(p2021);

  COUNT(*)
----------
	 0

SQL>

パーティションp2019のみにレコードが格納されていることがわかります。

lineorder_hyb_part表にlineorder表の2020年と2021年のデータをINSERTします。

SQL> INSERT INTO lineorder_hyb_part
  2  SELECT * FROM lineorder
  3  WHERE TO_CHAR(lo_orderdate,'YYYY') = '2020' OR TO_CHAR(lo_orderdate,'YYYY') = '2021';

240000 rows created.

SQL> COMMIT;

Commit complete.

SQL>

各パーティションのレコード数を確認します。

SQL> SELECT COUNT(*) FROM lineorder_hyb_part;

  COUNT(*)
----------
    360000

SQL> SELECT COUNT(*) FROM lineorder_hyb_part PARTITION(p2019);

  COUNT(*)
----------
    120000

SQL> SELECT COUNT(*) FROM lineorder_hyb_part PARTITION(p2020);

  COUNT(*)
----------
    120000

SQL> SELECT COUNT(*) FROM lineorder_hyb_part PARTITION(p2021);

  COUNT(*)
----------
    120000

SQL>

以上でlineorder表をlo_orderdateでパーティション化したハイブリッド・パーティション表lineorder_hyb_partが作成できました。
パーティションp2019がObject Storage上のCSVファイルを使用した外部パーティション、その他のパーティションは内部パーティションとなっています。

4. ハイブリッド・パーティション表の内部パーティションを外部パーティション化

lineorder_hyb_part表の内部パーティションp2020を外部パーティション化してみます。
まず、パーティションp2020のデータをDBMS_CLOUD.EXPORT_DATAを使用してCSV形式でObject Storageに出力します。

SQL> BEGIN
  2    DBMS_CLOUD.EXPORT_DATA(
  3      credential_name => 'OCI$RESOURCE_PRINCIPAL',
  4      file_uri_list   => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/nrhnlrqdttaw/b/BucketForPartition/o/p2020.csv',
  5      query           => 'SELECT * FROM lineorder_hyb_part PARTITION(p2020)',
  6      format          => JSON_OBJECT('type' value 'csv', 'delimiter' value ',')
  7    );
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>

DBMS_CLOUD.LIST_OBJECTSでObject Storageに出力されたCSVファイルのファイル名を確認します。

SQL> col object_name for a50
SQL> SELECT object_name 
  2  FROM DBMS_CLOUD.LIST_OBJECTS(
  3  	'OCI$RESOURCE_PRINCIPAL', 
  4      'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/ネームスペース/b/BucketForPartition/o/'
  5  )
  6  WHERE object_name like 'p2020%';

OBJECT_NAME
--------------------------------------------------
p2020_1_20220302T234520Z.csv

SQL>

次に、パーティションp2020を削除します。

SQL> ALTER TABLE lineorder_hyb_part DROP PARTITION p2020;

Table altered.

SQL> SELECT COUNT(*) FROM lineorder_hyb_part;

  COUNT(*)
----------
    240000

SQL>

最後に、ALTER TABLE SPLIT PARTITIONで、パーティションp2021をp2020とp2021の2つのパーティションに分割し、パーティションp2020のEXTERNAL LOCATIONにパーティションp2020のデータを出力したObject Storage上のCSVファイルを指定します。

SQL> ALTER TABLE lineorder_hyb_part
  2  SPLIT PARTITION p2021 INTO (
  3  	PARTITION p2020 VALUES LESS THAN (TO_DATE('2021-01-01','YYYY-MM-DD'))
  4  		EXTERNAL LOCATION ('https://objectstorage.ap-tokyo-1.oraclecloud.com/n/ネームスペース/b/BucketForPartition/o/p2020_1_20220302T234520Z.csv'),
  5  	PARTITION p2021
  6  );

Table altered.

SQL>

パーティションp2021が分割され、外部パーティションp2020が追加されました。

lineorder_hyb_partの各パーティションのレコード数を確認してみます。

SQL> SELECT COUNT(*) FROM lineorder_hyb_part;

  COUNT(*)
----------
    360000

SQL> SELECT COUNT(*) FROM lineorder_hyb_part PARTITION(p2019);

  COUNT(*)
----------
    120000

SQL> SELECT COUNT(*) FROM lineorder_hyb_part PARTITION(p2020);

  COUNT(*)
----------
    120000

SQL> SELECT COUNT(*) FROM lineorder_hyb_part PARTITION(p2021);

  COUNT(*)
----------
    120000

SQL>

p2019、p2020。p2021の各パーティション に120,000レコードづつ格納されていることが確認できました。

USER_TAB_PARTITIONSでTABLESPACE_NAMEを確認すると、外部パーティションはSYSTEMとなっています。

SQL> SELECT table_name,partition_name,tablespace_name
  2  FROM user_tab_partitions
  3  WHERE table_name = 'LINEORDER_HYB_PART';

TABLE_NAME	         PARTITION_NAME	      TABLESPACE_NAME
-------------------- -------------------- --------------------
LINEORDER_HYB_PART   P2020		          SYSTEM
LINEORDER_HYB_PART   P2019		          SYSTEM
LINEORDER_HYB_PART   P2021		          DATA
LINEORDER_HYB_PART   PMAX		          DATA

SQL>

lineorder_hyb_part表の内部パーティションp2020を外部パーティション化することができました。

まとめ

以上で、非パーティション表をもとにハイブリッド・パーティション表を作成し、作成したハイブリッド・パーティション表の内部パーティションをObject Storage上のファイルを用いた外部パーティションに移行する手順が確認できました。

参考情報

ハイブリッド・パーティション表の管理
DBMS_CLOUD.CREATE_HYBRID_PART_TABLEプロシージャ
DBMS_CLOUD.EXPORT_DATAプロシージャ
DBMS_CLOUD.LIST_OBJECTSファンクション
しばちょう先生の試して納得！DBAへの道　第48回 [Oracle Database 12c] 時間隔パーティション表（インターバル・パーティション表）のクセ

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?