Query zur Laufzeit ändern


Zeitweise hat man das Problem das ein DBA vor einer Abfrage steht, welche ein Problem (Performance, Werte, etc.) verursacht, jedoch in die Applikation nicht eingegriffen werden kann. Auf einer Enterprise Edition gibt es hier einen kleinen Trick den man anwenden kann.

Vorbereitung

Dem Benutzer werden entsprechende Rechte gegeben.

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO scott;
GRANT CREATE MATERIALIZED VIEW TO scott;

Auf der Datenbank Seite sollte man prüfen, ob der Parameter query_rewrite_integrity auf „trusted“ oder  „stale_tolerated“ gesetzt ist bzw. der Parameter „query_rewrite_enabled“ auf TRUE steht. Bei einem Default Setup sollte man eigentlich nur folgenden Befehl absetzen müssen.

SQL> alter system set query_rewrite_integrity=trusted scope=both;

Der Rewrite

Gehen wir nun von folgenden SQL aus.

SQL> SELECT e.ename ,e.sal*0.5  FROM  emp  e,dept d  WHERE e.sal > 2800    AND e.deptno = d.deptno;
ENAME       E.SAL*0.5 
----------  ---------- 
JONES       1487.5 
BLAKE       1425 
SCOTT       1500 
KING        2500 
FORD        1500

Ziel soll es sein den SAL Faktor von 0.5 zu ändern, genau hier kommt das Package dbms_advanced_rewrite zum Einsatz.

SQL> begin
     sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
     name                =>     'new_sal',
     source_stmt         =>     'SELECT e.ename ,e.sal*0.5  FROM  emp  e,dept d  WHERE e.sal > 2800    AND e.deptno = d.deptno',
     destination_stmt    =>     'SELECT e.ename ,e.sal*2  FROM  emp  e,dept d  WHERE e.sal > 2800    AND e.deptno = d.deptno',
     validate            =>      false ,
     rewrite_mode        =>     'GENERAL');
     end;
  /
PL/SQL procedure successfully completed.

Führen wir nun die Abfrage erneut aus, so wird diese entsprechend geändert.

SQL> SELECT e.ename ,e.sal*0.5  FROM  emp  e,dept d  WHERE e.sal > 10    AND e.deptno = d.deptno;
ENAME       E.SAL*0.5 
---------- ---------- 
JONES            5950 
BLAKE            5700 
SCOTT            6000 
KING            10000 
FORD             6000

Wir können sehen, das trotz der Ausführung mit einem Faktor 0.5 die Anweisung mit dem Faktor 2 Ausgeführt worden ist.

Das Löschen einer hinterlegten rewrite Bedingung kann mit demselben Package erfolgen.

execute sys.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE (name => 'new_sal');

Auch können bestehende Definitionen aus dem Dictionary abgefragt werden.

SQL> select owner,name,rewrite_mode from DBA_REWRITE_EQUIVALENCES;
OWNER                          NAME                           REWRITE_MO 
------------------------------ ------------------------------ ---------- 
SCOTT                          NEW_SAL                        GENERAL

Ich finde das eine coole und einfache Sache, mehr Informationen bekommt man aus der Oracle Dokumentation. Grundlegend ist aber Vorsicht geboten das man seine Rewrite Anweisung gut überdenkt, da ja auch ebenfalls andere SQL Befehle davon betroffen sein können.

Wir immer freue ich mich über Kommentare.

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s