テーブルの作成

CREATE TABLE ステートメントを使用してテーブルを作成できます。

このセクションでは、非パーティションテーブルの作成について説明します。パーティションテーブルの作成と使用については、パーティションテーブルの作成を参照してください。

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

非パーティションテーブルとは、パーティションが1つしかないテーブルを指します。

非パーティションテーブルを作成する例文は以下のとおりです：

obclient>CREATE TABLE table_name1(w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, unique(w_name, w_city)
, primary key(w_id)
);

Query OK, 0 rows affected (0.09 sec)

obclient>CREATE TABLE table_name2 (c_w_id int NOT NULL
, c_d_id int NOT null
, c_id int NOT null
, c_discount decimal(4, 4)
, c_credit char(2)
, c_last varchar(16)
, c_first varchar(16)
, c_middle char(2)
, c_balance decimal(12, 2)
, c_ytd_payment decimal(12, 2)
, c_payment_cnt int
, c_credit_lim decimal(12, 2)
, c_street_1 varchar(20)
, c_street_2 varchar(20)
, c_city varchar(20)
, c_state char(2)
, c_zip char(9)
, c_phone char(16)
, c_since date
, c_delivery_cnt int
, c_data varchar(500)
, index icust(c_last, c_d_id, c_w_id, c_first, c_id)
, FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id)
, primary key (c_w_id, c_d_id, c_id)
);

Query OK, 0 rows affected

例では2つのテーブルを作成し、それぞれのテーブルの列に対して主キーや外部キーなどの制約を定義しています。主キーや外部キーの詳細については、列の制約タイプの定義を参照してください。

テーブルの列を作成する際は、適切なデータ型を選択してください。SQLデータ型の詳細については、データ型を参照してください。

レプリケーションテーブルの作成

レプリケーションテーブルは、OceanBaseデータベースの特殊なテーブルです。この種のテーブルは、任意の「正常」なレプリカでデータの最新の変更を読み取ることができます。書き込み頻度の要件が低く、読み取り操作の遅延とロードバランシングの要件が高いユーザーにとって、レプリケーションテーブルは優れた選択肢です。

ユーザーがレプリケーションテーブルを作成すると、そのテナント内のすべてのOBServerノードにレプリケーションテーブルのレプリカが作成されます。これらのレプリカのうち1つがリーダーとして選ばれ、書き込みリクエストを受け付けます。残りのレプリカは読み取りリクエストのみを受け付けることができます。

すべてのレプリカはリーダーに状態を報告する必要があり、主にレプリカの再実行進捗、つまりデータ同期の進捗です。一般的に、フォロワーの再実行進捗はリーダーより若干遅れますが、その遅れが一定のしきい値を超えない限り、リーダーはレプリカが「正常」な状態にあり、リーダー上の変更を迅速に再実行できると判断します。リーダーが特定のレプリカを一定期間「正常」と認識すると、そのフォロワーに一定期間のLeaseを付与します。一般的に言えば、リーダーはその後の一定期間、フォロワーが「正常」な状態を維持し、強整合性読み取りサービスを提供できると「信頼」しています。この「信頼」期間中、リーダーはレプリケーションテーブルのトランザクションをコミットする前に、常にフォロワーの再実行進捗を確認します。フォロワーがそのトランザクションの変更を再実行した後、リーダーはユーザーにトランザクションが正常にコミットされたことを通知します。この時点で、ユーザーはフォロワー上で、ちょうどコミットされたトランザクションの変更を読み取ることができます。

レプリケーションテーブル機能は、OceanBaseデータベースV3.xバージョンから存在しています。V4.xバージョンでは、OceanBaseデータベースのアーキテクチャが大きく変更されたため、V4.xのレプリケーションテーブルは、単一マシンログストリームの新しいアーキテクチャに適応するために、パーティションに基づく読み取り可能なバージョン番号検証とログストリームに基づくLease付与メカニズムを構築し、強整合性読み取りの正確性を保証しています。

