Utilizzo delle API per creare un indice

È possibile creare un indice per una tabella NoSQL utilizzando i comandi SQL o l'API TableRequest.

Argomenti correlati

Uso dei comandi SQL

È possibile creare un indice utilizzando il comando CREATE INDEX.

Creare un singolo indice di campo:

Esempio: creare un indice per il codice di prenotazione dei passeggeri.
CREATE INDEX fixedschema_conf ON baggageInfo(confNo)

Quanto sopra è un esempio di indice di schema fisso a colonna singola. L'indice viene creato nel campo confNo con il tipo di dati string nella tabella baggageInfo.

Creare un indice composto:

Esempio: creare un indice con il nome completo e il numero di telefono dei passeggeri.
CREATE INDEX compindex_namephone ON baggageInfo(fullName,contactPhone)
Quanto sopra è un esempio di indice composto. L'indice viene creato in due campi dello schema baggageInfo, nel nome completo e nel numero di telefono del contatto.

Nota

È possibile disporre di uno o più campi di questo indice come colonne di schema fisso.

Crea un indice JSON:

Un indice viene definito indice JSON se almeno uno dei campi si trova all'interno di dati JSON. Poiché JSON è privo di schema, il tipo di dati di un campo JSON indicizzato potrebbe essere diverso tra le righe. Quando si crea un indice nei campi JSON, se non si è certi del tipo di dati previsto per il campo JSON, è possibile utilizzare il tipo di dati anyAtomic. In alternativa, puoi specificare uno dei tipi di dati atomici di Oracle NoSQL Database. Per farlo, dichiarare un tipo di dati utilizzando la parola chiave AS accanto a ogni percorso di indice nel campo JSON.

Esempio 1: creare un indice sul numero di tag delle borse passeggeri.
CREATE INDEX jsonindex_tagnum ON baggageInfo(bagInfo[].tagnum as INTEGER)

Quanto sopra è un esempio di indice JSON. L'indice viene creato nel campo tagnum presente nel campo JSON baginfo nella tabella baggageInfo. Si noti che durante la creazione dell'indice viene fornito un tipo di dati per il campo tagnum.

La creazione di un indice JSON non riuscirà se la tabella associata contiene righe con dati che violano il tipo di dati dichiarato. Analogamente, dopo aver creato un indice JSON, un'operazione di inserimento/aggiornamento non riuscirà se la nuova riga non è conforme al tipo di dati dichiarato nell'indice JSON.

Esempio 2: creare un indice sul percorso dei passeggeri.
CREATE INDEX jsonindex_routing ON baggageInfo(bagInfo[].routing as ANYATOMIC)
La dichiarazione di un percorso di indice JSON come anyAtomic ha il vantaggio di consentire al campo JSON indicizzato di avere valori di vari tipi di dati. Le voci di indice vengono ordinate in ordine crescente. Quando questi valori vengono memorizzati nell'indice, vengono ordinati come indicato di seguito.
  • Numeri
  • Stringa
  • boolean

Tuttavia, questo vantaggio è compensato dai costi di spazio e CPU. Questo perché i valori numerici di qualsiasi tipo nel campo indicizzato verranno convertiti in Numero prima di essere memorizzati nell'indice. Questo cast richiede tempo CPU e lo storage risultante per il numero sarà più grande dello storage originale per il numero.

Crea un indice semplice:

Un indice è chiamato indice semplice se, per ogni riga di dati nella tabella, c'è una voce creata nell'indice. L'indice restituirà un singolo valore di tipo dati atomico o qualsiasi valore speciale (SQL NULL, JSON NULL, EMPTY). In sostanza, i percorsi di indice di un indice semplice non devono restituire un array o una mappa o un tipo di dati nidificato.

Esempio: creare un indice su tre campi, l'ultima volta che il sacchetto è stato visualizzato, l'ultima stazione visualizzata e la data e l'ora di arrivo.
CREATE INDEX simpleindex_arrival ON baggageInfo(bagInfo[].lastSeenTimeGmt as ANYATOMIC,
bagInfo[].bagArrivalDate as ANYATOMIC, bagInfo[].lastSeenTimeStation as ANYATOMIC)

Quanto sopra è un esempio di indice semplice creato su un documento JSON in un campo JSON. L'indice viene creato sul lastSeenTimeGmt e bagArrivalDate e lastSeenTimeStation, tutti dal documento JSON bagInfo nel campo JSON informazioni nella tabella baggageInfo. Se la valutazione di un percorso di indice semplice restituisce un risultato vuoto, come voce di indice viene utilizzato il valore speciale VUOTO. Nell'esempio precedente, se nel documento JSON bagInfo non è presente alcuna voce lastSeenTimeGmt, bagArrivalDate o lastSeenTimeStation oppure se non è presente alcun array JSON bagInfo, il valore speciale EMPTY viene indicizzato.

