Utilisation des API pour créer un index

Vous pouvez créer un index pour une table NoSQL à l'aide de commandes SQL ou à l'aide de l'API TableRequest.

Utiliser des commandes SQL

Un index peut être créé à l'aide de la commande CREATE INDEX.

Créez un index de champ unique :

Exemple : Créez un index sur le code de réservation des passagers.

CREATE INDEX fixedschema_conf ON baggageInfo(confNo)

Ce qui précède est un exemple d'index de schéma fixe à une colonne. L'index est créé dans le champ confNo avec le type de données string dans la table baggageInfo.

Créez un index composite :

Exemple : Créez un index sur le nom complet et le numéro de téléphone des passagers.

CREATE INDEX compindex_namephone ON baggageInfo(fullName,contactPhone)

Ce qui précède est un exemple d'index composite. L'index est créé sur deux champs du schéma baggageInfo, sur le nom complet et le numéro de téléphone du contact.

Note : Vous pouvez avoir un ou plusieurs champs de cet index en tant que colonnes de schéma fixe.

Créer un index JSON :

Un index est appelé index JSON si au moins un des champs se trouve dans les données JSON. Comme JSON est sans schéma, le type de données d'un champ JSON indexé peut être différent d'une rangée à l'autre. Lors de la création d'un index sur les champs JSON, si vous n'êtes pas sûr du type de données attendu pour le champ JSON, vous pouvez utiliser le type de données anyAtomic. Vous pouvez également spécifier l'un des types de données atomiques Oracle NoSQL Database. Pour ce faire, vous déclarez un type de données à l'aide du mot clé AS à côté de chaque chemin d'index dans le champ JSON.

Exemple 1 : Créez un index sur le numéro d'étiquette des sacs de passagers.

CREATE INDEX jsonindex_tagnum ON baggageInfo(bagInfo[].tagnum as INTEGER)

Ce qui précède est un exemple d'index JSON. L'index est créé sur le champ tagnum présent dans le champ JSON baginfo de la table baggageInfo. Notez que vous fournissez un type de données pour le champ tagnum lors de la création de l'index.

La création d'un index JSON échouera si la table associée contient des rangées contenant des données qui violent le type de données déclaré. De même, après la création d'un index JSON, une opération d'insertion/mise à jour échoue si la nouvelle rangée n'est pas conforme au type de données déclaré dans l'index JSON.

Exemple 2 : Créez un index sur l'itinéraire des passagers.

CREATE INDEX jsonindex_routing ON baggageInfo(bagInfo[].routing as ANYATOMIC)

Déclarer un chemin d'index JSON comme anyAtomic a l'avantage de permettre au champ JSON indexé d'avoir des valeurs de différents types de données. Les entrées d'index sont triées dans l'ordre croissant. Lorsque ces valeurs sont stockées dans l'index, elles sont triées comme suit :

Nombres
Chaîne
boolean

Cependant, cet avantage est compensé par les coûts d'espace et de CPU. C'est parce que les valeurs numériques de tout type dans le champ indexé seront converties en nombre avant d'être stockées dans l'index. Cette distribution prend du temps CPU, et le stockage résultant pour le nombre sera plus grand que le stockage d'origine pour le nombre.

Créez un index simple :

Un index est appelé index simple si, pour chaque ligne de données de la table, une entrée est créée dans l'index. L'index retourne une valeur unique de type atomique ou toute valeur spéciale (SQL NULL, JSON NULL, EMPTY). Essentiellement, les chemins d'index d'un index simple ne doivent pas renvoyer un tableau, une carte ou un type de données imbriqué.

Exemple : Créez un index sur trois champs, lorsque le sac a été vu pour la dernière fois, la dernière station vue et la date et l'heure d'arrivée.

CREATE INDEX simpleindex_arrival ON baggageInfo(bagInfo[].lastSeenTimeGmt as ANYATOMIC,
bagInfo[].bagArrivalDate as ANYATOMIC, bagInfo[].lastSeenTimeStation as ANYATOMIC)

