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.

Rubriques connexes

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

Vous trouverez ci-dessus un exemple d'index de schéma fixe à colonne unique. L'index est créé sur 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.

Remarques :

Un ou plusieurs champs de cet index peuvent être des colonnes de schéma fixe.

Créez un index JSON :

Un index est appelé index JSON si au moins un des champs se trouve dans les données JSON. JSON étant sans schéma, le type de données d'un champ JSON indexé peut être différent d'une ligne à l'autre. Lors de la création d'un index sur des champs JSON, si vous ne savez pas quel 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 à l'aide du 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)

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. 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 échouera si la table associée contient des lignes avec des données qui enfreignent 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éez 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 en tant que 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 nombres 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 données atomiques 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, à quel moment 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, bagArrivalDate et lastSeenTimeStation, tous à 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'existe aucune entrée lastSeenTimeGmt, bagArrivalDate ou lastSeenTimeStation dans le document JSON bagInfo, ou s'il n'existe aucun 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, au moins un chemin d'index 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 en cours d'indexation.

Exemple 1 : index de 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[] dans 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 sur le tableau des détails d'épisode de l'application de compte de transmission en continu.

Un index est un index à plusieurs clés imbriqué s'il est créé sur un champ présent dans un tableau qui est lui-même 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 multiclé 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 l'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)

Ce qui précède est un exemple d'index multi-clé composite ayant un chemin d'index multi-clé 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 à la section Specifications & Restrictions on Multikey index pour en savoir plus sur les restrictions relatives à l'index multi-clé.

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 sur 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 sur les champs indexés. Cela réduit le temps et l'espace pendant l'indexation.
  • Toutefois, l'utilisation de ces index par les requêtes est limitée. 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 a 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 showId en double pour un seul tableau contentStreamed. Cela informe le processeur de requête 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 si des ID d'affichage en double existaient, ils ne seraient pas inclus dans l'index. Si vous insérez une ligne avec le même élément showId 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 la requête :

Lorsque vous créez un index avec des clés uniques par ligne, il contient moins d'entrées que le nombre d'éléments dans le tableau contentStreamed. Vous pouvez écrire une requête efficace pour utiliser cet index. L'utilisation d'un tel index par l'interrogation produirait 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 l'heure de modification la plus récente :
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 à 2019-08-01T10 :45:00. Il utilise l'index idx_modtime défini ci-dessus. Vous pouvez le vérifier en consultant 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. Vous pouvez le vérifier en consultant 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 dans 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))
Exemple de requête utilisant cet index : 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. Vous pouvez le vérifier en consultant 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 spécifié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 d'informations sur la classe TableRequest et ses méthodes, reportez-vous à Référence d'API du 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 d'informations sur table_request et ses méthodes, reportez-vous à Référence d'API du 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 pouvant être utilisée pour effectuer une interrogation jusqu'à ce que la table atteigne l'état souhaité. Pour plus d'informations 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 d'informations sur les méthodes, 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 d'informations sur ces méthodes, reportez-vous à Référence d'API du kit SDK Dotnet Oracle NoSQL.

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