さらに、V4.xバージョンのレプリケーションテーブル機能は、リーダー切り替え時のトランザクションキル防止機能を強化しています。ユーザーまたはロードバランシングがリーダー切り替えを開始すると、未コミットのレプリケーションテーブルトランザクションは、V3.xバージョンのように継続できなくなることはなく、リーダー切り替え後に引き続き実行されます。V3.xバージョンと比較して、V4.xのレプリケーションテーブル機能は、書き込みトランザクションのパフォーマンスと災害復旧能力が向上しており、レプリカのダウンは読み取り操作への影響を低減しています。

レプリケーションテーブルの使用制限

• レプリケーションテーブル：

  • レプリケーションテーブルの作成制限：sysテナント、metaテナントにはブロードキャストログストリームがないため、レプリケーションテーブルの作成はサポートされません。

  • 書き込み性能はノード数の影響を受ける：レプリケーションテーブルへの書き込みはすべてのレプリカに同期する必要があるため、ノード数が多いほど書き込み性能に影響します。

    • 回避策：レプリケーションテーブルの書き込みと読み取りを同一トランザクション内で行うことを避けてください。レプリケーションテーブルトランザクションの純粋な書き込みまたは純粋な読み取りは問題ありません。

  • プロパティ変換：

    • レプリケーションテーブルとTableGroupは互斥であり、レプリケーションテーブルのTableGroupプロパティを変更するとエラーが発生します。通常のテーブルをレプリケーションテーブルに変換する際、その通常のテーブルが特定のTableGroupに属している場合、プロパティ変更コマンドはエラーになります。
    • レプリケーションテーブルの変換はロードバランシングとtransferに依存しているため、関連するパラメータが有効な状態であることを確認する必要があります。

  • ルーティング：

    • トランザクション内にレプリケーションテーブルへの書き込み操作があり、その後レプリケーションテーブルをクエリする際、ランダムにフォロワーにルーティングされると、レプリカが読み取り不可である問題が発生し、observer内部で再度レプリケーションテーブルのリーダーに転送され、クエリ性能に影響します。

      • ODP V4.3.3ではルーティング戦略が調整されており、レプリケーションテーブルへの書き込み操作がある場合、その後のクエリはレプリケーションテーブルのリーダーにルーティングされます。

    • レプリケーションテーブルと通常のテーブルのJOINクエリ操作では、レプリケーションテーブルにランダムにルーティングされます(JOINクエリでは、ODPは最初のテーブルに従ってルーティングします)。この際、通常のテーブルの非リーダーノードにルーティングされ、リモートプランが発生する可能性があります。

    • 通常のテーブルのプロパティをレプリケーションテーブルに変更しても、ODPはそれを検知できず、レプリケーションテーブルに従ってルーティングして負荷を分散することができません。

  • トランザクション内でレプリケーションテーブルに変更を加えた後の、その後のクエリ操作：レプリケーションテーブルをクエリする際、生成される実行計画はローカルレプリカを選択しますが、変更が加えられているため、レプリカが読み取り不可であるというエラーが発生します。SQLは再試行してリーダーレプリカを選択しますが、この時点でプランキャッシュがヒットせず、クエリのパフォーマンスが低下します。

    説明

    OceanBaseデータベースでは、トランザクション内でレプリケーションテーブルに変更を加えた場合、システムはクエリ時に常にレプリケーションテーブルのリーダーレプリカを優先的に選択し、直接ローカルレプリカを選択することはありません。これにより、OceanBaseデータベースはクエリ効率を向上させ、誤ったレプリカの選択を回避します。

• ブロードキャストログストリーム：

  • 各ユーザーテナントは最大1つのブロードキャストログストリームしか持てません。
  • ブロードキャストログストリームと通常のログストリーム間の属性変換はサポートされていません。
  • ブロードキャストログストリームの手動削除はサポートされていません。現在はテナントの削除と同時に削除されます。

レプリケーションテーブル作成構文

レプリケーションテーブルを作成する構文は、CREATE TABLE ステートメントの後に DUPLICATE_SCOPE オプションを追加することです。レプリケーションテーブルはユーザーテナントのみが作成でき、sys テナントでは作成できません。レプリケーションテーブルを作成するSQLステートメントは以下のとおりです：

CREATE TABLE table_name column_definition DUPLICATE_SCOPE='none | cluster';

ここで、DUPLICATE_SCOPE パラメータはレプリケーションテーブルの属性を指定するために使用され、取り得る値は以下のとおりです：