Ce qui précède est un exemple d'index simple créé sur un document JSON dans un champ JSON. L'index est créé sur lastSeenTimeGmt et bagArrivalDate et lastSeenTimeStation, le tout à partir du document JSON bagInfo dans le champ JSON d'informations de la table baggageInfo. Si l'évaluation d'un chemin d'index simple renvoie un résultat vide, la valeur spéciale EMPTY est utilisée en tant qu'entrée d'index. Dans l'exemple ci-dessus, s'il n'y a pas d'entrée lastSeenTimeGmt ou bagArrivalDate ou lastSeenTimeStation dans le document JSON bagInfo ou s'il n'y a pas de tableau JSON bagInfo, la valeur spéciale EMPTY est indexée.

Créez un index à plusieurs clés :

Un index est appelé index à plusieurs clés si, pour chaque ligne de données de la table, plusieurs entrées sont créées dans l'index. Dans un index à plusieurs clés, il existe au moins un chemin d'index qui utilise un tableau ou un type de données imbriqué. Dans un index à plusieurs clés, pour chaque ligne de table, des entrées d'index sont créées sur tous les éléments des tableaux qui sont indexés.

Exemple 1 : Index à plusieurs clés : Créez un index sur le tableau d'informations de série de l'application de compte de diffusion en continu.

CREATE INDEX multikeyindex1 ON stream_acct (acct_data.contentStreamed[].seriesInfo[] AS ANYATOMIC)

L'index est créé sur le tableau seriesInfo[] dans la table stream_acct. Ici, tous les éléments du tableau seriesInfo[] de chaque rangée de la table stream_acct seront indexés.

Exemple 2 : Index à clés multiples imbriqué : Créez un index sur le tableau des détails d'épisode de l'application de compte de diffusion en continu.

Un index est un index multiclé imbriqué s'il est créé sur un champ présent à l'intérieur d'un tableau qui est à son tour présent à l'intérieur d'un autre tableau.

CREATE INDEX multikeyindex2 ON stream_acct (
    acct_data.contentStreamed[].seriesInfo[].episodes[]  AS ANYATOMIC)

Ce qui précède est un exemple d'index multiclé imbriqué où le champ est présent dans un tableau qui est présent à l'intérieur d'un autre tableau. L'index est créé sur le tableau episodes[] du tableau seriesInfo[] dans le JSON acct_data de la table stream_acct.

Exemple 3 : Index à clés multiples composite :

Un index est appelé index multiclé composite s'il est créé sur plusieurs champs, et au moins un de ces champs est multiclé. Un index multiclé composite peut avoir une combinaison de chemins d'index multiclé et de chemins d'index simples.

CREATE INDEX multikeyindex3 ON stream_acct (acct_data.country AS ANYATOMIC,
acct_data.contentStreamed[].seriesInfo[].episodes[]  AS ANYATOMIC)

Ce qui précède est un exemple d'index multiclé composite ayant un chemin d'index multiclé et un chemin d'index simple. L'index est créé dans le champ country et le tableau episodes[] dans la colonne JSON acct_data de la table stream_acct.

Voir Spécifications et restrictions sur l'index à clés multiples pour en savoir plus sur les restrictions sur l'index à clés multiples.

Créer un index sans clause NULLS

Vous pouvez créer un index avec la clause facultative WITH NO NULLS. Dans ce cas, les lignes contenant des valeurs NULL et/ou EMPTY dans les champs indexés ne seront pas indexées.

CREATE INDEX nonull_phone ON baggageInfo (contactPhone) WITH NO NULLS

