テーブルの作成

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つがリーダーとして選ばれ、書き込みリクエストを受け付け、残りのレプリカは読み取りリクエストのみを受け付けることができます。

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

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

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

テーブル複製の使用制限

• テーブル複製：

  • テーブル複製の作成制限：sysテナントおよびmetaテナントにはブロードキャストログストリームがないため、テーブル複製の作成はサポートされていません。

  • 書き込み性能はノード数に影響を受けます：テーブル複製の書き込みではすべてのレプリカに同期する必要があるため、ノード数が多いほど書き込み性能への影響が大きくなります。

    • 回避策：テーブル複製の書き込みと読み取りを同一トランザクションで行うことを避けることを推奨します。テーブル複製トランザクションの純粋な書き込みまたは読み取りは問題ありません。

  • プロパティ変換：

    • テーブル複製とTableGroupは排他的であり、テーブル複製のTableGroupプロパティを変更しようとするとエラーが発生します。通常のテーブルをテーブル複製に変換する際、その通常のテーブルが特定のTableGroupに属している場合、プロパティ変更コマンドでエラーが報告されます。
    • テーブル複製の変換はロードバランシングと転送に依存しており、関連する構成パラメータが有効になっていることを確認する必要があります。

  • ルーティング：

    • トランザクション内でテーブル複製の書き込み操作が行われ、その後テーブル複製を照会する場合、ランダムルーティングでフォロワーにルーティングされると、レプリカが読み取り不可能という問題が発生し、オブザーバー内部で再ルーティングが行われてテーブル複製のリーダーに転送されるため、照会性能に影響を与えます。

      • ODP V4.3.3ではルーティング戦略が調整されており、テーブル複製の書き込み操作があった場合、その後の照会はテーブル複製のリーダーにルーティングされます。

    • テーブル複製と通常のテーブルのJOINクエリ操作では、テーブル複製に基づいてランダムルーティングが行われます（JOINクエリについては、ODPは最初のテーブルに基づいてルーティングします）。この場合、通常のテーブルの非リーダーノードにルーティングされる可能性があり、リモート計画が発生する原因となります。

    • 通常のテーブルのプロパティをテーブル複製に変更しても、ODPはそれを検出できず、テーブル複製に基づいて負荷を分散するルーティングを行うことができません。

  • トランザクション内でテーブル複製に変更が加えられた後のクエリ操作：テーブル複製を照会する際、生成される実行計画はローカルレプリカを選択しますが、変更が加えられているため、レプリカが読み取り不可能というエラーが報告されます。SQLの再試行ではリーダーレプリカが選択されますが、この時点でプランキャッシュにヒットしないため、クエリ性能が低下します。

    説明

    OceanBaseデータベースV4.3.5では、V4.3.5 BP2以降のバージョンから、トランザクション内でテーブル複製に変更が加えられた場合、システムは照会時に常にテーブル複製のリーダーレプリカを優先的に選択し、ローカルレプリカを直接選択することはありません。これにより、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      |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
|  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 を参照してください。

  説明

  現在のバージョンの構成パラメータ 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データベースのMySQLモードにおける一時テーブルは、セッションレベルの特殊なテーブルであり、主に中間データを一時的に格納するために使用されます。

CREATE TEMPORARY TABLE ステートメントを使用して一時テーブルを作成できます。構文は以下のとおりです：

CREATE TEMPORARY TABLE table_name column_definition;

制限事項

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

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

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

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

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

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

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

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

    ALTER proxyconfig SET server_protocol = 'OceanBase 2.0';
    

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

    ALTER proxyconfig SET client_session_id_version = 2;
    

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

    ALTER proxyconfig SET proxy_id = 1;
    

例

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

CREATE TEMPORARY TABLE tbl1(col1 INT);

関連ドキュメント

• CREATE TABLE

• ALTER TABLE