• none：このテーブルが通常のテーブルであることを示します。
• cluster：このテーブルがレプリケーションテーブルであることを示します。リーダーはトランザクションを現在のテナントのすべてのFレプリカおよびRレプリカに複製する必要があります。

テーブル作成時に DUPLICATE_SCOPE を指定しない場合、デフォルト値は none です。

CREATE TABLE dup_t1 (c1 int,c2 int) DUPLICATE_SCOPE= 'cluster';

特定のテナントで最初のレプリケーションテーブルが作成されると、システムは同時に特殊なログストリームであるブロードキャストログストリームを作成します。その後に新規作成されるすべてのレプリケーションテーブルは、このブロードキャストログストリーム上に作成されます。ブロードキャストログストリームと通常のログストリームの違いは、ブロードキャストログストリームが自動的にテナント内の各OBServerノードにレプリカをデプロイする点です。これにより、理想的な状況下では、レプリケーションテーブルは任意のOBServerノードで強整合性読み取りを提供できます。テナントのレプリケーションテーブルが配置されているブロードキャストログストリームを確認するには、以下のSQLを実行します：

SELECT * FROM oceanbase.DBA_OB_LS WHERE flag LIKE "%DUPLICATE%";

クエリ結果の例は以下のとおりです。

+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+
| LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG      | UNIT_LIST |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+
|  1003 | NORMAL | z1;z2        |             0 |           0 | 1683267390195713284 |     NULL | 1683337744205408139 | 1683337744205408139 | DUPLICATE |           |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+
1 rows in set

例では、LS_ID が 1003 のログストリームがブロードキャストログストリームであり、テナントのすべてのレプリケーションテーブルはこのログストリーム上に作成されます。ブロードキャストログストリームの詳細については、レプリカの紹介を参照してください。

レプリケーションテーブルの作成が成功すると、通常のテーブルと同様に挿入や読み書き操作を実行できます。異なる点は、読み取りリクエストについてです。Proxy経由でデータベースに接続する場合、読み取りリクエストは任意のOBServerノードで実行される可能性があります。直接接続でデータベースに接続する場合、ローカルレプリカが読み取り可能であれば、システムは直接接続されたOBServerノードで読み取りリクエストを実行します。データベース接続方法の詳細については、接続方法の概要を参照してください。

既存テーブルのデータをコピーして新しいテーブルを作成する

テーブルデータのコピー

CREATE TABLE AS SELECT ステートメントを使用してテーブルのデータをコピーできますが、構造は完全には一致せず、制約、インデックス、デフォルト値、パーティションなどの情報が失われます。

例文は以下のとおりです：

obclient>CREATE TABLE t1_copy AS SELECT * FROM t1;
Query OK, 3 rows affected

テーブル構造のコピー

CREATE TABLE LIKE ステートメントを使用してテーブル構造をコピーできますが、テーブルデータはコピーできません。

例：

obclient>CREATE TABLE t1_like like t1;
Query OK, 0 rows affected

行ストアテーブルの作成

OceanBaseデータベースは、行ストアテーブルの作成および行ストアとカラムストア間の変換機能をサポートしています。

パラメータ default_table_store_format=‘row’（デフォルト値）が設定されている場合、デフォルトで作成されるテーブルは行ストアテーブルです。default_table_store_formatがrow以外の場合は、WITH COLUMN GROUP(all columns)オプションを設定することで行ストアテーブルを作成できます。

行ストアとカラムストアの変換に関する情報は、テーブルの変更を参照してください。カラムストアインデックスの作成に関する情報については、インデックスの作成を参照してください。

WITH COLUMN GROUP(all columns)を指定して行ストアテーブルを作成します。

例：

CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(all columns);

カラムストアテーブルの作成

OceanBaseデータベースは、カラムストアテーブルの作成、行ストアからカラムストアへの変換、およびカラムストアインデックスの作成をサポートしています。OceanBaseデータベースはデフォルトで行ストアテーブルを作成しますが、WITH COLUMN GROUPオプションを設定することで、明示的にカラムストアまたは行ストア/カラムストアの冗長状態を指定できます。

行ストアからカラムストアへの変換の詳細については、テーブルの変更を参照してください。カラムストアインデックスの作成に関する情報については、インデックスの作成を参照してください。

