Indexer les tables hybrides¶

Types d’index¶

Les tables hybrides prennent en charge deux types d’index :

• Les index créés automatiquement lorsque vous déclarez des contraintes pour les colonnes de table hybride.

  • Index pour les contraintesPRIMARY KEY

  • Index pour les contraintesFOREIGN KEY

  • Index pour les contraintes UNIQUE

• Les index définis par l’utilisateur, appelés index secondaires, que vous pouvez définir sur d’autres colonnes si nécessaire. Un seul index peut couvrir une ou plusieurs colonnes. Vous pouvez utiliser CREATE HYBRID TABLE ou CREATE INDEX pour définir des index secondaires.

  Lorsque vous créez des index secondaires, vous pouvez « inclure » des colonnes qui ne font pas partie de la clé d’index, mais qui sont associées et stockées avec l’index lui-même. Voir les colonnes INCLUDE.

  Attention

  Pour ajouter un index secondaire, vous devez utiliser un rôle bénéficiant du privilège SELECT sur la table hybride. Si vous avez accès à une vue des données de la table hybride, mais pas à la table elle-même, vous ne pouvez pas ajouter d’index secondaire.

Ajouter des index secondaires¶

Toutes les tables hybrides nécessitent une clé primaire unique. Les données d’une table hybride sont classées en fonction de cette clé primaire. Vous pouvez créer des index secondaires supplémentaires à des attributs de clé non primaire afin d’accélérer les recherches sur ces attributs. Les index peuvent être en mesure de réduire le nombre d’enregistrements analysés lorsqu’un prédicat de requête utilise l’une des conditions suivantes :

• =, >, >=, <, <= (opérateurs de comparaison)

• Conditions [ NOT ] IN

• Conditions [ NOT ] BETWEEN

Si vous avez des requêtes courantes et répétées avec des prédicats sur un attribut spécifique ou un groupe d’attributs composite, vous pouvez envisager d’ajouter un index à cet attribut ou à ce groupe d’attributs pour améliorer les performances. Il convient toutefois de tenir compte des considérations suivantes lorsque vous utilisez des index :

• Augmentation de la consommation de stockage lors du stockage de copies supplémentaires du sous-ensemble de données dans l’index.

• Ajout de surcharge de traitement aux DMLs, car les index sont gérés de manière synchrone.

Vous pouvez ajouter des index secondaires à une table hybride lorsque vous la créez, ou vous pouvez les ajouter ultérieurement en utilisant la commande CREATE INDEX. Par exemple, l’instruction suivante CREATE HYBRID TABLE crée automatiquement deux index (sur les colonnes PRIMARY KEY et UNIQUE, col1 et col2) et un index secondaire défini par l’utilisateur (sur 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;

Vous pouvez également créer un index secondaire pour une table hybride existante via la commande CREATE INDEX. Utilisez cette commande pour ajouter un index à une table hybride qui est activement utilisée pour une charge de travail et qui sert des requêtes, ou qui possède des clés étrangères. La commande CREATE INDEX crée des index simultanément sans verrouiller la table pendant l’opération.

Toutefois, si votre application de table hybride est en mode de développement ou de test et que le temps d’arrêt de la table n’est pas un problème, il est plus efficace de recréer la table hybride et de créer les index en exécutant un chargement en masse optimisé. Cette méthode est plus efficace que la création d’un index en ligne avec la commande CREATE INDEX.

Le chargement en masse optimisé est pris en charge pour CTAS, COPY, et INSERT INTO. .. SELECT mais vous ne pouvez pas utiliser CTAS si votre table a une contrainte FOREIGN KEY. La deuxième table créée dans cet exemple, fk_hybrid_table, devrait être chargée en masse avec COPY ou 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)
);

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);

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
  );

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

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;

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