La requête ci-dessus crée un index sur le numéro de téléphone des passagers. Si certains passagers n'ont pas de numéro de téléphone, ces champs ne feront pas partie de l'index.
Les index créés avec la clause WITH NO NULLS peuvent être utiles lorsque les données contiennent beaucoup de valeurs NULL et/ou EMPTY dans les champs indexés. Il réduit la surcharge de temps et d'espace lors de l'indexation.
Toutefois, l'utilisation de tels index par les interrogations est restreinte. Si un index est créé avec la clause WITH NO NULLS, les prédicats IS NULL et NOT EXISTS ne peuvent pas être utilisés en tant que prédicats d'index pour cet index.
En fait, un tel index ne peut être utilisé par une interrogation que si celle-ci comporte un prédicat d'index pour chacun des champs indexés.

Créer un index avec des clés uniques par rangée

Vous pouvez créer un index avec des clés uniques par propriété de ligne.

CREATE INDEX idx_showid ON
stream_acct(acct_data.contentStreamed[].showId AS INTEGER)
WITH UNIQUE KEYS PER ROW

Dans l'interrogation ci-dessus, un index est créé sur showId et il ne peut pas y avoir de showId en double pour un seul tableau contentStreamed. Le processeur d'interrogation est ainsi informé que, pour tout utilisateur du service de diffusion en continu, le tableau contentStreamed ne peut pas contenir deux émissions ou plus portant le même ID émission. La restriction est nécessaire car s'il existait des ID d'affichage en double, ils ne seraient pas inclus dans l'index. Si vous insérez une rangée avec le même showId deux éléments ou plus dans un seul tableau contentStreamed, une erreur est générée et l'opération d'insertion échoue.

Optimisation lors de l'exécution de l'interrogation :

Lorsque vous créez un index avec des clés uniques par rangée, l'index contient moins d'entrées que le nombre d'éléments dans le tableau contentStreamed. Vous pouvez écrire une interrogation efficace pour utiliser cet index. L'utilisation d'un tel index par l'interrogation produirait moins de résultats à partir de la clause FROM que si l'index n'était pas utilisé.

Exemples de création d'index sur des fonctions :

Exemple 1 : Créez un index qui indexe les rangées de la table BaggageInfo en fonction de leur dernière heure de modification :

CREATE INDEX idx_modtime ON BaggageInfo(modification_time())

Cet index sera utilisé dans une interrogation comportant modification_time comme condition de filtre.

SELECT * FROM BaggageInfo $u WHERE
modification_time($u) > "2019-08-01T10:45:00"

Cette interrogation retourne toutes les rangées dont le temps de modification le plus récent est après 2019-08-01T10:45:00. Il utilise l'index idx_modtime défini ci-dessus. Vous pouvez le vérifier en consultant le plan d'interrogation à l'aide de la commande show query.

Exemple 2 : Créez un index qui indexe les rangées de la table BaggageInfo sur la longueur du champ de routage.

CREATE INDEX idx_routlen ON BaggageInfo (length(bagInfo[].routing as string))

Cet index sera utilisé dans une interrogation comportant length comme condition de filtre.

SELECT * from BaggageInfo $bag where length($bag.bagInfo[].routing) > 10

Cette interrogation retourne toutes les rangées dont la longueur du champ d'acheminement est supérieure à 10. Il utilise l'index idx_routlen défini ci-dessus. Vous pouvez le vérifier en consultant le plan d'interrogation à l'aide de la commande show query.

Exemple 3 : Utilisation d'un chemin d'index à plusieurs clés

Dans l'exemple suivant, vous indexez les utilisateurs de la table stream_acct en fonction de l'ID des émissions qu'ils regardent et de l'année et du mois des dates auxquelles l'émission a été regardée.

CREATE INDEX idx_showid_year_month ON
stream_acct(acct_data.contentStreamed[].showId AS INTEGER,
substring(acct_data.contentStreamed[].seriesInfo[].episodes[].date AS STRING,0, 4),
substring(acct_data.contentStreamed[].seriesInfo[].episodes[].date AS STRING,5, 2))

Un exemple d'interrogation utilisant cet index est présenté ci-dessous. L'interrogation compte le nombre d'utilisateurs ayant regardé n'importe quel épisode de l'émission 16 dans l'année 2022.