WITH COLUMN GROUP(all columns, each column) を指定して、行ストア/カラムストアの冗長テーブルを作成します。

例：

CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(all columns, each column);

WITH COLUMN GROUP(each column) を指定して、カラムストアテーブルを作成します。

例：

CREATE TABLE tbl2_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(each column);

カラムストアテーブルを作成して使用する際、大量のデータをインポートした場合は、読み取りパフォーマンスを向上させるために一度のメジャーコンパクションを実行し、統計情報収集を行って実行戦略を調整する必要があります。

• メジャーコンパクション：データを一括インポートした後、メジャーコンパクションを実行することを推奨します。これは、断片化されたデータを整理し、物理的に連続した状態にすることで、読み取り時のディスクI/Oを削減し、読み取りパフォーマンスを向上させるのに役立ちます。データインポート後、テナント内でメジャーコンパクションを一度トリガーし、すべてのデータがベースライン層にマージされていることを確認してください。操作については、MAJOR AND MINORを参照してください。

• 統計情報収集：メジャーコンパクションが完了した後、統計情報の収集を行うことを推奨します。これは、オプティマイザーが有効なクエリプランと実行戦略を生成するために非常に重要です。GATHER_SCHEMA_STATSを実行して、すべてのテーブルの統計情報を収集し、ビューGV$OB_OPT_STAT_GATHER_MONITORで収集進捗を監視できます。

なお、カラムストアテーブルのデータ量が増加するにつれて、メジャーコンパクションの速度が低下する可能性があるため注意してください。

テーブルの更新モデルの指定

テーブル作成時に更新モデルを指定する構文は、CREATE TABLE ステートメントの後にテーブルオプション MERGE_ENGINE を追加することです。SQLステートメントは以下のとおりです：

CREATE TABLE table_name column_definition
    MERGE_ENGINE = {delete_insert | partial_update}
    WITH COLUMN GROUP([all columns,] each column);

MERGE_ENGINE パラメータはテーブルの更新モデルを指定するために使用され、取り得る値は以下のとおりです：

• partial_update：デフォルト値で、既存の更新モードを変更しないことを意味します。
• delete_insert: merge on write 方式を採用し、update を delete と insert に変換し、全列を書き込むことを意味します。

MERGE_ENGINE オプションを指定しない場合、このオプションの値は構成パラメータ default_table_merge_engine の値と同じになります。

適用シナリオ

OLAPシナリオでは delete_insert の使用を推奨しますが、それ以外のシナリオでは partial_update の使用を推奨します。より詳細な設定シナリオについては、設定のベストプラクティスを参照してください。

例：

• クリアカラムストアテーブル mer_tbl1 を作成し、更新モデルは merge on write に設定します。

  CREATE TABLE mer_tbl1 (col1 INT, col2 INT)
      MERGE_ENGINE = delete_insert
      WITH COLUMN GROUP(each column);
  

• 行と列の冗長化テーブル mer_tbl2 を作成し、更新モデルは merge on write に設定します。

  CREATE TABLE mer_tbl2 (col1 INT, col2 INT)
      MERGE_ENGINE = delete_insert
      WITH COLUMN GROUP(all columns, each column);
  

ヒープ組織テーブル（ヒープテーブル）の作成

OceanBaseデータベースは、インデックス組織テーブル（Index Organized Table）とヒープ組織テーブル（Heap Organized Table）の2種類のテーブル構造をサポートしています。

使用上の制限

• ヒープ組織テーブルのローカル一意キー（Unique Key Local）または主キー（Primary Key）は、すべてのパーティションキー（Partition Key）を含む必要があります。

• ヒープ組織テーブルでインデックスを作成する場合、インデックス名は主キーの最初の列名と同じにすることはできません。

• OceanBaseデータベースのヒープ組織テーブルは、現在以下のDDL操作をサポートしていません：

  • 主キー制約操作
  • 列を主キーに変更する
  • 主キー列の型の長さを長くする
  • 主キー列を追加する
  • 主キーを自動インクリメント列に変更する
  • 主キー列を削除する
  • パーティションの分割

テーブル構造を指定する構文

