Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.
Migrazione di indici basati su funzioni da Oracle a PostgreSQL
Creato da Veeranjaneyulu Grandhi (AWS) e Navakanth Talluri (AWS)
Ambiente: produzione | Fonte: Oracle | Obiettivo: PostgreSQL |
Tipo R: Re-architect | Carico di lavoro: Oracle | Tecnologie: migrazione; database |
Riepilogo
Gli indici sono un modo comune per migliorare le prestazioni dei database. Un indice consente al server del database di trovare e recuperare righe specifiche molto più velocemente di quanto potrebbe fare senza un indice. Ma gli indici aggiungono anche un sovraccarico all'intero sistema di database, quindi devono essere usati in modo sensato. Gli indici basati su funzioni, che si basano su una funzione o un'espressione, possono includere più colonne ed espressioni matematiche. Un indice basato su funzioni migliora le prestazioni delle query che utilizzano l'espressione dell'indice.
A livello nativo, PostgreSQL non supporta la creazione di indici basati su funzioni utilizzando funzioni la cui volatilità è definita stabile. Tuttavia, è possibile creare funzioni simili con volatilità e utilizzarle nella creazione di indici. IMMUTABLE
Una IMMUTABLE funzione non può modificare il database ed è garantito che restituirà gli stessi risultati con gli stessi argomenti per sempre. Questa categoria consente all'ottimizzatore di valutare preventivamente la funzione quando una query la richiama con argomenti costanti.
Questo modello aiuta a migrare gli indici basati sulle funzioni Oracle quando vengono utilizzati con funzioni come to_charto_date, e verso to_number l'equivalente PostgreSQL.
Prerequisiti e limitazioni
Prerequisiti
Un account Amazon Web Services (AWS) attivo
Un'istanza di database Oracle di origine con il servizio listener configurato e funzionante
Familiarità con i database PostgreSQL
Limitazioni
Il limite di dimensione del database è di 64 TB.
Le funzioni utilizzate nella creazione dell'indice devono essere IMMUTABILI.
Versioni del prodotto
Tutte le edizioni del database Oracle per le versioni 11g (versioni 11.2.0.3.v1 e successive) e fino a 12.2 e 18c
PostgreSQL 9.6 e versioni successive
Architettura
Stack tecnologico di origine
Un database Oracle in locale o su un'istanza Amazon Elastic Compute Cloud (Amazon EC2) o un'istanza Amazon RDS for Oracle DB
Stack tecnologico Target
Qualsiasi motore PostgreSQL
Strumenti
pGAdmin 4 è uno strumento di gestione open source per Postgres. Lo strumento pgAdmin 4 fornisce un'interfaccia grafica per la creazione, la manutenzione e l'utilizzo di oggetti di database.
Oracle SQL Developer è un ambiente di sviluppo integrato (IDE) per lo sviluppo e la gestione di database Oracle in implementazioni tradizionali e cloud.
Epiche
| Attività | Descrizione | Competenze richieste |
|---|---|---|
| Crea un indice basato su funzioni su una colonna utilizzando la funzione to_char. | Utilizzate il codice seguente per creare l'indice basato sulle funzioni.
Nota: PostgreSQL non consente la creazione di un indice basato su funzioni senza la clausola. | DBA, sviluppatore di app |
| Verifica la volatilità della funzione. | Per controllare la volatilità della funzione, usa il codice nella sezione Informazioni aggiuntive. | DBA |
| Attività | Descrizione | Competenze richieste |
|---|---|---|
| Crea una funzione wrapper. | Per creare una funzione wrapper, usa il codice nella sezione Informazioni aggiuntive. | Sviluppatore PostgreSQL |
| Crea un indice utilizzando la funzione wrapper. | Utilizzate il codice nella sezione Informazioni aggiuntive per creare una funzione definita dall'utente con la parola chiave Se una funzione definita dall'utente viene creata in uno schema comune (dall'esempio precedente), aggiornatela come mostrato.
| DBA, sviluppatore PostgreSQL |
| Attività | Descrizione | Competenze richieste |
|---|---|---|
| Convalida la creazione dell'indice. | Verifica che l'indice debba essere creato, in base ai modelli di accesso alle query. | DBA |
| Verifica che l'indice possa essere usato. | Per verificare se l'indice basato sulla funzione viene rilevato da PostgreSQL Optimizer, esegui un'istruzione SQL utilizzando explain o explain analyze. Usa il codice nella sezione Informazioni aggiuntive. Se possibile, raccogli anche le statistiche della tabella. Nota: se noti il piano di spiegazione, l'ottimizzatore PostgreSQL ha scelto un indice basato sulle funzioni a causa della condizione del predicato. | DBA |
Risorse correlate
Informazioni aggiuntive
Crea una funzione wrapper
CREATE OR REPLACE FUNCTION myschema.to_char(var1 timestamp without time zone, var2 varchar) RETURNS varchar AS $BODY$ select to_char(var1, 'YYYYMMDD'); $BODY$ LANGUAGE sql IMMUTABLE;
Crea un indice utilizzando la funzione wrapper
postgres=# create function common.to_char(var1 timestamp without time zone, var2 varchar) RETURNS varchar AS $BODY$ select to_char(var1, 'YYYYMMDD'); $BODY$ LANGUAGE sql IMMUTABLE; CREATE FUNCTION postgres=# create index funcindex_idx on funcindex(common.to_char(col1,'DD-MM-YYYY HH24:MI:SS')); CREATE INDEX
Controlla la volatilità della funzione
SELECT DISTINCT p.proname as "Name",p.provolatile as "volatility" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang WHERE n.nspname OPERATOR(pg_catalog.~) '^(pg_catalog)$' COLLATE pg_catalog.default AND p.proname='to_char'GROUP BY p.proname,p.provolatile ORDER BY 1;
Verifica che l'indice possa essere utilizzato
explain analyze <SQL> postgres=# explain select col1 from funcindex where common.to_char(col1,'DD-MM-YYYY HH24:MI:SS') = '09-08-2022 16:00:57'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Index Scan using funcindex_idx on funcindex (cost=0.42..8.44 rows=1 width=8) Index Cond: ((common.to_char(col1, 'DD-MM-YYYY HH24:MI:SS'::character varying))::text = '09-08-2022 16:00:57'::text) (2 rows)