Crea un indice con più chiavi:

Un indice è chiamato indice multichiave se, per ogni riga di dati nella tabella, ci sono più voci create nell'indice. In un indice multichiave esiste almeno un percorso indice che utilizza un array o un tipo di dati nidificato. In un indice a più chiavi, per ogni riga di tabella, le voci di indice vengono create su tutti gli elementi degli array da indicizzare.

Esempio 1: indice multichiave: creare un indice nell'array di informazioni serie dell'applicazione dell'account di streaming.
CREATE INDEX multikeyindex1 ON stream_acct (acct_data.contentStreamed[].seriesInfo[] AS ANYATOMIC)

L'indice viene creato sull'array seriesInfo[] nella tabella stream_acct. Qui verranno indicizzati tutti gli elementi dell'array seriesInfo[] in ogni riga della tabella stream_acct.

Esempio 2: indice multichiave nidificato: creare un indice nell'array dei dettagli dell'episodio dell'applicazione dell'account di streaming.

Un indice è un indice multichiave nidificato se viene creato su un campo presente all'interno di un array che a sua volta è presente all'interno di un altro array.
CREATE INDEX multikeyindex2 ON stream_acct (
    acct_data.contentStreamed[].seriesInfo[].episodes[]  AS ANYATOMIC)

Quanto sopra è un esempio di indice multichiave nidificato in cui il campo è presente in un array presente all'interno di un altro array. L'indice viene creato sull'array episodes[] nell'array seriesInfo[] nel JSON acct_data della tabella stream_acct.

Esempio 3: indice multichiave composto:

Un indice viene definito indice multichiave composto se viene creato su più campi e almeno uno di questi campi è multichiave. Un indice multikey composto può avere una combinazione di percorsi indice multikey e percorsi indice semplici.
CREATE INDEX multikeyindex3 ON stream_acct (acct_data.country AS ANYATOMIC,
acct_data.contentStreamed[].seriesInfo[].episodes[]  AS ANYATOMIC)

Quanto sopra è un esempio di indice multikey composto con un percorso indice multikey e un percorso indice semplice. L'indice viene creato nel campo country e nell'array episodes[] nella colonna JSON acct_data della tabella stream_acct.

Vedere Specifiche e restrizioni sull'indice Multikey per informazioni sulle limitazioni dell'indice multikey.

Creare un indice con la clausola NO NULLS

È possibile creare un indice con la clausola WITH NO NULLS facoltativa. In tal caso, le righe con valori NULL e/o EMPTY nei campi indicizzati non verranno indicizzate.
CREATE INDEX nonull_phone ON baggageInfo (contactPhone) WITH NO NULLS
  • La query precedente crea un indice sul numero di telefono dei passeggeri. Se alcuni passeggeri non hanno un numero di telefono, questi campi non faranno parte dell'indice.
  • Gli indici creati con la clausola WITH NO NULLS possono essere utili quando i dati contengono molti valori NULL e/o EMPTY nei campi indicizzati. Ridurrà il sovraccarico di tempo e spazio durante l'indicizzazione.
  • Tuttavia, l'uso di tali indici da parte delle query è limitato. Se viene creato un indice con la clausola WITH NO NULLS, i predicati IS NULL e NOT EXISTS non possono essere utilizzati come predicati indice per tale indice.
  • Infatti, un indice di questo tipo può essere utilizzato da una query solo se la query dispone di un predicato indice per ciascuno dei campi indicizzati.

Crea un indice con chiavi univoche per riga

È possibile creare un indice con chiavi univoche per proprietà riga.
CREATE INDEX idx_showid ON 
stream_acct(acct_data.contentStreamed[].showId AS INTEGER)
WITH UNIQUE KEYS PER ROW

Nella query precedente viene creato un indice su showId e non è possibile duplicare showId per un singolo array contentStreamed. In questo modo il processore di query informa che per qualsiasi utente in streaming, l'array contentStreamed non può contenere due o più visualizzazioni con lo stesso ID visualizzato. La limitazione è necessaria perché se esistessero ID di visualizzazione duplicati, non verrebbero inclusi nell'indice. Se si inserisce una riga con lo stesso showId due o più elementi in un singolo array contentStreamed, viene restituito un errore e l'operazione di inserimento non riesce.

Ottimizzazione nel tempo di esecuzione della query:

Quando si crea un indice con chiavi univoche per riga, l'indice conterrebbe un numero di voci inferiore al numero di elementi nell'array contentStreamed. È possibile scrivere una query efficiente per utilizzare questo indice. L'uso di tale indice da parte della query produrrebbe un minor numero di risultati dalla clausola FROM rispetto a se l'indice non fosse utilizzato.