テーブル作成時に構造を指定する構文は、CREATE TABLE ステートメントの後にテーブルオプション ORGANIZATION を追加するものです。SQLステートメントは以下のとおりです：

CREATE TABLE table_name column_definition ORGANIZATION [=] {INDEX | HEAP};

ここで、ORGANIZATION パラメータは、テーブル内のデータ行の格納順序を指定するために使用され、取り得る値は以下のとおりです：

• INDEX：テーブルモデルが集約インデックステーブルモデル、すなわちインデックス組織テーブルであることを示します。
• HEAP：テーブルモデルが非集約インデックステーブルモデル、すなわちヒープ組織テーブルであることを示します。

ORGANIZATION オプションを指定しない場合、そのオプションの値はパラメータ default_table_organization の値と同じになります。

例：

• テーブル作成時に ORGANIZATION プロパティの値を HEAP に指定します。

  CREATE TABLE ora_tbl1 (col1 INT, col2 INT) ORGANIZATION = HEAP;
  

• テーブル作成時に ORGANIZATION を指定しない場合。

  テナントレベルのパラメータ default_table_organization を変更した後、テーブルを作成します。default_table_organization を参照してください。

  説明

  現在のバージョンでは、構成パラメータ default_table_organization はOceanBaseデータベースのMySQLモードユーザーテナントにのみ適用されます。sys（システム）テナントとOracleモードユーザーテナントはサポートされていません。

  1. 構成パラメータ default_table_organization の値を HEAP に設定します。

     ALTER SYSTEM SET default_table_organization = 'HEAP';
     

  2. テーブル ora_tbl2 を作成します。

     CREATE TABLE ora_tbl2(col1 INT, col2 INT);
     

  3. テーブル ora_tbl2 の定義を確認します。

     SHOW CREATE TABLE ora_tbl2;
     

     実行結果は次のとおりです：

     +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | Table    | Create Table                                                                                                                                                                                                                                                                                                                 |
     +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | ora_tbl2 | CREATE TABLE `ora_tbl2` (
       `col1` int(11) DEFAULT NULL,
       `col2` int(11) DEFAULT NULL
     ) ORGANIZATION HEAP DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
     +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

一時テーブルの作成

一時テーブルはセッションレベルの特殊なテーブルであり、主に中間データの一時保存に使用されます。

制限事項

• 一時テーブルのルーティング：一時テーブルも通常のテーブルと同様にロードバランシングをサポートする必要があるため、デフォルトでは特定のマシンに一時テーブルをバインドすることはできません。ロードバランシングを無効にして使用することを検討してください。

• OceanBaseデータベースは、直列化可能分離レベル（Serializable）では、同一トランザクション内で作成された一時テーブルに対する書き込み操作をサポートしていません。

• 本セッションに通常のテーブルと同名の一時テーブルが存在する場合、そのセッションでデータベースレベルの統計情報収集機能を使用することは推奨されません。結果が期待通りにならない可能性があります。

• 一時テーブルはバックグラウンドロジックによってクリーンアップされますが、そのDDLが業務に顕著な影響を与えないように、クリーンアップ速度は制限されています。そのため、一時テーブルを大量に使用するシナリオでは、定期的に手動で削除することを推奨します。

• DBLinkは一時テーブルにアクセスできません。

• ODPを介してOBServerに接続する場合、server_protocal、client_session_id_version、およびproxy_idパラメータを手動で調整する必要があります。

  具体的な操作は以下のとおりです：

  • 以下のコマンドを実行して、OBProxyの通信プロトコル設定を変更します。

    obclient> ALTER proxyconfig SET server_protocol = 'OceanBase 2.0';
    

  • 以下のコマンドを実行して、クライアントセッションID（Client Session ID）を生成するアルゴリズムをバージョン2に設定します。

    obclient> ALTER proxyconfig SET client_session_id_version = 2;
    

  • 以下のコマンドを実行して、ODPのIDを1に変更します。異なるODPには異なる数字を設定する必要があります。これにより、生成されるClient Session IDが競合しないように保証できます。

    obclient> ALTER proxyconfig SET proxy_id = 1;
    

使用例

一時テーブルtbl1を作成します:

obclient> CREATE TEMPORARY TABLE tbl1(col1 INT);

関連ドキュメント

• CREATE TABLE

• ALTER TABLE