SELECT count(*) FROM stream_acct s1 WHERE EXISTS
s1.acct_data.contentStreamed[$element.showId = 16].seriesInfo.
episodes[substring($element.date, 0, 4) = "2022"]

Cette interrogation utilisera l'index idx_showid_year_month. Vous pouvez le vérifier en consultant le plan d'interrogation à l'aide de la commande show query.

show query SELECT count(*) FROM stream_acct s1 WHERE EXISTS
> s1.acct_data.contentStreamed[$element.showId = 16].seriesInfo.episodes[substring($element.date, 0, 4) = "2022"]

{
  "iterator kind" : "GROUP",
  "input variable" : "$gb-1",
  "input iterator" :
  {
    "iterator kind" : "RECEIVE",
    "distribution kind" : "ALL_SHARDS",
    "distinct by fields at positions" : [ 1 ],
    "input iterator" :
    {
      "iterator kind" : "SELECT",
      "FROM" :
      {
        "iterator kind" : "TABLE",
        "target table" : "stream_acct",
        "row variable" : "$$s1",
        "index used" : "idx_showid_year_month",
        "covering index" : true,
        "index row variable" : "$$s1_idx",
        "index scans" : [
          {
            "equality conditions" : {"acct_data.contentStreamed[].showId":16,"substring#acct_data.contentStreamed[].seriesInfo[].episodes[].date@,0,4":"2022"},
            "range conditions" : {}
          }
        ]
      },
      "FROM variable" : "$$s1_idx",
      "SELECT expressions" : [
        {
          "field name" : "Column_1",
          "field expression" :
          {
            "iterator kind" : "CONST",
            "value" : 1
          }
        },
        {
          "field name" : "acct_id_gen",
          "field expression" :
          {
            "iterator kind" : "FIELD_STEP",
            "field name" : "#acct_id",
            "input iterator" :
            {
              "iterator kind" : "VAR_REF",
              "variable" : "$$s1_idx"
            }
          }
        }
      ]
    }
  },
  "grouping expressions" : [

  ],
  "aggregate functions" : [
    {
      "iterator kind" : "FUNC_COUNT_STAR"
    }
  ]
}

Utilisation de l'API TableRequest

Vous pouvez utiliser l'API TableRequest pour créer un index sur une table NoSQL.

La classe TableRequest est utilisée pour créer un index sur une table. L'exécution des opérations spécifiées par cette demande est asynchrone. Il s'agit d'opérations potentiellement de longue durée. TableResult est retourné par les opérations TableRequest et encapsule l'état de la table. Voir Informations de référence sur l'API de la trousse SDK Java pour Oracle NoSQL pour plus de détails sur la classe TableRequest et ses méthodes.

Téléchargez le code complet Indexes.java à partir des exemples ici.

/**
* Create an index acct_episodes in the stream_acct table
*/
private static void crtIndex(NoSQLHandle handle) throws Exception {
   String createIndexDDL = "CREATE INDEX acct_episodes ON " + tableName +
                           "(acct_data.contentStreamed[].seriesInfo[].episodes[]  AS ANYATOMIC)";
   TableRequest treq = new TableRequest().setStatement(createIndexDDL);
   TableResult tres = handle.tableRequest(treq);
   tres.waitForCompletion(handle, 60000, /* wait 60 sec */
          1000); /* delay ms for poll */
   System.out.println("Index acct_episodes on " + tableName + " is created");
}

La classe borneo.TableRequest est utilisée pour créer un index sur une table. Tous les appels à borneo.NoSQLHandle.table_request() sont asynchrones. Il est donc nécessaire de vérifier le résultat et d'appeler borneo.TableResult.wait_for_completion() pour attendre la fin de l'opération. Voir Informations de référence sur l'API de la trousse SDK Python pour Oracle NoSQL pour plus de détails sur table_request et ses méthodes.

