ハイブリッドテーブルのインデックス作成

このトピックでは、ハイブリッドテーブル にインデックスを付ける方法について説明します。

インデックスの種類

ハイブリッドテーブルは、2種類のインデックスをサポートしています。

  • ハイブリッドテーブル列に制約を宣言すると自動的に作成されるインデックス。

    • PRIMARY KEY 制約のインデックス

    • FOREIGN KEY 制約のインデックス

    • UNIQUE 制約のインデックス

  • *セカンダリインデックス*とも呼ばれるユーザー定義インデックスは、必要に応じて他の列に定義できます。1つのインデックスで1つ以上の列をカバーできます。セカンダリインデックスを定義するには、CREATE HYBRID TABLE または CREATE INDEX を使用できます。

    セカンダリインデックスを作成するときに、インデックスキーの一部ではないが、インデックス自体に関連付けられて格納されている列を「含める」ことができます。INCLUDE列 をご参照ください。

    注意

    セカンダリインデックスを追加するには、ハイブリッドテーブルに SELECT 権限を付与されたロールを使用する必要があります。ハイブリッドテーブルのデータのビューにはアクセスでき、テーブル自体にはアクセスできない場合、セカンダリインデックスを追加できません。

セカンダリインデックスを追加する

すべてのハイブリッドテーブルには一意の主キーが必要です。ハイブリッドテーブルのデータは、この主キーによって順序付けられます。主キー以外の属性に追加のセカンダリインデックスを作成して、それらの属性の検索を高速化できます。クエリ述語で次のいずれかの条件が使用されている場合、インデックスによってスキャンされる記録の数を削減できる可能性があります。

特定の属性または複合属性グループに対する述語を含む一般的な繰り返しクエリがある場合は、パフォーマンスを向上させるために、その属性または属性グループにインデックスを追加することを検討してください。インデックスを使用する場合は次の点に注意してください。

  • インデックス内のデータのサブセットの追加コピーを保存すると、ストレージ消費量が増加します。

  • インデックスは同期的に維持されるため、DMLs に追加のオーバーヘッドが発生します。

ハイブリッドテーブルには、作成時にセカンダリインデックスを追加することも、CREATE INDEX コマンドを使用して後から追加することもできます。たとえば、次の CREATE HYBRID TABLE ステートメントは、自動的に2つのインデックス(PRIMARY KEY と UNIQUE 列、col1col2)と1つのユーザー定義のセカンダリインデックス(col3)を作成します。

CREATE OR REPLACE HYBRID TABLE target_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0) UNIQUE,
    col3 NUMBER(38,0),
    INDEX index_col3 (col3)
    )
  AS SELECT col1, col2, col3 FROM source_table;
Copy

または、CREATE INDEX コマンドを使用して、既存のハイブリッドテーブルのセカンダリインデックスを作成することができます。ワークロードでアクティブに使用され、クエリを提供している、または外部キーを持つハイブリッドテーブルにインデックスを追加するには、このコマンドを使用します。CREATEINDEX コマンドは、操作中にテーブルをロックすることなく、同時にインデックスを作成します。

Tip

SHOW INDEXES コマンドを使用して、インデックス構築のステータスを確認してください。インデックスは一度に1つしか構築できません。

ただし、ハイブリッドテーブルアプリケーションが開発またはテストモードであり、テーブルのダウンタイムが問題にならない場合は、最適化されたバルクロードを実行してハイブリッドテーブルを再作成し、インデックスを作成する方が効率的です。この方法は、CREATE INDEX コマンドを使用したオンラインインデックス構築よりも効率的です。

最適化された一括ロードは、 CTAS、 COPY、 INSERT INTO ... SELECT でサポートされています。ただし、テーブルに CTAS FOREIGN 制約がある場合は KEY を使用できません。この例で作成された2番目のテーブル fk_hybrid_table は、 COPY または INSERT INTO ... SELECT で一括ロードする必要があります。

CREATE OR REPLACE HYBRID TABLE ref_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0) UNIQUE
);

CREATE OR REPLACE HYBRID TABLE fk_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0),
    col3 NUMBER(38,0),
    FOREIGN KEY (col2) REFERENCES ref_hybrid_table(col2),
    INDEX index_col3 (col3)
);
Copy

INCLUDE列

INCLUDE 列はセカンダリインデックスキーの一部ではありませんが、インデックス記録とともに格納されます。実際にインデックスが作成された列と含まれる列のデータとの関連性により、特定のクエリはテーブルスキャンを回避し、インデックスを利用したより低コストなスキャンの恩恵を受けることができます。しかし、インデックスに含まれる列を使用すると、インデックス対象の列と一緒に追加の列も保存されるため、ストレージの消費量が増加する可能性があります。

たとえば、次のテーブルとインデックスを考えます。この場合のインデックスは、CREATE TABLE ステートメントまたは CREATE INDEX ステートメントのいずれかで宣言できます。

CREATE OR REPLACE HYBRID TABLE sensor_data_device1 (
  device_id VARCHAR(10),
  timestamp TIMESTAMP PRIMARY KEY,
  temperature DECIMAL(6,4),
  vibration DECIMAL(6,4),
  motor_rpm INT
  );

CREATE INDEX sec_sensor_idx
  ON TABLE sensor_data_device1(temperature)
    INCLUDE (vibration, motor_rpm);
Copy

このセカンダリインデックスは、1つの列(temperature)を直接カバーし、2つの列(vibration, motor_rpm)を間接的にカバーしているため、temperature を制約し、含まれている列からデータを選択する特定のクエリを最適化するために使用できます。

この動作をテストするには、まずテーブルにいくつかの行を生成します。

INSERT INTO sensor_data_device1 (device_id, timestamp, temperature, vibration, motor_rpm)
  SELECT 'DEVICE1', timestamp,
    UNIFORM(25.1111, 40.2222, RANDOM()), -- Temperature range in °C
    UNIFORM(0.2985, 0.3412, RANDOM()), -- Vibration range in mm/s
    UNIFORM(1400, 1495, RANDOM()) -- Motor RPM range
  FROM (
    SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
      FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
  );
Copy

次に以下のクエリを実行します。

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature = 25.6;
Copy

このクエリは sec_sensor_idx という名前のセカンダリインデックスを利用します。この動作は、クエリに対して EXPLAIN コマンドを実行するか、Snowsight のクエリプロファイルを精査することで確認できます。セカンダリインデックスに対するインデックススキャンが行われ、ハイブリッドテーブル自体に対する「プローブスキャン」は行われません。

以下のクエリも、他のサポートされている WHERE 句の条件を使用しており、同じセカンダリインデックスの恩恵を受けます。

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature IN (25.6, 31.2, 35.8);

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature BETWEEN 25.0 AND 26.0;
Copy

次に、最初のクエリを変更して、選択リストに device_id 列を追加します。この列は sec_sensor_idx インデックスによってカバーされていません。

SELECT device_id, temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature = 25.6;
Copy

このクエリは、セカンダリインデックスだけに依存できず、正しい device_id の値を返すためにハイブリッドテーブルのプローブスキャンが必要です。