Esempi di creazione di indici sulle funzioni:

Esempio 1: creare un indice che indicizzi le righe della tabella BaggageInfo in base all'ora di modifica più recente:
CREATE INDEX idx_modtime ON BaggageInfo(modification_time())
Questo indice verrà utilizzato in una query con modification_time come condizione di filtro.
SELECT * FROM BaggageInfo $u WHERE 
modification_time($u) > "2019-08-01T10:45:00"

Questa query restituisce tutte le righe la cui ora di modifica più recente è successiva a 2019-08-01T10:45:00. Utilizza l'indice idx_modtime definito in precedenza. È possibile verificarlo visualizzando il piano di query utilizzando il comando show query.

Esempio 2: creare un indice che indicizzi le righe della tabella BaggageInfo sulla lunghezza del campo di instradamento.
CREATE INDEX idx_routlen ON BaggageInfo (length(bagInfo[].routing as string))
Questo indice verrà utilizzato in una query con length come condizione di filtro.
SELECT * from BaggageInfo $bag where length($bag.bagInfo[].routing) > 10

Questa query restituisce tutte le righe la cui lunghezza del campo di instradamento è maggiore di 10. Utilizza l'indice idx_routlen definito in precedenza. È possibile verificarlo visualizzando il piano di query utilizzando il comando show query.

Esempio 3: utilizzo di un percorso di indice con più chiavi

Nell'esempio seguente, gli utenti nella tabella stream_acct vengono indicizzati in base all'ID degli spettacoli che guardano e all'anno e al mese delle date in cui è stato osservato lo spettacolo.
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 esempio di query che utilizza questo indice è riportato di seguito. La query conta il numero di utenti che hanno guardato qualsiasi episodio del programma 16 nell'anno 2022. 
SELECT count(*) FROM stream_acct s1 WHERE EXISTS 
s1.acct_data.contentStreamed[$element.showId = 16].seriesInfo.
episodes[substring($element.date, 0, 4) = "2022"]
Questa query utilizzerà l'indice idx_showid_year_month. È possibile verificarlo visualizzando il piano di query utilizzando il comando 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"
    }
  ]
}

Utilizzo dell'API TableRequest

È possibile utilizzare l'API TableRequest per creare un indice in una tabella NoSQL.

La classe TableRequest viene utilizzata per creare un indice in una tabella. L'esecuzione delle operazioni specificate da questa richiesta è asincrona. Si tratta di operazioni potenzialmente di lunga durata. TableResult viene restituito dalle operazioni TableRequest e incapsula lo stato della tabella. Per ulteriori dettagli sulla classe TableRequest e sui relativi metodi, vedere Oracle NoSQL Java SDK API Reference.

Scarica il codice completo Indexes.java dagli esempi qui.

/**
* 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 viene utilizzata per creare un indice in una tabella. Tutte le chiamate a borneo.NoSQLHandle.table_request() sono asincrone, pertanto è necessario controllare il risultato e chiamare borneo.TableResult.wait_for_completion() per attendere il completamento dell'operazione. Per ulteriori informazioni su table_request e sui relativi metodi, vedere Oracle NoSQL Python SDK API Reference.

Scarica il codice completo Indexes.py dagli esempi qui.

#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 viene utilizzata per creare un indice in una tabella. L'esecuzione delle operazioni specificate da TableRequest è asincrona. Si tratta di operazioni potenzialmente di lunga durata. Questa richiesta viene utilizzata come input di un'operazione Client.DoTableRequest(), che restituisce un valore TableResult che può essere utilizzato per eseguire il polling fino a quando la tabella non raggiunge lo stato desiderato. Per ulteriori dettagli sui vari metodi della classe TableRequest, consulta Oracle NoSQL Go SDK API Reference.

Scarica il codice completo Indexes.go dagli esempi qui.

//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
}

È possibile creare un indice su una tabella utilizzando il metodo tableDDL. Questo metodo è asincrono e restituisce una promessa di TableResult. TableResult è un semplice oggetto JavaScript che incapsula lo stato della tabella. Per ulteriori informazioni sul metodo, vedere la classe NoSQLClient.

Scarica il codice JavaScript completo Indexes.js dagli esempi qui e il codice TypeScript completo Indexes.ts dagli esempi qui.

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

Per creare un indice su una tabella, utilizzare uno dei metodi ExecuteTableDDLAsync o ExecuteTableDDLWithCompletionAsync. Entrambi questi metodi restituiscono Task<TableResult>. L'istanza TableResult contiene lo stato dell'operazione DDL, ad esempio TableState e lo schema di tabella. Per ulteriori dettagli su questi metodi, consulta Oracle NoSQL Riferimento API SDK Dotnet.

Scarica il codice completo Indexes.cs dagli esempi qui.
// 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);
}