Utilizzo delle API per creare un indice

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

Uso dei comandi SQL

È possibile creare un indice utilizzando il comando CREATE INDEX.

Creare un unico indice di campo:

Esempio: creare un indice sul 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 tipo di dati string nella tabella baggageInfo.

Creare un indice composto:

Esempio: creare un indice per il nome completo e il numero di telefono dei passeggeri.

CREATE INDEX compindex_namephone ON baggageInfo(fullName,contactPhone)

Quanto sopra è un esempio di indice composito. L'indice viene creato su due campi nello schema baggageInfo, sul nome completo e sul numero di telefono del contatto.

Nota: è possibile avere uno o più campi di questo indice come colonne di schema fisso.

Creare un indice JSON:

Un indice viene chiamato indice JSON se almeno uno dei campi si trova all'interno dei dati JSON. Poiché JSON è privo di schema, il tipo di dati di un campo JSON indicizzato può essere diverso tra le righe. Quando si crea un indice nei campi JSON, se non si è sicuri del tipo di dati previsto per il campo JSON, è possibile utilizzare il tipo di dati anyAtomic. In alternativa, è possibile specificare uno dei tipi di dati atomici di Oracle NoSQL Database. A tale scopo, 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 cartellino delle valigie 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 si fornisce un tipo di dati per il campo tagnum durante la creazione dell'indice.

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 dell'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. È perché i valori numerici di qualsiasi tipo nel campo indicizzato verranno espressi in Numero prima di essere memorizzati nell'indice. Questo cast richiede tempo CPU e la memoria risultante per il numero sarà maggiore della memoria originale per il numero.

Crea un indice semplice:

Un indice è chiamato indice semplice se, per ogni riga di dati nella tabella, è stata creata una voce 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 in 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 in lastSeenTimeGmt e bagArrivalDate e lastSeenTimeStation, il tutto dal documento JSON bagInfo nel campo JSON delle informazioni nella tabella baggageInfo. Se la valutazione di un percorso di indice semplice restituisce un risultato vuoto, il valore speciale EMPTY viene utilizzato come voce di indice. 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 multichiave:

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

Esempio 1: indice a più chiavi: creare un indice nell'array di informazioni sulla serie dell'applicazione dell'account di streaming.

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

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

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 nell'array episodes[] nell'array seriesInfo[] nell'JSON acct_data della tabella stream_acct.

Esempio 3: indice a più tasti composto:

Un indice viene definito indice multichiave composito se viene creato su più campi e almeno uno di questi campi è multichiave. Un indice multichiave composto può avere una combinazione di percorsi indice multichiave 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 multichiave composito con un percorso indice multichiave 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.

Fare riferimento alla sezione Specifications & Restrictions on Multikey Index per informazioni sulle limitazioni sull'indice multichiave.

Creare un indice con clausola NO NULLS

È possibile creare un indice con la clausola facoltativa WITH NO NULLS. 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 sopra 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 di 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, tale indice può essere utilizzato da una query solo se la query ha 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à di 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. Indica al processore di query che, per qualsiasi utente di streaming, l'array contentStreamed non può contenere due o più visualizzazioni con lo stesso ID di visualizzazione. La restrizione è 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 generato un errore e l'operazione di inserimento non riesce.

Ottimizzazione nel runtime della query:

Quando si crea un indice con chiavi univoche per riga, l'indice contiene meno voci rispetto 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 meno 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 la condizione di filtro modification_time.

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

Questa query restituisce tutte le righe il cui tempo di modifica più recente è successivo a 2019-08-01T10:45:00. Utilizza l'indice idx_modtime definito sopra. È 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 la condizione di filtro length.

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 sopra. È possibile verificarlo visualizzando il piano di query utilizzando il comando show query.

Esempio 3: utilizzo di un percorso di indice a 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 lo spettacolo è stato guardato.

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

Di seguito è riportato un esempio di query che utilizza questo indice. La query conta il numero di utenti che hanno guardato qualsiasi episodio della serie 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"
    }
  ]
}

Uso 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 lunghe. TableResult viene restituito dalle operazioni TableRequest e incapsula lo stato della tabella. Per ulteriori dettagli sulla classe TableRequest e sui relativi metodi, consultare Oracle NoSQL Java SDK API Reference.

Scaricare 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 quindi è necessario controllare il risultato e chiamare borneo.TableResult.wait_for_completion() per attendere il completamento dell'operazione. Per ulteriori dettagli su table_request e sui relativi metodi, consultare 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 lunghe. 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, vedere 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 in una tabella utilizzando il metodo tableDDL. Questo metodo è asincrono e restituisce una promessa di TableResult. TableResult è un oggetto JavaScript normale che incapsula lo stato della tabella. Per i dettagli del metodo, vedere la classe NoSQLClient.

Scarica il codice JavaScript completo Indexes.js dagli esempi riportati qui e il codice TypeScript completo Indexes.ts dagli esempi riportati 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 in una tabella, utilizzare i metodi ExecuteTableDDLAsync o ExecuteTableDDLWithCompletionAsync. Entrambi i 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 la riferimento API Oracle NoSQL Dotnet SDK.

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

Creazione delle tabelle

Utilizzo delle API per creare un indice

Uso dei comandi SQL

Uso dell'API TableRequest

Argomenti correlati