Téléchargez le code complet Indexes.py à partir des exemples ici.

#create an index
def create_index(handle):
   statement = '''CREATE INDEX acct_episodes ON stream_acct (acct_data.contentStreamed[].seriesInfo[].episodes[]  AS ANYATOMIC)'''
   request = TableRequest().set_statement(statement)
   table_result = handle.do_table_request(request, 40000, 3000)
   table_result.wait_for_completion(handle, 40000, 3000)
   print('Index acct_episodes on the table stream_acct is created')

La classe TableRequest est utilisée pour créer un index sur une table. L'exécution des opérations spécifiées par TableRequest est asynchrone. Il s'agit d'opérations potentiellement de longue durée. Cette demande est utilisée comme entrée d'une opération Client.DoTableRequest(), qui retourne une valeur TableResult qui peut être utilisée pour scruter jusqu'à ce que la table atteigne l'état souhaité. Voir Informations de référence sur l'API de la trousse SDK Oracle NoSQL Go pour plus de détails sur les différentes méthodes de la classe TableRequest.

Téléchargez le code complet Indexes.go à partir des exemples ici.

//create an index on a table
func createIndex(client *nosqldb.Client, err error, tableName string)(){
   stmt := fmt.Sprintf("CREATE INDEX acct_episodes ON %s "+
		"(acct_data.contentStreamed[].seriesInfo[].episodes[]  AS ANYATOMIC)",tableName)
   tableReq := &nosqldb.TableRequest{
		Statement: stmt,
   }
   tableRes, err := client.DoTableRequest(tableReq)
   if err != nil {
      fmt.Printf("cannot initiate CREATE INDEX request: %v\n", err)
      return
   }
   // The create index request is asynchronous, wait for index creation to complete.
   _, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
   if err != nil {
      fmt.Printf("Error finishing CREATE INDEX request: %v\n", err)
      return
   }
   fmt.Println("Created Index acct_episodes on table ", tableName)
   return
}

Vous pouvez créer un index sur une table à l'aide de la méthode tableDDL. Cette méthode est asynchrone et retourne une promesse de TableResult. TableResult est un objet JavaScript simple qui encapsule l'état de la table. Pour plus de détails sur la méthode, voir la classe NoSQLClient.

Téléchargez le code JavaScript complet Indexes.js à partir des exemples ici et le code TypeScript complet Indexes.ts à partir des exemples ici.

//creates an index
async function createIndex(handle) {
   const crtindDDL = `CREATE INDEX acct_episodes ON ${TABLE_NAME}(acct_data.contentStreamed[].seriesInfo[].episodes[]  AS ANYATOMIC)`;
   let res =  await handle.tableDDL(crtindDDL);
   console.log('Index acct_episodes is created on table:' + TABLE_NAME);
}

Pour créer un index sur une table, utilisez l'une des méthodes ExecuteTableDDLAsync ou ExecuteTableDDLWithCompletionAsync. Ces deux méthodes retournent Task<TableResult>. L'instance TableResult contient le statut de l'opération LDD telle que TableState et le schéma de table. Voir Informations de référence sur l'API de la trousse SDK Oracle NoSQL Dotnet pour plus de détails sur ces méthodes.

Téléchargez le code complet Indexes.cs à partir des exemples ici.

// Creates an index on a table
private static async Task createIndex(NoSQLClient client){
   var sql =
      $@"CREATE INDEX acct_episodes ON {TableName}(acct_data.contentStreamed[].seriesInfo[].episodes[]  AS ANYATOMIC)";
   var tableResult = await client.ExecuteTableDDLAsync(sql);
   // Wait for the operation completion
   await tableResult.WaitForCompletionAsync();
   Console.WriteLine(" Index acct_episodes is created on table Table {0}",
                tableResult.TableName);
}

Création de tables

Utilisation des API pour créer un index

Utiliser des commandes SQL

Utilisation de l'API TableRequest

Rubriques connexes