Migrieren Sie funktionsbasierte Indizes von Oracle zu Postgre SQL - AWS Prescriptive Guidance
ÜbersichtVoraussetzungen und EinschränkungenArchitekturToolsEpenZugehörige RessourcenZusätzliche Informationen

Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

Migrieren Sie funktionsbasierte Indizes von Oracle zu Postgre SQL

Erstellt von Veeranjaneyulu Grandhi () und Navakanth Talluri () AWS AWS

Umwelt: Produktion

Quelle: Oracle

Ziel: Postgre SQL

R-Typ: Re-Architect

Arbeitslast: Oracle

Technologien: Migration; Datenbanken

Übersicht

Indizes sind eine gängige Methode zur Verbesserung der Datenbankleistung. Ein Index ermöglicht es dem Datenbankserver, bestimmte Zeilen viel schneller zu finden und abzurufen, als dies ohne einen Index möglich wäre. Indizes erhöhen jedoch auch den Mehraufwand für das Datenbanksystem als Ganzes, weshalb sie sinnvoll verwendet werden sollten. Funktionsbasierte Indizes, die auf einer Funktion oder einem Ausdruck basieren, können mehrere Spalten und mathematische Ausdrücke beinhalten. Ein funktionsbasierter Index verbessert die Leistung von Abfragen, die den Indexausdruck verwenden. 

Postgre unterstützt nativ SQL nicht die Erstellung funktionsbasierter Indizes mit Funktionen, deren Volatilität als stabil definiert ist. Sie können jedoch ähnliche Funktionen mit Volatilität erstellen IMMUTABLE und sie bei der Indexerstellung verwenden.

Eine IMMUTABLE Funktion kann die Datenbank nicht ändern, und es ist garantiert, dass sie bei denselben Argumenten für immer dieselben Ergebnisse zurückgibt. Diese Kategorie ermöglicht es dem Optimierer, die Funktion vorab auszuwerten, wenn eine Abfrage sie mit konstanten Argumenten aufruft. 

Dieses Muster hilft bei der Migration der funktionsbasierten Oracle-Indizes, wenn es mit Funktionen wieto_char, und to_number verwendet wirdto_date, auf das Postgre-Äquivalent. SQL

Voraussetzungen und Einschränkungen

Voraussetzungen

  • Ein aktives Amazon Web Services (AWS) -Konto

  • Eine Oracle-Quelldatenbank-Instance, bei der der Listener-Service eingerichtet ist und läuft

  • Vertrautheit mit Postgre-Datenbanken SQL

Einschränkungen

  • Die maximale Datenbankgröße beträgt 64 TB.

  • Funktionen, die bei der Indexerstellung verwendet werden, müssenIMMUTABLE.

Produktversionen

  • Alle Oracle-Datenbankeditionen für die Versionen 11g (Versionen 11.2.0.3.v1 und höher) und bis zu 12.2 und 18c

  • Postgre-Versionen 9.6 und höher SQL

Architektur

Quelltechnologie-Stack

  • Eine Oracle-Datenbank vor Ort oder auf einer Amazon Elastic Compute Cloud (AmazonEC2) -Instance oder eine Amazon RDS for Oracle DB-Instance

Zieltechnologie-Stack

  • Beliebige Postgre-Engine SQL

Tools

  • pgAdmin 4 ist ein Open-Source-Verwaltungstool für Postgres. Das pgAdmin 4-Tool bietet eine grafische Oberfläche zum Erstellen, Verwalten und Verwenden von Datenbankobjekten.

  • Oracle SQL Developer ist eine integrierte Entwicklungsumgebung (IDE) für die Entwicklung und Verwaltung von Oracle Database sowohl in herkömmlichen als auch in Cloud-Bereitstellungen.

Epen

AufgabeBeschreibungErforderliche Fähigkeiten
Erstellen Sie mit der Funktion to_char einen funktionsbasierten Index für eine Spalte.

Verwenden Sie den folgenden Code, um den funktionsbasierten Index zu erstellen.

postgres=# create table funcindex( col1 timestamp without time zone);
CREATE TABLE
postgres=# insert into funcindex values (now());
INSERT 0 1
postgres=# select * from funcindex;
            col1
----------------------------
 2022-08-09 16:00:57.77414
(1 rows)
 
postgres=# create index funcindex_idx on funcindex(to_char(col1,'DD-MM-YYYY HH24:MI:SS'));
ERROR:  functions in index expression must be marked IMMUTABLE

 

Hinweis: Postgre erlaubt es SQL nicht, einen funktionsbasierten Index ohne die Klausel zu erstellen. IMMUTABLE

DBA, App-Entwickler
Überprüfen Sie die Volatilität der Funktion.

Verwenden Sie den Code im Abschnitt Zusätzliche Informationen, um die Volatilität der Funktion zu überprüfen. 

DBA
AufgabeBeschreibungErforderliche Fähigkeiten
Erstellen Sie eine Wrapper-Funktion.

Verwenden Sie den Code im Abschnitt Zusätzliche Informationen, um eine Wrapper-Funktion zu erstellen.

SQLPostgre-Entwickler
Erstellen Sie einen Index mithilfe der Wrapper-Funktion.

Verwenden Sie den Code im Abschnitt Zusätzliche Informationen, um eine benutzerdefinierte Funktion mit dem Schlüsselwort IMMUTABLE im selben Schema wie die Anwendung zu erstellen, und verweisen Sie im Skript zur Indexerstellung darauf.

Wenn eine benutzerdefinierte Funktion in einem gemeinsamen Schema (aus dem vorherigen Beispiel) erstellt wird, aktualisieren Sie diese wie gezeigt. search_path

ALTER ROLE <ROLENAME> set search_path=$user, COMMON;
DBA, SQL Postgre-Entwickler
AufgabeBeschreibungErforderliche Fähigkeiten
Überprüfen Sie die Indexerstellung.

Stellen Sie sicher, dass der Index auf der Grundlage von Abfragezugriffsmustern erstellt werden muss.

DBA
Stellen Sie sicher, dass der Index verwendet werden kann.

Um zu überprüfen, ob der funktionsbasierte Index vom SQL Postgre-Optimizer übernommen wird, führen Sie eine SQL Anweisung mit explain oder explain analyze aus. Verwenden Sie den Code im Abschnitt Zusätzliche Informationen. Sammeln Sie nach Möglichkeit auch die Tabellenstatistiken.

Hinweis: Wenn Ihnen der Explain-Plan auffällt, hat der SQL Postgre-Optimizer aufgrund der Prädikatbedingung einen funktionsbasierten Index ausgewählt.

DBA

Zugehörige Ressourcen

Zusätzliche Informationen

Erstellen Sie eine Wrapper-Funktion

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;

Erstellen Sie einen Index mithilfe der Wrapper-Funktion

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

Überprüfen Sie die Volatilität der Funktion

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;

Stellen Sie sicher, dass der Index verwendet werden kann

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)