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

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éé sur le champ confNo ayant 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 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 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 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 à 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 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 le trajet 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 d'autoriser le champ JSON indexé à 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
  • boolean

Toutefois, 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. Ce casting prend du temps CPU, et le stockage résultant pour le numéro sera plus grand que le stockage d'origine pour le numéro.

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, quand l'entité a été vue 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 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 multikey :

Un index est appelé index à clés multiples si, pour chaque ligne de données de la table, plusieurs entrées sont créées dans l'index. Dans un index à clés multiples, il existe au moins un chemin d'index qui utilise un tableau ou un type de données imbriqué. Dans un index à clés multiples, 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 à clés multiples : 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[] dans chaque rangée 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 diffusion en continu.

Un index est un index multiclé imbriqué s'il est créé sur un champ présent dans un tableau qui est à son tour 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 qui est présent à l'intérieur d'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 multiclé composite peut comporter 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 multikey composite ayant un chemin d'index multikey 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.

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 avec clause NO 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éduira le temps et l'espace au cours 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 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 rangée

Vous pouvez créer un index avec des clés uniques par propriété de rangée.
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 showId ne peut pas être en double pour un tableau contentStreamed unique. Cela informe le processeur d'interrogation que pour tout utilisateur de diffusion en continu, le tableau contentStreamed ne peut pas contenir deux émissions ou plus ayant le même ID affichage. La restriction est nécessaire car si des ID show en double existaient, ils ne seraient pas inclus dans l'index. Si vous insérez une rangée avec le même élément showId deux ou plusieurs 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 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 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 rangées de la table BaggageInfo selon leur dernière heure de modification :
CREATE INDEX idx_modtime ON BaggageInfo(modification_time())
Cet index sera utilisé dans une interrogation dont la condition de filtre est modification_time.
SELECT * FROM BaggageInfo $u WHERE 
modification_time($u) > "2019-08-01T10:45:00"

Cette interrogation retourne toutes les rangées 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 vérifier cela 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 gamme d'opérations.
CREATE INDEX idx_routlen ON BaggageInfo (length(bagInfo[].routing as string))
Cet index sera utilisé dans une interrogation dont la condition de filtre est length.
SELECT * from BaggageInfo $bag where length($bag.bagInfo[].routing) > 10

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

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

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))
Un exemple d'interrogation utilisant cet index est présenté ci-dessous. L'interrogation compte le nombre d'utilisateurs ayant 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 interrogation utilisera l'index idx_showid_year_month. Vous pouvez vérifier cela 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 dans les 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 dans les 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 en tant qu'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 Go pour Oracle NoSQL pour plus de détails sur les différentes méthodes de la classe TableRequest.

Téléchargez le code complet Indexes.go dans les 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 brut 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. Pour plus de détails sur ces méthodes, voir Informations de référence sur l'API de la trousse SDK Dotnet pour Oracle NoSQL.

Téléchargez le code complet Indexes.cs dans les 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);
}