Utilisation d'API pour créer un index

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

Utilisation de commandes SQL

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

Créez un index de champ unique :

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

CREATE INDEX fixedschema_conf ON baggageInfo(confNo)

L'exemple ci-dessus illustre un index de schéma fixe à colonne unique. L'index est créé sur le champ confNo dont le type de données est 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)

L'exemple ci-dessus illustre un 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.

Remarque : vous pouvez avoir un ou plusieurs champs de cet index en tant que colonnes de schéma fixes.

Créez un index JSON :

Un index est appelé index JSON si au moins un des champs se trouve dans des 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 ligne à l'autre. Lorsque vous créez un index sur des champs JSON, si vous n'êtes pas sûr du type de données à attendre pour le champ JSON, vous pouvez utiliser le type de données anyAtomic. Vous pouvez également indiquer l'un des types de données atomiques Oracle NoSQL Database. Pour ce faire, déclarez un type de données en utilisant le mot-clé AS en regard 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)

Voici un exemple d'index JSON. L'index est créé sur le champ tagnum présent dans le champ JSON baginfo de la table baggageInfo. Vous indiquez un type de données pour le champ tagnum lors de la création de l'index.

La création d'un index JSON échoue si la table associée contient des lignes avec 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 ligne n'est pas conforme au type de données déclaré dans l'index JSON.

Exemple 2 : créer un index sur l'itinéraire des passagers.

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

La déclaration d'un chemin d'index JSON comme anyAtomic présente 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 par ordre croissant. Lorsque ces valeurs sont stockées dans l'index, elles sont triées comme suit :

Nombres
Chaîne
valeur booléenne

Cependant, cet avantage est compensé par les coûts d'espace et de CPU. En effet, les valeurs numériques de tout type dans le champ indexé seront converties en nombre avant d'être stockées dans l'index. Cette conversion 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 renvoie 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 de tableau, de carte ou de 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)

Voici un exemple d'index simple créé sur un document JSON dans un champ JSON. L'index est créé sur lastSeenTimeGmt, 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 comme entrée d'index. Dans l'exemple ci-dessus, s'il n'y a pas d'entrée lastSeenTimeGmt, 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 transmission en continu.

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

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

Exemple 2 : index multiclé imbriqué : créez un index dans le tableau de détails de l'é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 dans un tableau qui à son tour est présent dans 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 multi-clés imbriqué où le champ est présent dans un tableau présent dans un autre tableau. L'index est créé sur le tableau episodes[] dans le tableau seriesInfo[] dans le JSON acct_data de la table stream_acct.

Exemple 3 : index multiclé 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 à plusieurs clés composite peut comporter une combinaison de chemins d'index à plusieurs clés et de chemins d'index simples.

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

L'exemple ci-dessus illustre un index à plusieurs clés composite ayant un chemin d'index à plusieurs clés et un chemin d'index simple. L'index est créé sur le champ country et le tableau episodes[] dans la colonne JSON acct_data de la table stream_acct.

Reportez-vous à Spécifications et restrictions sur l'index multiclé pour en savoir plus sur les restrictions sur l'index multiclé.

Créer un index avec une clause NO NULLS

Vous pouvez créer un index avec la clause facultative WITH NO NULLS. Dans ce cas, les lignes avec 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. Cela permet de réduire la surcharge de temps et d'espace pendant l'indexation.
Toutefois, l'utilisation de ces index par des 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 comme prédicats d'index pour cet index.
En fait, un tel index ne peut être utilisé par une requête 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 ligne

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 la requête ci-dessus, un index est créé sur showId et il ne peut pas y avoir de doublon showId pour un seul tableau contentStreamed. Le processeur de requête est informé que pour tout utilisateur de transmission en continu, le tableau contentStreamed ne peut pas contenir plusieurs affichages avec le même ID d'affichage. 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 ligne avec les mêmes éléments showId dans un tableau contentStreamed unique, une erreur est générée et l'opération d'insertion échoue.

Optimisation lors de l'exécution de la requête :

Lorsque vous créez un index avec des clés uniques par ligne, l'index contient moins d'entrées que le nombre d'éléments du tableau contentStreamed. Vous pouvez écrire une requête efficace pour utiliser cet index. L'utilisation d'un tel index par l'interrogation donnerait moins de résultats 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 lignes de la table BaggageInfo en fonction de leur heure de dernière modification :

CREATE INDEX idx_modtime ON BaggageInfo(modification_time())

Cet index sera utilisé dans une requête dont la condition de filtre est modification_time.

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

Cette requête renvoie toutes les lignes dont l'heure de modification la plus récente est postérieure au 01/08/2019T10:45:00. Il utilise l'index idx_modtime défini ci-dessus. Pour ce faire, consultez le plan de requête à l'aide de la commande show query.

Exemple 2 : créez un index qui indexe les lignes 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 requête dont la condition de filtre est length.

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

Cette requête renvoie toutes les lignes dont la longueur du champ de routage est supérieure à 10. Il utilise l'index idx_routlen défini ci-dessus. Pour ce faire, consultez le plan de requête à 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 illustré ci-dessous. La requête compte le nombre d'utilisateurs qui ont regardé un épisode de l'émission 16 au cours de 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 requête utilisera l'index idx_showid_year_month. Pour ce faire, consultez le plan de requête à 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 permet de créer un index sur une table. L'exécution des opérations indiquées par cette demande est asynchrone. Il s'agit d'opérations potentiellement longues. TableResult est renvoyé par les opérations TableRequest et encapsule l'état de la table. Pour plus de détails sur la classe TableRequest et ses méthodes, reportez-vous à Référence d'API de kit SDK Java Oracle NoSQL.

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 permet de créer un index sur une table. Tous les appels vers 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. Pour plus de détails sur table_request et ses méthodes, reportez-vous à Référence d'API de kit SDK Python Oracle NoSQL.

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 permet de créer un index sur une table. L'exécution des opérations indiquées par TableRequest est asynchrone. Il s'agit d'opérations potentiellement longues. Cette demande est utilisée en tant qu'entrée d'une opération Client.DoTableRequest(), qui renvoie une valeur TableResult qui peut être utilisée pour interroger jusqu'à ce que la table atteigne l'état souhaité. Pour plus de détails sur les différentes méthodes de la classe TableRequest, reportez-vous à Référence d'API du kit SDK Go Oracle NoSQL.

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 renvoie la promesse TableResult. TableResult est un objet JavaScript simple qui encapsule l'état de la table. Pour plus de détails sur la méthode, reportez-vous à 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 renvoient Task<TableResult>. L'instance TableResult contient le statut de l'opération LDD, telle que TableState et le schéma de table. Pour plus de détails sur ces méthodes, reportez-vous à Référence d'API du kit SDK Oracle NoSQL Dotnet.

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 d'API pour créer un index

Utilisation de commandes SQL

Utilisation de l'API TableRequest

Rubriques connexes