Dieses Dokument ist Teil einer Reihe, die wichtige Informationen und Anleitungen zur Planung und Durchführung von Oracle® 11g/12c-Datenbankmigrationen zu Cloud SQL for PostgreSQL Version 12 enthält. In diesem Dokument werden die grundlegenden Unterschiede zwischen Oracle® Database und Cloud SQL for PostgreSQL in Bezug auf das Erstellen von Nutzern, Schemas, Tabellen, Indexen und Ansichten erläutert.
Zusätzlich zum Teil der anfänglichen Einrichtung umfasst die Reihe die folgenden Teile:
- Oracle-Nutzer zu Cloud SQL for PostgreSQL migrieren: Terminologie und Funktionalität
- Oracle-Nutzer zu Cloud SQL for PostgreSQL migrieren: Datentypen, Nutzer und Tabellen
- Oracle-Nutzer zu Cloud SQL for PostgreSQL migrieren: Abfragen, gespeicherte Verfahren, Funktionen und Trigger
- Oracle-Nutzer zu Cloud SQL for PostgreSQL migrieren: Sicherheit, Vorgänge, Monitoring und Logging
- Oracle Database-Nutzer und -Schemas zu Cloud SQL for PostgreSQL migrieren (dieses Dokument)
Terminologieunterschiede zwischen Oracle und Cloud SQL for PostgreSQL
Oracle und Cloud SQL for PostgreSQL haben unterschiedliche Architekturen und Terminologie für Instanzen, Datenbanken, Nutzer und Schemas. Eine Zusammenfassung dieser Unterschiede finden Sie im Teil zur Terminologie in dieser Reihe.
Oracle-Konfigurationen exportieren
Einer der ersten Schritte bei der Planung einer Migration zu Cloud SQL for PostgreSQL ist das Prüfen der vorhandenen Parametereinstellungen in der Oracle-Quelldatenbank. Die Einstellungen für Arbeitsspeicherzuweisung, Zeichensatz und Speicherparameter sind besonders nützlich, da sie die Erstkonfiguration und Größe der Cloud SQL for PostgreSQL-Zielumgebung bestimmen können. Es gibt mehrere Methoden zum Extrahieren von Oracle-Parametereinstellungen. Hier einige Beispiele:
- AWR-Berichte (Automatic Workload Repository) enthalten Daten zur Ressourcenzuweisung (CPU, RAM), Instanzparameterkonfiguration und maximalen Anzahl aktiver Sitzungen.
DBA_HIST
,V$OSSTAT
undV$LICENSE
für Details zur CPU-Nutzung.V$PARAMETER
-Ansicht für Datenbankkonfigurationsparameter.V$NLS_PARAMETERS
-Ansicht für Parameter der Datenbanksprache.DBA_DATA_FILES
-Ansicht zum Berechnen der Speichergröße der Datenbank.- Oracle-
SPFILE
für Datenbankinstanzkonfigurationen - Jobplaner-Tools (z. B.
crontab
) zur Identifizierung von Routine-Sicherungen oder Wartungsfenstern, die berücksichtigt werden sollten.
Nutzer in Cloud SQL for PostgreSQL importieren und konfigurieren
Auf übergeordneter Ebene sollte jedes Oracle-Schema als eigenes Schema in PostgreSQL erstellt werden. In einer Oracle-Datenbank ist Nutzer synonym mit Schema. Das bedeutet, dass beim Erstellen eines Nutzers ein Schema erstellt wird. Zwischen Nutzern und Schemas besteht immer eine 1:1-Beziehung. In PostgreSQL werden Nutzer und Schemas separat erstellt. Ein Nutzer kann ohne Erstellen eines entsprechenden Schemas erstellt werden. Damit Sie in PostgreSQL dieselbe Oracle-Nutzer- oder -Schemastruktur beibehalten können, können Sie für jeden Nutzer ein Schema erstellen.
In der folgenden Tabelle werden Konvertierungsbeispiele gezeigt:
Aktionstyp | Datenbanktyp | Befehlsvergleich |
---|---|---|
Nutzer und Schema erstellen | Oracle |
CREATE USER username IDENTIFIED BY password; |
PostgreSQL |
Nutzer und Schema sind in PostgreSQL unterschiedliche Konzepte. Daher sind zwei separate CREATE -Anweisungen erforderlichCREATE USER username WITH PASSWORD 'password'; |
|
Rollen zuweisen | Oracle |
GRANT CONNECT TO username; |
PostgreSQL |
GRANT pg_monitor TO username; |
|
Berechtigungen gewähren | Oracle |
GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username; |
PostgreSQL |
GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username; |
|
Berechtigungen widerrufen | Oracle |
REVOKE UPDATE ON HR.EMPLOYEES FROM username; |
PostgreSQL |
REVOKE UPDATE ON HR.EMPLOYEES FROM username; |
|
DBA/Superuser gewähren | Oracle |
GRANT DBA TO username; |
PostgreSQL |
GRANT cloudsqlsuperuser TO username; |
|
Nutzer entfernen | Oracle |
DROP USER username CASCADE; |
PostgreSQL |
Nutzer und Schema sind in PostgreSQL unterschiedliche Konzepte. Daher sind zwei separate DROP -Anweisungen erforderlichDROP USER username; |
|
Nutzermetadaten | Oracle |
DBA_USERS |
PostgreSQL |
pg_catalog.pg_user |
|
Berechtigungsmetadaten | Oracle |
DBA_SYS_PRIVS |
PostgreSQL |
pg_catalog.pg_roles |
|
CLI-Verbindungsstring | Oracle |
sqlplus username/password@host/tns_alias |
PostgreSQL |
Ohne Passwort-Eingabeaufforderung:PGPASSWORD=password psql -h hostname -U username -d database_name Mit Passwort-Eingabeaufforderung: psql -h hostname -U username -W -d database_name |
Oracle 12c-Datenbanken-Nutzer:
In Oracle 12c gibt es zwei Arten von Nutzern: allgemeine Nutzer und lokale Nutzer. Gewöhnliche Nutzer werden in der Stamm-CDB erstellt, einschließlich PDBs. Sie werden durch das Präfix C##
in ihrem Nutzernamen identifiziert. Lokale Nutzer werden nur in einer bestimmten PDB erstellt.
Verschiedene Datenbanknutzer mit identischen Nutzernamen können in mehreren PDBs erstellt werden. Ändern Sie bei der Migration von Oracle 12c zu PostgreSQL die Nutzer und Berechtigungen entsprechend der PostgreSQL-Architektur. Hier zwei gängige Beispiele zur Veranschaulichung dieser Unterschiede:
# 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)
Nutzer über die Google Cloud Console verwalten
Öffnen Sie zum Anzeigen der aktuell konfigurierten Nutzer für Cloud SQL for PostgreSQL die folgende Seite in der Google Cloud Console:
Google Cloud > Speicher > SQL > Instanz > Nutzer
Tabellen- und Ansichtsdefinitionen importieren
Oracle und PostgreSQL unterscheiden sich in Bezug auf die Groß- und Kleinschreibung. Bei Oracle-Namen wird die Groß- und Kleinschreibung nicht berücksichtigt. Bei PostgreSQL-Namen wird nicht zwischen Groß- und Kleinschreibung unterschieden, es sei denn, sie sind in doppelte Anführungszeichen gesetzt. Viele Tools für den Schemaexport und für SQL-Generierung für Oracle wie DBMS_METADATA.GET_DDL
fügen automatisch Anführungszeichen zu Objektnamen hinzu. Diese Anführungszeichen können nach der Migration zu vielen Problemen führen.
Wir empfehlen, alle Anführungszeichen um Objektnamen aus DDL-Anweisungen (Datendefinitionssprache) zu entfernen, bevor Sie die Objekte in PostgreSQL erstellen.
Tabellensyntax erstellen
Wenn Sie Tabellen von Oracle in MySQL-Datentypen konvertieren, müssen Sie im ersten Schritt die Oracle-Tabellenanweisungen aus der Quelldatenbank erstellen. Mit der folgenden Beispielabfrage wird die DDL für die Standorttabelle aus dem HR-Schema extrahiert:
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
Die vollständige Ausgabe enthält Speicherelemente, Indexe und Tablespace-Informationen, die ausgelassen wurden, da diese zusätzlichen Elemente von der PostgreSQL-Anweisung CREATE TABLE
nicht unterstützt werden.
Nachdem die DDL extrahiert wurde, entfernen Sie die Anführungszeichen um die Namen und führen Sie die Tabellenkonvertierung gemäß der Oracle-zu-PostgreSQL-Konvertierungstabelle für Datentypen aus. Prüfen Sie für jede Spalte, ob sie konvertiert werden kann. Wenn dies nicht unterstützt wird, wählen Sie gemäß der Konvertierungstabelle einen anderen Datentyp aus. Im Folgenden sehen Sie beispielsweise die konvertierte DDL-Anweisung für die Standorttabelle.
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)
)
Create Table As Select (CTAS)
Die Anweisung CREATE TABLE AS SELECT
(CTAS) wird verwendet, um eine neue Tabelle auf Basis einer vorhandenen Tabelle zu erstellen. Beachten Sie, dass nur Spaltennamen und Spaltendatentypen kopiert werden, Einschränkungen und Indexe jedoch nicht. PostgreSQL unterstützt den ANSI-SQL-Standard für CTAS-Funktionen und ist mit der Oracle-CTAS-Anweisung kompatibel.
Unsichtbare Spalten in Oracle 12c
PostgreSQL unterstützt keine unsichtbaren Spalten. Erstellen Sie als Behelfslösung eine Ansicht, die nur die sichtbaren Spalten enthält.
Tabelleneinschränkungen
Oracle bietet sechs Arten von Tabelleneinschränkungen, die beim Erstellen der Tabelle oder nach der Tabellenerstellung mit dem Befehl ALTER TABLE
definiert werden können. Die Oracle-Einschränkungstypen sind PRIMARY KEY
, FOREIGN KEY
, UNIQUE
, CHECK
, NOT
NULL
und REF
. Darüber hinaus können Nutzer mit Oracle den Status einer Einschränkung über die folgenden Optionen steuern:
INITIALLY IMMEDIATE
: Überprüft die Einschränkung am Ende jeder nachfolgenden SQL-Anweisung (Standardstatus).DEFERRABLE/NOT DEFERRABLE
: Aktiviert die Verwendung der KlauselSET CONSTRAINT
in nachfolgenden Transaktionen, bis eineCOMMIT
-Anweisung gesendet wird.INITIALLY DEFERRED
: Überprüft die Einschränkung am Ende von nachfolgenden Transaktionen.VALIDATE/NO VALIDATE
: Überprüft (oder überprüft explizit nicht) neue oder geänderte Zeilen auf Fehler. Diese Parameter hängen davon ab, ob die EinschränkungENABLED
oderDISABLED
lautet.ENABLED/DISABLED
: Gibt an, ob die Einschränkung nach der Erstellung erzwungen werden soll (standardmäßigENABLED
).
PostgreSQL unterstützt auch sechs Arten von Tabelleneinschränkungen: PRIMARY KEY
, FOREIGN KEY
, UNIQUE
, CHECK
, NOT NULL
und EXCLUDE
. Es gibt jedoch einige wichtige Unterschiede zwischen den Einschränkungstypen von Oracle und PostgreSQL, darunter:
- PostgreSQL unterstützt nicht die Einschränkung
REF
von Oracle. - PostgreSQL erstellt nicht automatisch einen Index für die referenzierenden Spalten für eine Fremdschlüsseleinschränkung. Wenn ein Index erforderlich ist, wird eine separate
CREATE INDEX
-Anweisung für die referenzierenden Spalten benötigt. - PostgreSQL unterstützt nicht die
ON DELETE SET NULL
-Klausel von Oracle. Diese Klausel weist Oracle an, alle abhängigen Werte in untergeordneten Tabellen aufNULL
zu setzen, wenn der Datensatz in der übergeordneten Tabelle gelöscht wird. - Einschränkungen für
VIEWS
werden mit Ausnahme vonCHECK OPTION
nicht unterstützt. - PostgreSQL unterstützt nicht das Deaktivieren von Einschränkungen. PostgreSQL unterstützt die Option
NOT VALID
, wenn eine neue Fremdschlüssel- oder Prüfungseinschränkung mit der AnweisungALTER TABLE
hinzugefügt wird. Mit dieser Option wird PostgreSQL angewiesen, die referenziellen Integritätsprüfungen für vorhandene Datensätze in der untergeordneten Tabelle zu überspringen.
In der folgenden Tabelle werden die wichtigsten Unterschiede zwischen den Einschränkungstypen von Oracle und PostgreSQL zusammengefasst:
Oracle-Einschränkungstyp | Unterstützung für Cloud SQL for PostgreSQL | Cloud SQL for PostgreSQL-Äquivalent |
---|---|---|
PRIMARY KEY |
Ja | PRIMARY KEY |
FOREIGN KEY |
Ja | Verwendet dieselbe ANSI-SQL-Syntax wie Oracle. Verwendet die ON DELETE -Klausel, um Fälle von Löschungen des übergeordneten FOREIGN
KEY -Datensatzes zu verarbeiten. PostgreSQL bietet drei Optionen für den Fall, dass Daten aus der übergeordneten Tabelle gelöscht werden und auf eine untergeordnete Tabelle mit der Einschränkung FOREIGN KEY verwiesen wird:
PostgreSQLs unterstützt nicht die ON DELETE SET NULL -Klausel von Oracle. Verwenden Sie die Klausel ON UPDATE , um Fälle von Aktualisierungen für übergeordnete FOREIGN
KEY -Datensätze zu verarbeiten.PostgreSQL bietet drei Optionen für den Umgang mit Aktualisierungsereignissen für FOREIGN KEY -Einschränkungen:
PostgreSQL erstellt nicht automatisch einen Index für die referenzierenden Spalten für eine Fremdschlüsseleinschränkung. |
UNIQUE |
Ja | Erstellt standardmäßig einen UNIQUE -Index. |
CHECK |
Ja | CHECK |
NOT NULL |
Ja | NOT NULL |
REF |
Nein | Nicht unterstützt. |
DEFERRABLE/NOT DEFERRABLE |
Ja | DEFERRABLE/NOT DEFERRABLE |
INITIALLY IMMEDIATE |
Ja | INITIALLY IMMEDIATE |
INITIALLY DEFERRED |
Ja | INITIALLY DEFERRED |
VALIDATE/NO VALIDATE |
Nein | Nicht unterstützt. |
ENABLE/DISABLE |
Nein | Standardmäßig aktiviert. Verwenden Sie die Option NOT VALID , wenn der Tabelle eine neue Fremdschlüssel- oder Prüfungseinschränkung mit einer ALTER
TABLE -Anweisung hinzugefügt wird, um referenzielle Integritätsprüfungen für vorhandene Datensätze zu überspringen. |
Einschränkung für ANSICHTEN | Nein | Nicht unterstützt außer der VIEW WITH CHECK OPTION . |
Metadaten von Einschränkungen | Oracle | DBA_CONSTRAINTS |
PostgreSQL | INFORMATION_SCHEMA.TABLE_CONSTRAINTS |
Virtuelle und generierte Spalten
Die virtuellen Spalten von Oracle basieren auf den Berechnungsergebnissen anderer Spalten. Sie werden als reguläre Spalten angezeigt, ihre Werte werden jedoch direkt aus der Oracle-Datenbank-Engine aus einer Berechnung abgeleitet und nicht in der Datenbank gespeichert. Virtuelle Spalten können mit Einschränkungen, Indexen, Tabellenpartitionierung und Fremdschlüsseln verwendet werden, lassen sich jedoch nicht durch DML-Vorgänge (Data Manipulation Language, Datenbearbeitungssprache) bearbeiten.
Die generierten Spalten von PostgreSQL sind hinsichtlich der Funktionalität mit den virtuellen Spalten von Oracle vergleichbar. Im Gegensatz zu Oracle werden generierte Spalten in PostgreSQL jedoch gespeichert und Sie müssen für jede generierte Spalte einen Datentyp angeben, d. h., sie belegen den Speicher, als wären sie normale Spalten.
Beispiel für eine virtuelle Spalte 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
Entsprechendes Beispiel 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)
Tabellenindexe
Oracle und PostgreSQL bieten eine Vielzahl von Indexierungsalgorithmen und Indextypen, die für verschiedene Anwendungen verwendet werden können. Im Folgenden finden Sie eine Liste der verfügbaren Indexierungsalgorithmen in PostgreSQL:
Indexalgorithmus | Beschreibung |
---|---|
B-Baum |
|
Hash |
|
GIN |
|
GiST |
|
SP-GiST |
|
BRIN |
|
In der folgenden Tabelle werden die Indextypen von Oracle und PostgreSQL verglichen:
Oracle-Index | Beschreibung | Unterstützt von PostgreSQL | PostgreSQL-Entsprechung |
---|---|---|---|
Bitmap-Index | Speichert eine Bitmap für jeden Indexschlüssel; am besten für die Bereitstellung von schnellem Datenabruf für OLAP-Arbeitslasten geeignet | Nein | – |
B-Baum-Index | Der häufigste Indextyp, der sich für eine Vielzahl von Arbeitslasten eignet und in der ASC|DESC -Sortierung konfiguriert werden kann. |
Ja | B-Baum-Index |
Zusammengesetzter Index | Erstellt über zwei oder mehr Spalten, um die Leistung des Datenabrufs zu verbessern. Die Spaltensortierung im Index bestimmt den Zugriffspfad. | Ja | Mehrspaltige Indexe Wenn Sie einen Index mit mehreren Spalten erstellen, können bis zu 32 Spalten angegeben werden. |
Funktionsbasierter Index | Speichert die Ausgabe einer Funktion, die auf die Werte einer Tabellenspalte angewendet wird. | Ja | Indexe für Ausdrücke |
Eindeutiger Index | Ein B-Baum-Index, der eine UNIQUE -Einschränkung für die indexierten Werte pro Spalte erzwingt. |
Ja | Eindeutiger Index |
Index der Anwendungsdomain | Geeignet zur Indexierung nicht relationaler Daten wie Audio- und Videodaten, LOB-Daten und anderer nicht textbasierter Typen. | Nein | – |
Unsichtbarer Index | Oracle-Feature, mit dem Sie Indexe verwalten und testen können, ohne die Entscheidung des Optimierungstools zu beeinträchtigen. | Nein | Als alternative Lösung können Sie für Testzwecke einen zusätzlichen Index für ein Lesereplikat erstellen, ohne fortlaufende Aktivitäten zu beeinträchtigen. |
Index-organisierte Tabelle | Ein Indextyp, mit dem gesteuert wird, wie Daten auf Tabellen- und Indexebene gespeichert werden. | Nein | PostgreSQL unterstützt keine per Index organisierten Tabellen. Die Anweisung CLUSTER weist PostgreSQL an, den Tabellenspeicher gemäß einem bestimmten Index zu organisieren. Das dient einem ähnlichen Zweck wie die indexbasierte Tabelle von Oracle. Das Clustering ist jedoch ein einmaliger Vorgang und PostgreSQL verwaltet die Struktur der Tabelle bei nachfolgenden Aktualisierungen nicht. Manuelles, regelmäßiges Clustering ist erforderlich. |
Lokaler und globaler Index | Wird zum Indexieren partitionierter Tabellen in einer Oracle-Datenbank verwendet. Jeder Index wird entweder als LOCAL oder GLOBAL definiert. |
Nein | Arbeitsindexe von PostgreSQL-Partitionen haben die gleiche Funktionalität wie lokale Oracle-Indexe (d. h., der Index wird auf Partitionsebene definiert; die globale Ebene wird nicht unterstützt). |
Teilindexe für partitionierte Tabellen (Oracle 12c) | Erstellt einen Index für einen Teil der Partitionen einer Tabelle. Unterstützt LOCAL und GLOBAL . |
Ja | Die Partitionierung in PostgreSQL funktioniert durch Anhängen untergeordneter Tabellen an eine übergeordnete Tabelle. Es ist möglich, Indexe nur für eine Teilmenge von untergeordneten Tabellen zu erstellen. |
CREATE/DROP INDEX |
Befehl zum Erstellen und Löschen von Indexen | Ja | PostgreSQL unterstützt den Befehl CREATE INDEX . Außerdem wird ALTER TABLE tableName ADD INDEX indexName
columnName unterstützt. |
ALTER INDEX ... REBUILD |
Erstellt den Index neu, was zu einer exklusive Sperre für die indexierte Tabelle führen kann. | Erfordert eine andere Syntax | PostgreSQL unterstützt die Neuerstellung von Indexen mithilfe der Anweisung REINDEX . Die Tabelle ist während dieses Vorgangs für Schreibvorgänge gesperrt und nur Lesevorgänge sind zulässig. |
ALTER INDEX ... REBUILD ONLINE |
Erstellt einen Index neu, ohne eine exklusive Sperre für die Tabelle zu erstellen. | Erfordert eine andere Syntax | PostgreSQL unterstützt gleichzeitige Indexneuerstellungen mit der REINDEX TABLE
CONCURRENTLY -Anweisung. In diesem Modus versucht PostgreSQL, Indexe mithilfe von minimalen Sperren neu zu erstellen. Dafür müssen Sie unter Umständen mehr Zeit und Ressourcen in Anspruch nehmen, um die Neuerstellung abzuschließen. |
Indexkomprimierung | Ein Feature zum Reduzieren der physischen Indexgröße. | Nein | – |
Index einem Tablespace zuweisen |
Erstellt einen Index-Tablespace, der auf einem anderen Laufwerk als die Tabellendaten gespeichert werden kann, um E/A-Engpässe auf dem Laufwerk zu verringern. | Nein | Obwohl PostgreSQL das Erstellen eines Index im benutzerdefinierten Tablespace ermöglicht, können Sie keine Tablespaces in Cloud SQL for PostgreSQL erstellen. Der Index muss im Standard-Tablespace erstellt werden. |
Indexe-Metadaten (Tabellen/Ansichten) | Oracle | DBA_INDEXES |
|
PostgreSQL | pg_catalog.pg_index |
Überlegungen zu Index-Umwandlung
In den meisten Fällen können Oracle-Indexe einfach in die B-Baum-Indexe von PostgreSQL konvertiert werden, da dieser Indextyp am häufigsten verwendet wird.
Wie bei einer Oracle-Datenbank wird auch für die PRIMARY KEY
-Felder einer Tabelle automatisch ein Index erstellt. In ähnlicher Weise wird ein UNIQUE
-Index automatisch für Felder mit einer UNIQUE
-Einschränkung erstellt. Außerdem werden sekundäre Indexe mit der Standardanweisung CREATE INDEX
erstellt.
Das folgende Beispiel zeigt, wie eine Oracle-Tabelle mit mehreren indexierten Feldern in PostgreSQL konvertiert werden kann:
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=>
Tabellenpartitionierung
Sowohl Oracle als auch PostgreSQL bieten Partitionierungsfunktionen zum Aufteilen großer Tabellen. Dies erfolgt durch physische Segmentierung einer Tabelle in kleinere Teile, wobei jeder Teil eine horizontale Teilmenge der Zeilen enthält. Die partitionierte Tabelle wird als übergeordnete Tabelle bezeichnet. Die zugehörigen Zeilen werden in ihren Partitionen physisch gespeichert. Obwohl nicht alle Partitionstypen von Oracle in PostgreSQL unterstützt werden, unterstützt PostgreSQL die gängigsten Typen.
In den folgenden Abschnitten werden die von PostgreSQL unterstützten Partitionstypen beschrieben. Sie werden jeweils mit einem Beispiel zum Erstellen von Partitionen dargestellt, die diesem Typ entsprechen.
RANGE-Partitionierung
Dieser Partitionstyp weist Partitionen basierend auf Spaltenwerten zu, die innerhalb eines bestimmten Bereichs liegen. Jede Partition enthält Zeilen, für die der Partitionierungsausdruckswert in einem bestimmten Bereich liegt. Beachten Sie, dass sich Bereiche nicht über Partitionen hinweg überschneiden.
Beispiel
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);
LIST-Partitionierung
Ähnlich wie bei der RANGE
-Partitionierung werden Partitionen durch die LIST
-Partitionierung auf Basis von Spaltenwerten zugewiesen, die in einen vordefinierten Satz von Werten fallen. Die in jeder Partition angezeigten Schlüsselwerte werden explizit für LIST
-Partitionen aufgelistet.
Beispiel
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);
HASH-Partitionierung
Die HASH
-Partitionierung ist am besten geeignet, wenn Sie eine gleichmäßige Datenverteilung zwischen allen Partitionen erreichen möchten. Ein Spaltenwert (oder Ausdruck, der auf einem zu hashenden Spaltenwert basiert) und der Zeilenwert werden der Partition zugewiesen, die diesem Hashwert entspricht. Hashwerte müssen Partitionen eindeutig zugewiesen sein und alle eingefügten Werte müssen genau einer Partition zugeordnet sein.
Beispiel
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);
Mehrstufige Partitionierung
Die mehrstufige Partitionierung ist eine Methode zum Erstellen einer Partitionshierarchie für eine einzelne Tabelle. Jede Partition ist weiter in eine Reihe verschiedener Partitionen unterteilt. Die Anzahl der Unterpartitionen kann von einer Partition zu einer anderen variieren.
Beispiel
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);
Partitionen anhängen oder trennen
In PostgreSQL können Partitionen zur übergeordneten Tabelle hinzugefügt oder daraus entfernt werden. Eine getrennte Partition kann später wieder an dieselbe Tabelle angehängt werden. Darüber hinaus können beim Anfügen der Partition neue Partitionierungsbedingungen angegeben werden, sodass die Partitionsgrenzen angepasst werden können.
Beispiel
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;
In der folgenden Tabelle wird beschrieben, wo die Partitionstypen von Oracle und Cloud SQL for PostgreSQL äquivalent sind und wo eine Umwandlung empfohlen wird:
Oracle-Partitionstyp | Unterstützt von PostgreSQL | PostgreSQL-Implementierung |
---|---|---|
RANGE -Partitionen |
Ja | PARTITION BY RANGE |
LIST -Partitionen |
Ja | PARTITION BY LIST |
HASH -Partitionen |
Ja | PARTITION BY HASH |
SUB-PARTITIONING |
Ja | Mehrstufige Partitionierung |
Intervallpartitionen | Nein | Nicht unterstützt |
Partitionsberater | Nein | Nicht unterstützt |
Präferenzpartitionierung | Nein | Nicht unterstützt |
Virtuelle, spaltenbasierte Partitionierung | Nein | Als Behelfslösung können Sie die Partitionierung mit dem Ausdruck für virtuelle Spalten direkt durchführen:
|
Automatische Liste-Partitionierung | Nein | Nicht unterstützt |
Partitionen teilen |
Nein | Als Behelfslösung können Sie Tabellenpartitionen trennen oder anhängen, um Partitionsgrenzen anzupassen |
Partitionen austauschen | Ja | DETACH / ATTACH PARTITION |
Partitionierung mit mehreren Typen (zusammengesetzte Partitionierung) | Ja | Mehrstufige Partitionierung |
Metadaten der Partitionen | Oracle | DBA_TAB_PARTITIONS |
PostgreSQL | pg_catalog.pg_class |
Das folgende Beispiel ist der direkte Vergleich der Erstellung von Tabellenpartitionen auf beiden Plattformen. PostgreSQL unterstützt nicht das Referenzieren eines Tablespace in der PARTITIONS
-Klausel des Befehls CREATE TABLE
.
Oracle-Implementierung
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
);
PostgreSQL-Implementierung
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);
Temporäre Tabellen
In einer Oracle-Datenbank werden temporäre Tabellen als GLOBAL TEMPORARY TABLES
bezeichnet. In PostgreSQL werden sie einfach als temporäre Tabellen bezeichnet. Die Grundfunktionalität einer temporären Tabelle ist auf beiden Plattformen gleich. Es gibt jedoch einige nennenswerte Unterschiede:
- Oracle speichert die temporäre Tabellenstruktur für die wiederholte Verwendung auch nach dem Neustart einer Datenbank. PostgreSQL speichert die temporäre Tabelle nur für die Dauer einer Sitzung.
- Auf eine temporäre Tabelle in einer Oracle-Datenbank kann von verschiedenen Nutzern mit den entsprechenden Berechtigungen zugegriffen werden. Auf eine temporäre Tabelle in PostgreSQL kann dagegen nur während der Sitzung zugegriffen werden, in der sie erstellt wurde, es sei denn, es wird auf die temporäre Tabelle mit schemaqualifizierten Namen verwiesen.
- In einer Oracle-Datenbank wird zwischen temporären
GLOBAL
- undLOCAL
-Tabellen unterschieden, die angeben, ob der Inhalt der Tabelle global oder sitzungsspezifisch ist. In PostgreSQL werden die SchlüsselwörterGLOBAL
undLOCAL
aus Kompatibilitätsgründen unterstützt. Sie haben aber keinen Einfluss auf die Sichtbarkeit der Daten. - Wenn die
ON COMMIT
-Klausel beim Erstellen einer temporären Tabelle weggelassen wird, ist das Standardverhalten in Oracle DatabaseON COMMIT DELETE ROWS
, d. h., Oracle schneidet die temporäre Tabelle nach jedem Commit ab. In PostgreSQL besteht das Standardverhalten hingegen darin, Zeilen nach jedem Commit in der temporären Tabelle beizubehalten.
In der folgenden Tabelle werden die Unterschiede in temporären Tabellen zwischen Oracle und Cloud SQL for PostgreSQL aufgezeigt.
Temporäre Tabelle-Feature | Umsetzung in Oracle | PostgreSQL-Implementierung |
---|---|---|
Syntax | CREATE GLOBAL TEMPORARY TABLE |
CREATE TEMPORARY TABLE |
Bedienungshilfen | Zugänglich von mehreren Sitzungen aus | Zugänglich nur über die Sitzung des Erstellers, es sei denn, es wird auf schemaqualifizierte Namen verwiesen |
Indexunterstützung | Ja | Ja |
Unterstützung von Fremdschlüsseln | Ja | Ja |
DDL beibehalten | Ja | Nein |
ON COMMIT -Standardaktion |
Datensätze werden gelöscht | Datensätze werden beibehalten |
ON COMMIT PRESERVE ROWS |
Ja | Ja |
ON COMMIT DELETE ROWS |
Ja | Ja |
ON COMMIT DROP |
Nein | Ja |
ALTER TABLE -Unterstützung |
Ja | Ja |
Statistiken erfassen | DBMS_STATS.GATHER_TABLE_STATS |
ANALYZE |
Oracle 12c GLOBAL_TEMP_ TABLE_STATS |
DBMS_STATS.SET_TABLE_PREFS |
ANALYZE |
Nicht verwendete Spalten:
Das Oracle-Feature zum Markieren bestimmter Spalten als UNUSED
wird häufig verwendet, um Spalten aus Tabellen zu entfernen, ohne die Spaltendaten physisch zu entfernen. Dadurch werden potenzielle hohe Lasten verhindert, die beim Löschen von Spalten aus großen Tabellen auftreten.
Beim Löschen einer großen Spalte in PostgreSQL werden die Spaltendaten nicht aus dem physischen Speicher entfernt. Daher ist es selbst bei großen Tabellen ein schneller Vorgang. Es ist nicht erforderlich, eine Spalte in einer Oracle-Datenbank als UNUSED
zu markieren. Der von der verworfenen Spalte belegte Speicherplatz wird entweder durch neue DML-Anweisungen oder während eines nachfolgenden VACUUM
-Vorgangs zurückgefordert.
Schreibgeschützte Tabellen
Schreibgeschützte Tabellen sind ein Oracle-Feature, das Tabellen mit dem Befehl ALTER TABLE
als schreibgeschützt markiert. In Oracle 12c R2 ist dieses Feature auch für Tabellen mit Partitionen und Unterpartitionen verfügbar. PostgreSQL bietet kein äquivalentes Feature, aber es gibt zwei mögliche Problemumgehungen:
- Gewähren Sie bestimmten Nutzern die Berechtigung
SELECT
für Tabellen. Beachten Sie, dass der Tabelleninhaber nicht daran gehindert wird, DML-Vorgänge auf seine Tabellen anzuwenden. - Erstellen Sie ein Lesereplikat für Cloud SQL for PostgreSQL und leiten Sie Nutzer zu den Replikattabellen, die schreibgeschützte Tabellen sind. Diese Lösung erfordert das Hinzufügen einer Lesereplikatinstanz zu einer vorhandenen Cloud SQL für PostgreSQL-Instanz.
Erstellen Sie einen Datenbanktrigger, der Ausnahmen für DML-Anweisungen auslöst. Beispiel:
-- 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=>
Zeichensätze
Sowohl Oracle als auch PostgreSQL unterstützen eine Vielzahl von Zeichensätzen, Sortierungen und Unicode, einschließlich Unterstützung für Single-Byte- und Multi-Byte-Sprachen. Darüber hinaus können PostgreSQL-Datenbanken, die sich auf derselben Instanz befinden, mit unterschiedlichen Zeichensätzen konfiguriert werden. Siehe dazu die Liste der unterstützten Zeichensätze in PostgreSQL.
In Oracle Database werden Zeichensätze auf Datenbankebene (Oracle 12g R1 oder früher) oder auf modularer Datenbankebene angegeben (Oracle 12g R2 oder höher). In PostgreSQL wird ein Standardzeichensatz angegeben, wenn eine neue Cloud SQL for PostgreSQL-Instanz erstellt wird. Jede innerhalb dieser Instanz erstellte Datenbank kann mit einem anderen Zeichensatz erstellt werden. Die Sortierfolge und die Klassifizierung von Zeichen kann pro Tabellenspalte angegeben werden.
Beispiel
-- 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(> );
Aufrufe
PostgreSQL unterstützt sowohl einfache als auch komplexe Ansichten. Bei den Optionen zum Erstellen von Ansichten gibt es einige Unterschiede zwischen Oracle und PostgreSQL. In der folgenden Tabelle werden diese Unterschiede erläutert.
Oracle-Feature für Ansichten | Beschreibung | Unterstützung für Cloud SQL for PostgreSQL | Überlegungen zu Konvertierungen |
---|---|---|---|
FORCE |
Erstellen einer Ansicht, ohne zu überprüfen, ob die Quelltabellen oder -Ansichten vorhanden sind. | Nein | Keine entsprechende Option verfügbar. |
CREATE OR REPLACE |
Nicht vorhandene Ansicht erstellen oder vorhandene Ansicht überschreiben. | Ja | PostgreSQL unterstützt den Befehl CREATE OR REPLACE für Ansichten. |
WITH CHECK OPTION |
Gibt den Grad der Durchsetzung bei der Durchführung von DML-Vorgängen gegen die Ansicht an. | Ja | Die Standardeinstellung ist CASCADED , was dazu führt, dass auch referenzierte Ansichten ausgewertet werden.Das Keyword LOCAL führt nur dazu, dass die aktuelle Ansicht ausgewertet wird. |
WITH READ-ONLY |
Gewährt nur Lesevorgänge für die Ansicht. DML-Vorgänge sind unzulässig. | Nein | Eine Problemumgehung besteht darin, allen Nutzern SELECT-Berechtigungen für die Ansicht zu gewähren. |
VISIBLE | INVISIBLE (Oracle 12c) |
Geben Sie an, ob eine Spalte basierend auf der Ansicht für den Nutzer sichtbar oder unsichtbar ist. | Nein | Erstellen Sie die VIEW nur mit den erforderlichen Spalten. |
Das folgende Konvertierungsbeispiel zeigt die Konvertierung von Oracle in Cloud SQL for PostgreSQL für Ansichten.
-- 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).
Zugriffsverwaltung für Ansichten:
Die Inhaber einer Ansicht müssen Berechtigungen für die Basistabellen haben, um die Ansicht erstellen zu können. Der Nutzer einer Ansicht benötigt die entsprechenden SELECT
-Berechtigungen für die Ansicht.
Außerdem benötigt er die entsprechenden Berechtigungen INSERT
, UPDATE
, DELETE
für die Ansicht, wenn DML-Vorgänge über die Ansicht ausgeführt werden. In beiden Fällen benötigen Nutzer keine Berechtigungen für die zugrunde liegenden Tabellen.
Nächste Schritte
- Weitere Informationen zu PostgreSQL-Nutzerkonten.
- Referenzarchitekturen, Diagramme und Best Practices zu Google Cloud kennenlernen. Weitere Informationen zu Cloud Architecture Center