Questo documento fa parte di una serie che fornisce informazioni chiave e indicazioni relative alla pianificazione e all'esecuzione delle migrazioni dei database Oracle® 11g/12c in Cloud SQL per PostgreSQL versione 12. Questo documento illustra le differenze di base tra il database Oracle® e Cloud SQL per PostgreSQL in relazione alla creazione di utenti, schemi, tabelle, indici e viste.
Oltre alla parte introduttiva della configurazione, la serie include le seguenti parti:
- Migrazione degli utenti Oracle a Cloud SQL per PostgreSQL: terminologia e funzionalità
- Migrazione degli utenti Oracle a Cloud SQL per PostgreSQL: tipi di dati, utenti e tabelle
- Migrazione degli utenti Oracle a Cloud SQL per PostgreSQL: query, stored procedure, funzioni e trigger
- Migrazione degli utenti Oracle a Cloud SQL per PostgreSQL: sicurezza, operazioni, monitoraggio e logging
- Migrazione degli utenti e degli schemi di Oracle Database in Cloud SQL per PostgreSQL (questo documento)
Differenze terminologiche tra Oracle e Cloud SQL per PostgreSQL
Oracle e Cloud SQL per PostgreSQL hanno architetture e terminologia diverse per istanze, database, utenti e schemi. Per un riepilogo di queste differenze, consulta la parte relativa alla terminologia di questa serie.
Esportazione delle configurazioni Oracle
Uno dei primi passaggi quando pianifichi una migrazione a Cloud SQL per PostgreSQL è esaminare le impostazioni dei parametri esistenti nel database Oracle di origine. Le impostazioni relative all'allocazione della memoria, al set di caratteri e ai parametri di archiviazione sono particolarmente utili perché possono fornire informazioni utili per la configurazione e il dimensionamento iniziali dell'ambiente di destinazione Cloud SQL per PostgreSQL. Esistono diversi metodi per estrarre le impostazioni dei parametri Oracle. Di seguito sono riportati alcuni dei fattori più comuni:
- I report AWR (Automatic Workload Repository) contengono i dati di allocazione delle risorse (CPU, RAM), la configurazione dei parametri di istanza e il numero massimo di sessioni attive.
DBA_HIST
,V$OSSTAT
eV$LICENSE
per i dettagli di utilizzo della CPU.- Vista
V$PARAMETER
per i parametri di configurazione del database. - Vista
V$NLS_PARAMETERS
per i parametri del linguaggio del database. DBA_DATA_FILES
per il calcolo delle dimensioni di archiviazione del database.- L'API Oracle
SPFILE
per le configurazioni delle istanze di database. - Strumenti di pianificazione dei job (ad esempio
crontab
) per identificare backup di routine o periodi di manutenzione da prendere in considerazione.
Importazione e configurazione degli utenti in Cloud SQL per PostgreSQL
A livello generale, ogni schema Oracle deve essere creato come il proprio schema in PostgreSQL. In un database Oracle, utente è sinonimo di schema. Ciò significa che viene creato uno schema quando crei un utente. Esiste sempre una relazione 1:1 tra utenti e schemi. In PostgreSQL, gli utenti e gli schemi vengono creati separatamente. È possibile creare un utente senza creare uno schema corrispondente. Per mantenere la stessa struttura di schema o utente Oracle in PostgreSQL, puoi creare uno schema per ogni utente.
La tabella seguente illustra alcuni esempi di conversione:
Tipo di azione | Tipo di database | Confronto tra comandi |
---|---|---|
Crea utente e schema | Oracle |
CREATE USER username IDENTIFIED BY password; |
PostgreSQL |
Utente e schema sono concetti distinti in PostgreSQL, pertanto richiedono due
istruzioni CREATE separateCREATE USER username WITH PASSWORD 'password'; |
|
Assegnazione dei ruoli | Oracle |
GRANT CONNECT TO username; |
PostgreSQL |
GRANT pg_monitor TO username; |
|
Concessione di privilegi | Oracle |
GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username; |
PostgreSQL |
GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username; |
|
Revoca dei privilegi | Oracle |
REVOKE UPDATE ON HR.EMPLOYEES FROM username; |
PostgreSQL |
REVOKE UPDATE ON HR.EMPLOYEES FROM username; |
|
Concedi nome commerciale/super user | Oracle |
GRANT DBA TO username; |
PostgreSQL |
GRANT cloudsqlsuperuser TO username; |
|
Rilascia utente | Oracle |
DROP USER username CASCADE; |
PostgreSQL |
Utente e schema sono concetti distinti in PostgreSQL, pertanto richiedono due
istruzioni DROP separateDROP USER username; |
|
Metadati utente | Oracle |
DBA_USERS |
PostgreSQL |
pg_catalog.pg_user |
|
Metadati delle autorizzazioni | Oracle |
DBA_SYS_PRIVS |
PostgreSQL |
pg_catalog.pg_roles |
|
Stringa di connessione dell'interfaccia a riga di comando | Oracle |
sqlplus username/password@host/tns_alias |
PostgreSQL |
Senza richiesta di password:PGPASSWORD=password psql -h hostname -U username -d database_name Con richiesta di password: psql -h hostname -U username -W -d database_name |
Utenti di database Oracle 12c:
In Oracle 12c sono disponibili due tipi di utenti: comuni e locali. Gli utenti comuni vengono creati nel CDB radice, inclusi i PDB. Sono identificati dal prefisso C##
nel nome utente. Gli utenti locali vengono creati solo in un PDB specifico.
È possibile creare utenti del database diversi con nomi utente identici in più PDB. Durante la migrazione da Oracle 12c a PostgreSQL, modifica gli utenti e le autorizzazioni in base all'architettura di PostgreSQL. Ecco due esempi comuni per illustrare
queste differenze:
# Oracle local user SQL> ALTER SESSION SET CONTAINER=pdb; SQL> CREATE USER username IDENTIFIED BY password QUOTA 50M ON USERS; # PostgreSQL user for a single database and schema postgres=> CREATE USER username WITH PASSWORD 'password'; postgres=> GRANT CONNECT TO DATABASE database_name TO username; postgres=> GRANT USAGE ON SCHEMA schema_name TO username; postgres=> -- Optionally, grant object privileges in the schema postgres=> GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO username; postgres=> GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO username; postgres=> GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema_name TO username; postgres=> GRANT ALL ON ALL PROCEDURES IN SCHEMA schema_name TO username; postgres=> GRANT ALL ON ALL ROUTINES IN SCHEMA schema_name TO username; # Oracle common user SQL> CREATE USER c##username IDENTIFIED BY password CONTAINER=ALL; # PostgreSQL user with permissions for all database (use the local user script above and repeat it for each database and schema)
Gestire gli utenti tramite la console Google Cloud
Per visualizzare gli attuali utenti configurati di Cloud SQL per PostgreSQL, vai alla pagina seguente nella console Google Cloud:
Google Cloud > Storage > SQL > Istanza > Utenti
Importazione delle definizioni di tabelle e visualizzazioni
Oracle e PostgreSQL differiscono in termini di sensibilità alle maiuscole. I nomi Oracle non fanno distinzione tra maiuscole e minuscole. I nomi PostgreSQL non fanno distinzione tra maiuscole e minuscole, tranne quando sono racchiusi tra virgolette doppie. Molti strumenti di esportazione di schemi e di generazione SQL per Oracle, come DBMS_METADATA.GET_DDL
, aggiungono automaticamente le virgolette doppie ai nomi degli oggetti. Le virgolette possono causare tutti i tipi di problemi dopo la migrazione.
Ti consigliamo di rimuovere tutte le virgolette che circondano i nomi degli oggetti dalle istruzioni DDL (Data Definition Language) prima di creare gli oggetti in PostgreSQL.
Crea sintassi della tabella
Quando converti le tabelle da Oracle in tipi di dati PostgreSQL, la prima cosa da fare è estrarre le istruzioni di creazione della tabella Oracle dal database di origine. La seguente query di esempio estrae il DDL per la tabella delle località dallo schema RU:
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOCATIONS') FROM DUAL;
CREATE TABLE "HR"."LOCATIONS"
( "LOCATION_ID" NUMBER(4,0),
"STREET_ADDRESS" VARCHAR2(40),
"POSTAL_CODE" VARCHAR2(12),
"CITY" VARCHAR2(30) CONSTRAINT "LOC_CITY_NN" NOT NULL ENABLE,
"STATE_PROVINCE" VARCHAR2(25),
"COUNTRY_ID" CHAR(2),
CONSTRAINT "LOC_ID_PK" PRIMARY KEY ("LOCATION_ID")
...
CONSTRAINT "LOC_C_ID_FK" FOREIGN KEY ("COUNTRY_ID")
REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID") ENABLE
L'output completo include elementi di archiviazione, indici e informazioni sullo spazio delle tabelle, che sono stati omessi perché questi elementi aggiuntivi non sono supportati dall'istruzione CREATE TABLE
di PostgreSQL.
Dopo aver estratto il DDL, rimuovi le virgolette che circondano i nomi ed esegui la conversione della tabella in base alla tabella di conversione dei tipi di dati da Oracle a PostgreSQL. Controlla ogni tipo di dati della colonna per verificare se può essere convertito così com'è o, se non è supportato, scegli un tipo di dati diverso in base alla tabella di conversione. Ad esempio, di seguito è riportato il DDL convertito per la tabella delle località.
CREATE TABLE HR.LOCATIONS (
LOCATION_ID NUMERIC(4,0),
STREET_ADDRESS VARCHAR(40),
POSTAL_CODE VARCHAR(12),
CITY VARCHAR(30) CONSTRAINT LOC_CITY_NN NOT NULL,
STATE_PROVINCE VARCHAR(25),
COUNTRY_ID CHAR(2),
CONSTRAINT LOC_ID_PK PRIMARY KEY (LOCATION_ID),
CONSTRAINT LOC_C_ID_FK FOREIGN KEY (COUNTRY_ID)
REFERENCES HR.COUNTRIES (COUNTRY_ID)
)
Crea tabella con selezione (CTAS)
L'istruzione CREATE TABLE AS SELECT
(CTAS) viene utilizzata per creare una nuova tabella
in base a una tabella esistente. Tieni presente che vengono copiati solo i nomi e i tipi di dati delle colonne, mentre i vincoli e gli indici non vengono copiati. PostgreSQL supporta lo standard ANSI SQL per la funzionalità CTAS ed è compatibile con l'istruzione Oracle CTAS.
Colonne invisibili Oracle 12c
PostgreSQL non supporta le colonne invisibili. Come soluzione alternativa, crea una vista che contenga solo le colonne visibili.
Vincoli della tabella
Oracle offre sei tipi di vincoli di tabella che possono essere definiti durante o dopo la creazione della tabella utilizzando il comando ALTER TABLE
. I tipi di vincoli Oracle sono PRIMARY KEY
, FOREIGN KEY
, UNIQUE
, CHECK
, NOT
NULL
e REF
. Inoltre, Oracle consente all'utente di controllare lo stato di un vincolo tramite le seguenti opzioni:
INITIALLY IMMEDIATE
: controlla il vincolo alla fine di ogni istruzione SQL successiva (lo stato predefinito).DEFERRABLE/NOT DEFERRABLE
: consente l'utilizzo della clausolaSET CONSTRAINT
nelle transazioni successive fino a quando non viene inviata un'istruzioneCOMMIT
INITIALLY DEFERRED
: controlla il vincolo alla fine delle transazioni successive.VALIDATE/NO VALIDATE
: controlla (o non verifica deliberatamente) l'eventuale presenza di errori nelle righe nuove o modificate. Questi parametri variano a seconda che il vincolo siaENABLED
oDISABLED
.ENABLED/DISABLED
: specifica se il vincolo deve essere applicato dopo la creazione (ENABLED
per impostazione predefinita)
PostgreSQL supporta anche sei tipi di vincoli di tabella: PRIMARY KEY
,
FOREIGN KEY
, UNIQUE
, CHECK
, NOT NULL
e EXCLUDE
. Tuttavia, esistono alcune differenze significative tra i tipi di vincolo Oracle e PostgreSQL, tra cui:
- PostgreSQL non supporta il vincolo
REF
di Oracle. - PostgreSQL non crea automaticamente un indice nelle colonne di riferimento per un vincolo di chiave esterna. Se è richiesto un indice, è necessaria un'istruzione
CREATE INDEX
separata nelle colonne di riferimento. - PostgreSQL non supporta la clausola
ON DELETE SET NULL
di Oracle. Questa clausola indica a Oracle di impostare tutti i valori dipendenti nelle tabelle figlio suNULL
quando il record nella tabella padre viene eliminato. - I vincoli su
VIEWS
non sono supportati, ad eccezione diCHECK OPTION
. - PostgreSQL non supporta la disattivazione dei vincoli. PostgreSQL supporta l'opzione
NOT VALID
quando vengono aggiunti una nuova chiave esterna o un nuovo vincolo di controllo utilizzando un'istruzioneALTER TABLE
. Questa opzione indica a PostgreSQL di saltare i controlli di integrità referenziali sui record esistenti nella tabella figlio.
La seguente tabella riassume le principali differenze tra i tipi di vincoli di Oracle e PostgreSQL:
Tipo di vincolo Oracle | Supporto di Cloud SQL per PostgreSQL | Equivalente Cloud SQL per PostgreSQL |
---|---|---|
PRIMARY KEY |
Sì | PRIMARY KEY |
FOREIGN KEY |
Sì | Utilizza la stessa sintassi SQL ANSI di Oracle. Utilizza la clausola ON DELETE per gestire i casi di eliminazione di FOREIGN
KEY record padre. PostgreSQL offre tre opzioni per gestire i casi in cui i dati vengono eliminati dalla tabella padre e un vincolo di FOREIGN KEY fa riferimento a una tabella figlio:
PostgreSQL non supporta la clausola ON DELETE SET NULL di Oracle. Utilizza la clausola ON UPDATE per gestire i casi di aggiornamenti dei record padre FOREIGN
KEY .PostgreSQL offre tre opzioni per gestire gli eventi di aggiornamento dei vincoli di FOREIGN KEY :
PostgreSQL non crea automaticamente un indice nelle colonne di riferimento per un vincolo di chiave esterna. |
UNIQUE |
Sì | Crea un indice UNIQUE per impostazione predefinita. |
CHECK |
Sì | CHECK |
NOT NULL |
Sì | NOT NULL |
REF |
No | Non supportati. |
DEFERRABLE/NOT DEFERRABLE |
Sì | DEFERRABLE/NOT DEFERRABLE |
INITIALLY IMMEDIATE |
Sì | INITIALLY IMMEDIATE |
INITIALLY DEFERRED |
Sì | INITIALLY DEFERRED |
VALIDATE/NO VALIDATE |
No | Non supportati. |
ENABLE/DISABLE |
No | Questa opzione è abilitata per impostazione predefinita. Utilizza l'opzione NOT VALID quando una nuova chiave esterna o un nuovo vincolo di controllo vengono aggiunti alla tabella utilizzando un'istruzione ALTER
TABLE per ignorare i controlli di integrità referenziali sui record esistenti. |
Vincolo per le VISUALIZZAZIONI | No | Non supportato ad eccezione di VIEW WITH CHECK OPTION . |
Metadati dei vincoli | Oracle | DBA_CONSTRAINTS |
PostgreSQL | INFORMATION_SCHEMA.TABLE_CONSTRAINTS |
Colonne virtuali e generate
Le colonne virtuali di Oracle si basano sui risultati del calcolo di altre colonne. Vengono visualizzate come colonne regolari, ma i valori derivano da un calcolo immediato da parte del motore del database Oracle e non sono archiviati nel database. Le colonne virtuali possono essere utilizzate con vincoli, indici, partizionamento delle tabelle e chiavi esterne, ma non possono essere manipolate tramite operazioni DML (Data Manipulation Language).
Le colonne generate di PostgreSQL sono paragonabili alle colonne virtuali di Oracle in termini di funzionalità. Tuttavia, a differenza di Oracle, le colonne generate in PostgreSQL sono archiviate ed è necessario specificare un tipo di dati per ogni colonna generata, il che significa che occupano spazio di archiviazione come se fossero colonne normali.
Esempio di colonna virtuale in Oracle:
SQL> CREATE TABLE PRODUCTS ( PRODUCT_ID INT PRIMARY KEY, PRODUCT_TYPE VARCHAR2(100) NOT NULL, PRODUCT_PRICE NUMBER(6,2) NOT NULL, PRICE_WITH_TAX AS (ROUND(PRODUCT_PRICE * 1.01, 2)) ); SQL> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE) VALUES(1, 'A', 99.99); SQL> SELECT * FROM PRODUCTS;
PRODUCT_ID PRODUCT_TYPE PRODUCT_PRICE PRICE_WITH_TAX ---------- -------------------- ------------- -------------- 1 A 99.99 100.99
Esempio equivalente in PostgreSQL:
postgres=> CREATE TABLE PRODUCTS ( postgres(> PRODUCT_ID INT PRIMARY KEY, postgres(> PRODUCT_TYPE VARCHAR(100) NOT NULL, postgres(> PRODUCT_PRICE NUMERIC(6,2) NOT NULL, postgres(> PRICE_WITH_TAX NUMERIC GENERATED ALWAYS AS (ROUND(PRODUCT_PRICE * 1.01, 2)) STORED postgres(> ); postgres=> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE) VALUES(1, 'A', 99.99); postgres=> SELECT * FROM PRODUCTS;
product_id | product_type | product_price | price_with_tax ------------+--------------+---------------+---------------- 1 | A | 99.99 | 100.99 (1 row)
Indici delle tabelle
Oracle e PostgreSQL forniscono una varietà di algoritmi e tipi di indici di indicizzazione che possono essere utilizzati per una varietà di applicazioni. Di seguito è riportato un elenco degli algoritmi di indicizzazione disponibili in PostgreSQL:
Algoritmo indice | Descrizione |
---|---|
B-tree |
|
Hash |
|
GIN |
|
GiST |
|
SP-GiST |
|
BRIN |
|
La tabella seguente confronta i tipi di indice tra Oracle e PostgreSQL:
Indice Oracle | Descrizione | Supportato da PostgreSQL | Equivalente PostgreSQL |
---|---|---|---|
Indice bitmap | Archivia una bitmap per ogni chiave di indice, ideale per fornire un recupero rapido dei dati per i carichi di lavoro OLAP | No | N/A |
Indice B-tree | Tipo di indice più comune, adatto a diversi carichi di lavoro e può essere configurato nell'ordinamento ASC|DESC . |
Sì | Indice B-Tree |
Indice composto | Creati su due o più colonne per migliorare le prestazioni del recupero dei dati. L'ordinamento delle colonne all'interno dell'indice determina il percorso di accesso. | Sì | Indici a più colonne È possibile specificare fino a 32 colonne quando si crea un indice a più colonne. |
Indice basato su funzioni | Archivia l'output di una funzione applicata ai valori di una colonna della tabella. | Sì | Indici sulle espressioni |
Indice univoco | Un indice B-tree che applica un vincolo UNIQUE sui valori indicizzati in base alla colonna. |
Sì | Indice univoco |
Indice dei domini dell'applicazione | Adatto per l'indicizzazione di dati non relazionali, come dati audio/video, dati LOB e altri tipi non testuali. | No | N/A |
Indice invisibile | Funzionalità Oracle che consente di gestire, gestire e testare gli indici senza influire sul processo decisionale dell'ottimizzatore. | No | Per una soluzione alternativa, puoi creare un indice aggiuntivo in una replica di lettura per scopi di test senza influire sull'attività in corso. |
Tabella organizzata nell'indice | Un tipo di indice che controlla in che modo i dati vengono archiviati a livello di tabella e indice. | No | PostgreSQL non supporta le tabelle organizzate nell'indice. L'istruzione CLUSTER indica a PostgreSQL di organizzare l'archiviazione delle tabelle in base a un indice specificato. Ha uno scopo simile alla tabella organizzata dell'indice di Oracle. Tuttavia, il clustering è un'operazione una tantum e PostgreSQL non mantiene la struttura della tabella negli aggiornamenti successivi. È necessario un clustering manuale e periodico. |
Indice locale e globale | Utilizzato per l'indicizzazione delle tabelle partizionate in un database Oracle. Ogni indice è
definito come LOCAL o GLOBAL . |
No | Gli indici delle partizioni PostgreSQL hanno la stessa funzionalità degli indici locali Oracle (ossia, l'indice è definito a livello di partizione, il livello globale non è supportato). |
Indici parziali per tabelle partizionate (Oracle 12c) | Crea un indice su un sottoinsieme delle partizioni di una tabella. Supporta
LOCAL e GLOBAL . |
Sì | Il partizionamento in PostgreSQL collega le tabelle figlio a una tabella padre. È possibile creare indici solo su un sottoinsieme di tabelle figlio. |
CREATE/DROP INDEX |
Comando utilizzato per la creazione e l'eliminazione degli indici. | Sì | PostgreSQL supporta il comando CREATE INDEX . Supporta inoltre
ALTER TABLE tableName ADD INDEX indexName
columnName |
ALTER INDEX ... REBUILD |
Ricrea l'indice, il che può causare un blocco esclusivo sulla tabella indicizzata. | Richiede una sintassi diversa | PostgreSQL supporta le ricostruzioni degli indici utilizzando l'istruzione REINDEX . La tabella è bloccata per le scritture durante questa operazione e sono consentite solo le letture. |
ALTER INDEX ... REBUILD ONLINE |
Ricrea un indice senza creare un blocco esclusivo nella tabella. | Richiede una sintassi diversa | PostgreSQL supporta le ricostruzioni dell'indice in parallelo utilizzando l'istruzione REINDEX TABLE
CONCURRENTLY . In questa modalità, PostgreSQL tenta di ricreare gli indici utilizzando blocchi minimi, ma senza rinunciare alla possibilità di impiegare più tempo e risorse per completare la rigenerazione. |
Compressione dell'indice | Una funzionalità che riduce le dimensioni fisiche dell'indice. | No | N/A |
Assegna l'indice a uno spazio tabella |
Crea uno spazio tabella di indice che può essere archiviato su un disco separato dai dati della tabella per ridurre i colli di bottiglia I/O del disco. | No | Sebbene PostgreSQL consenta la creazione di un indice in uno spazio delle tabelle definito dall'utente, non puoi creare spazi di tabella in Cloud SQL per PostgreSQL e l'indice deve essere creato in uno spazio delle tabelle predefinito. |
Indicizza i metadati (tabelle/visualizzazioni) | Oracle | DBA_INDEXES |
|
PostgreSQL | pg_catalog.pg_index |
Considerazioni sulle conversioni dell'indice
Nella maggior parte dei casi, gli indici Oracle possono essere semplicemente convertiti negli indici B-tree di PostgreSQL, poiché questo tipo di indice è il tipo di indice più utilizzato.
Come in un database Oracle, viene creato automaticamente un indice nei campi PRIMARY KEY
di una tabella. Analogamente, un indice UNIQUE
viene creato automaticamente sui campi che hanno un vincolo UNIQUE
. Inoltre, gli indici secondari vengono creati utilizzando l'istruzione CREATE INDEX
standard.
L'esempio seguente illustra come convertire una tabella Oracle con più campi indicizzati in PostgreSQL:
SQL> CREATE TABLE ORA_IDX_TO_PG (
col1 INT PRIMARY KEY,
col2 VARCHAR2(60),
col3 DATE,
col4 CLOB,
col5 VARCHAR2(20)
);
-- Single-field index
SQL> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);
-- Composite index
SQL> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);
-- Unique index
SQL> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(col3);
-- Function-based index
SQL> CREATE INDEX idx_func_col3 ON
ora_idx_to_pg(EXTRACT(MONTH FROM col3));
-- CLOB index
SQL> CREATE INDEX idx_col4 ON
ora_idx_to_pg(col4) INDEXTYPE IS CTXSYS.CONTEXT;
-- Invisible index
SQL> CREATE INDEX idx_col5_inv ON
ora_idx_to_pg(col5) INVISIBLE;
-- Drop index
SQL> DROP INDEX idx_col5_inv;
postgres=> CREATE TABLE ORA_IDX_TO_PG (
postgres(> col1 INT PRIMARY KEY,
postgres(> col2 VARCHAR(60),
postgres(> col3 DATE,
postgres(> col4 TEXT,
postgres(> col5 VARCHAR(20)
postgres(> );
-- Single index (supported)
postgres=> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);
-- Composite index (supported)
postgres=> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);
-- Unique index (supported)
postgres=> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(COL3);
-- Function-based index (supported)
postgres=> CREATE INDEX idx_func_col3 ON
postgres-> ora_idx_to_pg(EXTRACT(MONTH FROM col3));
-- CLOB (Supported, but requires different syntax. See Full Text Search for details)
postgres=> CREATE INDEX idx_col4 ON ora_idx_to_pg
postgres-> USING GIN (to_tsvector('english', col4));
-- Invisible index (not supported)
-- Optional - create the index as a B-tree index
postgres=> CREATE INDEX idx_col5 ON ora_idx_to_pg(col5);
-- Drop index
postgres=> DROP INDEX idx_col2;
SQL> SELECT ui.table_name,
ui.index_name,
ui.index_type,
ic.column_name
FROM user_indexes ui JOIN user_ind_columns ic
ON ui.index_name = ic.index_name
WHERE ui.table_name = 'ORA_IDX_TO_PG'
ORDER BY 4;
postgres=> select distinct
postgres-> t.relname as table_name,
postgres-> i.relname as index_name,
postgres-> pg_get_indexdef(ix.indexrelid) index_definition
postgres-> from
postgres-> pg_class t,
postgres-> pg_class i,
postgres-> pg_index ix
postgres-> where
postgres-> t.oid = ix.indrelid
postgres-> and i.oid = ix.indexrelid
postgres-> and t.relname = 'ora_idx_to_pg'
postgres-> order by
postgres-> t.relname,
postgres-> i.relname;
-- OR Use psql \d command:
postgres=> \d ora_idx_to_pg
Table "public.ora_idx_to_pg"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
col1 | integer | | not null |
col2 | character varying(60) | | |
col3 | date | | |
col4 | text | | |
col5 | character varying(20) | | |
Indexes:
"ora_idx_to_pg_pkey" PRIMARY KEY, btree (col1)
"idx_col2" btree (col2)
"idx_col4" gin (to_tsvector('english'::regconfig, col4))
"idx_col5" btree (col5)
"idx_cols3_2" btree (col3 DESC, col2)
"idx_func_col3" btree (date_part('month'::text, col3))
postgres=>
Partizionamento della tabella
Sia Oracle che PostgreSQL offrono funzionalità di partizionamento per la suddivisione di tabelle di grandi dimensioni. A questo scopo, segmenta fisicamente una tabella in parti più piccole, dove ogni parte contiene un sottoinsieme orizzontale di righe. La tabella partizionata è detta tabella padre e le sue righe sono archiviate fisicamente nelle sue partizioni. Sebbene non tutti i tipi di partizione di Oracle siano supportati in PostgreSQL, PostgreSQL supporta quelli più comuni.
Le seguenti sezioni descrivono i tipi di partizione supportati da PostgreSQL, illustrando ciascuno con un esempio di come creare le partizioni corrispondenti a quel tipo.
Partizionamento RANGE
Questo tipo di partizione assegna righe alle partizioni in base ai valori delle colonne che rientrano in un determinato intervallo. Ogni partizione contiene righe per le quali il valore dell'espressione di partizionamento rientra in un intervallo dato. È importante notare che gli intervalli non si sovrappongono tra le partizioni.
Esempio
CREATE TABLE employees (
empid INT,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE,
separated DATE,
job_code INT,
store_id INT)
PARTITION BY RANGE (store_id);
CREATE TABLE employees_p0 PARTITION OF employees
FOR VALUES FROM (MINVALUE) TO (6);
CREATE TABLE employees_p1 PARTITION OF employees
FOR VALUES FROM (6) TO (11);
CREATE TABLE employees_p2 PARTITION OF employees
FOR VALUES FROM (11) TO (16);
CREATE TABLE employees_p3 PARTITION OF employees
FOR VALUES FROM (16) TO (21);
Partizionamento LIST
Come per il partizionamento RANGE
, il partizionamento LIST
assegna righe alle partizioni in base ai valori delle colonne che rientrano in un insieme predefinito di valori. I valori delle chiavi visualizzati in ogni partizione sono elencati in modo esplicito per le partizioni LIST
.
Esempio
CREATE TABLE employees (
empid INT,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE,
separated DATE,
job_code INT,
store_id INT)
PARTITION BY LIST (store_id);
CREATE TABLE employees_pNorth PARTITION OF employees
FOR VALUES IN (3,5,6);
CREATE TABLE employees_pEast PARTITION OF employees
FOR VALUES IN (1,2,10);
CREATE TABLE employees_pWest PARTITION OF employees
FOR VALUES IN (4,12,13);
CREATE TABLE employees_pCnrl PARTITION OF employees
FOR VALUES IN (7,8,15);
Partizionamento HASH
Il partizionamento HASH
è più adatto quando l'obiettivo è ottenere una distribuzione uniforme dei dati tra tutte le partizioni. Un valore della colonna (o un'espressione basata sul valore di una colonna da sottoporre ad hashing) e il valore della riga vengono assegnati alla partizione corrispondente a quel valore hash. I valori hash devono essere assegnati in modo univoco alle partizioni e tutti i valori inseriti devono essere mappati esattamente a una partizione.
Esempio
CREATE TABLE employees (
empid INT,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE,
separated DATE,
job_code INT,
store_id INT)
PARTITION BY HASH (date_part('year', hired));
CREATE TABLE employees_p0 PARTITION OF employees
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE employees_p1 PARTITION OF employees
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE employees_p2 PARTITION OF employees
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE employees_p3 PARTITION OF employees
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
Partizionamento multilivello
Il partizionamento multilivello è un metodo per creare una gerarchia di partizioni per una singola tabella. Ogni partizione è ulteriormente suddivisa in una serie di partizioni diverse. Il numero di partizioni secondarie può variare da una partizione all'altra.
Esempio
CREATE TABLE sales (
Saleid INT,
sale_date DATE,
cust_code VARCHAR(15),
income DECIMAL(8,2))
PARTITION BY RANGE(date_part('year', sale_date));
CREATE TABLE sales_2019 PARTITION OF sales
FOR VALUES FROM (2019) TO (2020)
PARTITION BY RANGE(date_part('month', sale_date));
CREATE TABLE sales_2019_q1 PARTITION OF sales_2019
FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2019_q2 PARTITION OF sales_2019
FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2019_q3 PARTITION OF sales_2019
FOR VALUES FROM (7) TO (10);
CREATE TABLE sales_2019_q4 PARTITION OF sales_2019
FOR VALUES FROM (10) TO (13);
CREATE TABLE sales_2020 PARTITION OF sales
FOR VALUES FROM (2020) TO (2021)
PARTITION BY RANGE(date_part('month', sale_date));
CREATE TABLE sales_2020_q1 PARTITION OF sales_2020
FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2020_q2 PARTITION OF sales_2020
FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2020_h2 PARTITION OF sales_2020
FOR VALUES FROM (7) TO (13);
Collegamento o scollegamento di partizioni
In PostgreSQL, le partizioni possono essere aggiunte o rimosse dalla tabella padre. Una partizione scollegata può essere ricollegata in un secondo momento alla stessa tabella. Inoltre, quando si ricollega la partizione, è possibile specificare nuove condizioni di partizionamento, in modo da poter regolare i limiti delle partizioni.
Esempio
CREATE TABLE employees (
empid INT,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE,
separated DATE,
job_code INT,
store_id INT)
PARTITION BY RANGE (date_part('year', hired));
CREATE TABLE employees_p0 PARTITION OF employees
FOR VALUES FROM (2010) TO (2015);
CREATE TABLE employees_p1 PARTITION OF employees
FOR VALUES FROM (2015) TO (2020);
-- changing partition boundaries
BEGIN TRANSACTION;
ALTER TABLE employees DETACH PARTITION employees_p1;
ALTER TABLE employees ATTACH PARTITION employees_p1 FOR VALUES FROM (2015) TO (2022);
COMMIT TRANSACTION;
La tabella seguente descrive dove i tipi di partizione Oracle e Cloud SQL per PostgreSQL sono equivalenti e dove è consigliata una conversione:
Tipo di partizione Oracle | Supportato da PostgreSQL | Implementazione di PostgreSQL |
---|---|---|
RANGE partizioni |
Sì | PARTITION BY RANGE |
LIST partizioni |
Sì | PARTITION BY LIST |
HASH partizioni |
Sì | PARTITION BY HASH |
SUB-PARTITIONING |
Sì | Partizionamento multilivello |
Partizioni a intervalli | No | Non supportata |
Consulente per la partizione | No | Non supportata |
Partizionamento delle preferenze | No | Non supportata |
Partizionamento virtuale basato su colonne | No | Una soluzione alternativa consiste nel partizionare direttamente con l'espressione della colonna virtuale:
|
Partizionamento automatico degli elenchi | No | Non supportata |
Dividi partizioni |
No | Per una soluzione alternativa, valuta la possibilità di scollegare o collegare le partizioni delle tabelle per regolare i limiti delle partizioni |
Partizioni di Exchange | Sì | DETACH / ATTACH PARTITION |
Partizionamento di più tipi (partizionamento composito) | Sì | Partizionamento multilivello |
Metadati delle partizioni | Oracle | DBA_TAB_PARTITIONS |
PostgreSQL | pg_catalog.pg_class |
L'esempio seguente è un confronto affiancato della creazione di partizioni di tabella su entrambe le piattaforme. Tieni presente che PostgreSQL non supporta il riferimento a uno spazio delle tabelle nella clausola PARTITIONS
del comando CREATE TABLE
.
Implementazione di Oracle
CREATE TABLE employees (
empid NUMBER,
fname VARCHAR2(30),
lname VARCHAR2(30),
hired DATE,
separated DATE,
job_code NUMBER,
store_id NUMBER)
PARTITION BY LIST (store_id) (
PARTITION employees_pNorth VALUES (3,5,6) TABLESPACE users,
PARTITION employees_pEast VALUES (1,2,10) TABLESPACE users,
PARTITION employees_pWest VALUES (4,12,13) TABLESPACE users,
PARTITION employees_pCnrl VALUES (7,8,15) TABLESPACE users
);
Implementazione di PostgreSQL
CREATE TABLE employees (
empid INT,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE,
separated DATE,
job_code INT,
store_id INT)
PARTITION BY LIST (store_id);
CREATE TABLE employees_pNorth PARTITION OF employees
FOR VALUES IN (3,5,6);
CREATE TABLE employees_pEast PARTITION OF employees
FOR VALUES IN (1,2,10);
CREATE TABLE employees_pWest PARTITION OF employees
FOR VALUES IN (4,12,13);
CREATE TABLE employees_pCnrl PARTITION OF employees
FOR VALUES IN (7,8,15);
Tabelle temporanee
In un database Oracle, le tabelle temporanee sono chiamate GLOBAL TEMPORARY TABLES
, mentre in PostgreSQL sono note semplicemente come tabelle temporanee. Le funzionalità di base di una tabella temporanea sono identiche su entrambe le piattaforme. Esistono, tuttavia, alcune differenze degne di nota:
- Oracle archivia la struttura delle tabelle temporanee per un utilizzo ripetuto anche dopo il riavvio di un database, mentre PostgreSQL archivia la tabella temporanea solo per la durata di una sessione.
- Una tabella temporanea in un database Oracle è accessibile da diversi utenti con le autorizzazioni appropriate. Al contrario, è possibile accedere a una tabella temporanea in PostgreSQL solo durante la sessione in cui è stata creata, a meno che alla tabella temporanea non vengano indicati nomi qualificati per lo schema.
- In un database Oracle, esiste una distinzione tra le tabelle temporanee
GLOBAL
eLOCAL
che specificano se il contenuto della tabella è globale o specifico per la sessione. In PostgreSQL, le parole chiaveGLOBAL
eLOCAL
sono supportate per motivi di compatibilità, ma non influiscono sulla visibilità dei dati. - Se la clausola
ON COMMIT
viene omessa durante la creazione di una tabella temporanea, il comportamento predefinito nel database Oracle èON COMMIT DELETE ROWS
, il che significa che Oracle tronca la tabella temporanea dopo ogni commit. Al contrario, in PostgreSQL il comportamento predefinito è preservare le righe nella tabella temporanea dopo ogni commit.
La tabella seguente evidenzia le differenze nelle tabelle temporanee tra Oracle e Cloud SQL per PostgreSQL.
Funzionalità tabella temporanea | Implementazione di Oracle | Implementazione di PostgreSQL |
---|---|---|
Sintassi | CREATE GLOBAL TEMPORARY TABLE |
CREATE TEMPORARY TABLE |
Accessibilità | Accessibile da più sessioni | Accessibile solo dalla sessione del creatore, a meno che non vi siano riferimenti con nomi qualificati per lo schema |
Supporto dell'indice | Sì | Sì |
Assistenza per le chiavi esterne | Sì | Sì |
Mantieni DDL | Sì | No |
ON COMMIT azione predefinita |
I record vengono eliminati | I record vengono conservati |
ON COMMIT PRESERVE ROWS |
Sì | Sì |
ON COMMIT DELETE ROWS |
Sì | Sì |
ON COMMIT DROP |
No | Sì |
ALTER TABLE assistenza |
Sì | Sì |
Raccolta di statistiche | DBMS_STATS.GATHER_TABLE_STATS |
ANALYZE |
Oracle 12c GLOBAL_TEMP_ TABLE_STATS |
DBMS_STATS.SET_TABLE_PREFS |
ANALYZE |
Colonne inutilizzate
La funzionalità di Oracle che permette di contrassegnare colonne specifiche come UNUSED
viene spesso utilizzata per rimuovere colonne dalle tabelle senza rimuovere fisicamente i dati della colonna. Questo per evitare i potenziali carichi elevati che si verificano durante l'eliminazione di colonne da tabelle di grandi dimensioni.
In PostgreSQL, l'eliminazione di una colonna di grandi dimensioni non rimuove i dati della colonna dall'archiviazione fisica ed è quindi un'operazione rapida anche su tabelle di grandi dimensioni. Non è necessario contrassegnare una colonna come UNUSED
come in un database Oracle. Lo spazio occupato dalla colonna eliminata viene recuperato da nuove istruzioni DML o durante un'operazione VACUUM
successiva.
Tabelle di sola lettura
Le tabelle di sola lettura sono una funzionalità Oracle che contrassegna le tabelle come di sola lettura utilizzando il comando ALTER TABLE
. In Oracle 12c R2, questa funzionalità è disponibile anche per le tabelle con partizioni e sottopartizioni. PostgreSQL non offre una funzionalità equivalente, ma esistono due possibili soluzioni alternative:
- Concedi a
SELECT
l'autorizzazione sulle tabelle per utenti specifici. Tieni presente che questo non preclude al proprietario della tabella di eseguire operazioni DML sulle proprie tabelle. - Crea una replica di lettura Cloud SQL per PostgreSQL e indirizza gli utenti alle tabelle di replica che sono tabelle di sola lettura. Questa soluzione richiede l'aggiunta di un'istanza di replica di lettura a un'istanza Cloud SQL per PostgreSQL esistente.
Crea un trigger di database che generi eccezioni per le istruzioni DML, ad esempio:
-- Define trigger function CREATE OR REPLACE FUNCTION raise_readonly_exception() RETURNS TRIGGER AS $$ BEGIN RAISE EXCEPTION 'Table is readonly!'; RETURN NULL; END; $$ LANGUAGE 'plpgsql'; -- Fire trigger when DML statements is executed on read only table CREATE TRIGGER myTable_readonly_trigger BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON myTable FOR EACH STATEMENT EXECUTE PROCEDURE raise_readonly_exception(); -- Testing the trigger postgres=> INSERT INTO myTable (id) VALUES (1); ERROR: Table is readonly! CONTEXT: PL/pgSQL function raise_readonly_exception() line 3 at RAISE postgres=>
Set di caratteri
Sia Oracle che PostgreSQL supportano un'ampia gamma di set di caratteri, collage e Unicode, compreso il supporto per linguaggi a byte singolo e multibyte. Inoltre, i database PostgreSQL che risiedono nella stessa istanza possono essere configurati con set di caratteri distinti. Consulta l'elenco dei set di caratteri supportati in PostgreSQL.
In Oracle Database, i set di caratteri sono specificati a livello di database (Oracle 12g R1 o versioni precedenti) o a livello di database modulare (Oracle 12g R2 o versioni successive). In PostgreSQL viene specificato un set di caratteri predefinito quando crea una nuova istanza di Cloud SQL per PostgreSQL. Ogni database creato all'interno dell'istanza può essere creato con un set di caratteri diverso. Puoi specificare l'ordine di ordinamento e la classificazione dei caratteri per ogni colonna della tabella.
Esempio
-- Create a database using UTF-8 character set and ja_JP.UTF collation
postgres=> CREATE DATABASE jpdb WITH ENCODING 'UTF8' LC_COLLATE='ja_JP.UTF8' LC_CTYPE='ja_JP.UTF8' TEMPLATE=template0;
-- Query the character set and collation settings of all databases
postgres=> SELECT datname AS DATABASE_NAME, datcollate AS LC_COLLATE, datctype AS LC_CTYPE from pg_database;
database_name | lc_collate | lc_ctype
---------------+------------+------------
cloudsqladmin | en_US.UTF8 | en_US.UTF8
template0 | en_US.UTF8 | en_US.UTF8
template1 | en_US.UTF8 | en_US.UTF8
postgres | en_US.UTF8 | en_US.UTF8
jpdb | ja_JP.UTF8 | ja_JP.UTF8
(5 rows)
-- Alternatively, use psql \l command to query the database settings
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------+-------------------+----------+------------+------------+-----------------------------------------
cloudsqladmin | cloudsqladmin | UTF8 | en_US.UTF8 | en_US.UTF8 |
postgres | cloudsqlsuperuser | UTF8 | en_US.UTF8 | en_US.UTF8 | =Tc/cloudsqlsuperuser +
| | | | | cloudsqlsuperuser=CTc/cloudsqlsuperuser+
| | | | | testuser=CTc/cloudsqlsuperuser
template0 | cloudsqladmin | UTF8 | en_US.UTF8 | en_US.UTF8 | =c/cloudsqladmin +
| | | | | cloudsqladmin=CTc/cloudsqladmin
template1 | cloudsqlsuperuser | UTF8 | en_US.UTF8 | en_US.UTF8 | =c/cloudsqlsuperuser +
| | | | | cloudsqlsuperuser=CTc/cloudsqlsuperuser
-- Specifying column level collation
postgres=> CREATE TABLE test1 (
postgres(> a text COLLATE "de_DE",
postgres(> b text COLLATE "es_ES"
postgres(> );
Viste
PostgreSQL supporta le viste semplici e complesse. Per le opzioni di creazione delle visualizzazioni, ci sono alcune differenze tra Oracle e PostgreSQL. La seguente tabella evidenzia queste differenze.
Funzionalità di visualizzazione Oracle | Descrizione | Supporto di Cloud SQL per PostgreSQL | Considerazioni sulle conversioni |
---|---|---|---|
FORCE |
Crea una vista senza verificare se le tabelle/visualizzazioni di origine esistono. | No | Nessuna opzione equivalente disponibile. |
CREATE OR REPLACE |
Crea una vista che non esiste o sovrascrivine una esistente. | Sì | PostgreSQL supporta il comando CREATE OR REPLACE per le viste. |
WITH CHECK OPTION |
Specifica il livello di applicazione quando esegui operazioni DML sulla vista. | Sì | Il valore predefinito è CASCADED , che determina la valutazione delle viste di riferimento.La parola chiave LOCAL fa sì che venga valutata solo la visualizzazione corrente. |
WITH READ-ONLY |
Consente solo operazioni di lettura sulla vista. Le operazioni DML sono vietate. | No | Una soluzione alternativa consiste nel concedere a tutti gli utenti privilegi SELECT per la vista. |
VISIBLE | INVISIBLE (Oracle 12c) |
Specifica se una colonna basata sulla visualizzazione è visibile o invisibile all'utente. | No | Crea il campo VIEW solo con le colonne obbligatorie. |
L'esempio di conversione seguente illustra la conversione da Oracle a Cloud SQL PostgreSQL per le viste.
-- Create view to retrieve employees from department 100 using the WITH CHECK -- OPTION option
SQL> CREATE OR REPLACE FORCE VIEW vw_emp_dept100
AS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100
WITH CHECK OPTION;
-- Perform an UPDATE operation on the VIEW
SQL> UPDATE vw_emp_dept100
SET salary=salary+1000;
postgres=> CREATE OR REPLACE VIEW vw_emp_dept100
postgres-> AS
postgres-> SELECT EMPLOYEE_ID,
postgres-> FIRST_NAME,
postgres-> LAST_NAME,
postgres-> SALARY,
postgres-> DEPARTMENT_ID
postgres-> FROM EMPLOYEES
postgres-> WHERE DEPARTMENT_ID=100
postgres-> WITH CHECK OPTION;
-- Perform an UPDATE operation on the VIEW
postgres=> UPDATE vw_emp_dept100
postgres-> SET salary=salary+1000;
-- Update one employee department id to 60
postgres=> UPDATE vw_emp_dept100
postgres-> SET DEPARTMENT_ID=60
postgres-> WHERE EMPLOYEE_ID=110;
ERROR: new row violates check option for view "vw_emp_dept100"
DETAIL: Failing row contains (110, John, Chen, JCHEN, 515.124.4269, 1997-09-28, FI_ACCOUNT, 9200.00, null, 108, 60).
Gestione degli accessi in visualizzazione:
I proprietari di una vista devono disporre dei privilegi per le tabelle di base per crearla. L'utente di una vista deve disporre delle autorizzazioni SELECT
appropriate per la vista.
Devono inoltre disporre delle autorizzazioni INSERT
, UPDATE
e DELETE
appropriate per la vista quando eseguono operazioni DML attraverso la vista. In entrambi i casi, gli utenti non hanno
bisogno di autorizzazioni per le tabelle sottostanti.
Passaggi successivi
- Scopri di più sugli account utente PostgreSQL.
- Esplora le architetture di riferimento, i diagrammi e le best practice su Google Cloud. Dai un'occhiata al nostro Cloud Architecture Center.