Heute geht es um ein etwas ausgefalleneres Feature der Oracle-Datenbank: Ihr könnt SQL-Abfragen
automatisch umschreiben lassen. Die technische Basis dafür sind Materialized Views - diese
sind in Oracle nicht nur einfache eine Tabelle mit den Inhalten der (eben materialisierten) View. Dazu
gehört auch das Query Rewrite - ein komplexes SQL wird vom Optimizer automatisch hin zu einem
einfachen Select auf die Materialized View umgeschrieben - sofern alle Voraussetzungen gegeben sind.
Zusätzlich gibt es jedoch "für den fortgeschrittenen" Nutzer das PL/SQL-Paket DBMS_ADVANCED_REWRITE
(Dokumentation)
im Schema SYS. Damit können zwei SQL-Abfragen als gleich deklariert werden. Standardmäßig hat kein
Datenbankuser Privilegien auf diesem Package - auch das Public Synonym fehlt. Also zuerst
die Privilegien einräumen - neben dem EXECUTE-Privileg auf das Package wird auch das
CREATE MATERIALIZED VIEW-Privileg benötigt ...
grant execute on sys.dbms_advanced_rewrite to scott / grant create materialized view to scott /
Nun könnt Ihr eine Query Rewrite Equivalence deklarieren. Das könnte zum Beispiel so
aussehen (bitte fragt nicht nach dem Sinn der Beispiele) ...
begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
name => 'MEIN_TEST',
source_stmt => 'select * from scott.emp',
destination_stmt => 'select empno, '||
' ename, '||
' to_char(null) as job, ' ||
' to_number(null) as mgr, ' ||
' to_date(null) as hiredate, ' ||
' to_number(null) as sal, '||
' to_number(null) as comm, '||
' to_number(null) as deptno '||
'from scott.emp',
validate => false,
rewrite_mode => 'TEXT_MATCH'
);
end;
/
Die Parameter SOURCE_STMT und DESTINATION_STMT legen fest, welche SQL-Abfrage
durch welche ersetzt werden soll. Der Parameter VALIDATE wird hier auf false gesetzt;
Oracle soll also nicht prüfen, ob diese beiden Abfragen wirklich das gleiche Ergebnis zurückliefern.
Würde man VALIDATE auf TRUE setzen, gäbe es eine Fehlermeldung, denn man sieht
sofort, dass diese Abfragen nicht identisch sind. Die Anzahl der Ergebnisspalten und deren
Datentypen müssen aber immer identisch sein. Der letzte Parameter legt fest, in welchen
Fällen dieses Rewrite genutzt werden soll - hier nur dann, wenn die SQL-Abfrage exakt so
wie deklariert ausgeführt wird (TEXT_MATCH). Die auf diese Art und Weise deklarierten
Equivalences
genießen beim Optimizer höchste Priorität - er geht davon aus, dass der Nutzer hier weiss, was
er tut! Probieren wir es aus.
SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- -------- ----- ----- ------ 7369 SMITH CLERK 7902 17.12.80 800 20 7499 ALLEN SALESMAN 7698 20.02.81 1600 300 30 7521 WARD SALESMAN 7698 22.02.81 1250 500 30 7566 JONES MANAGER 7839 02.04.81 2975 20 7654 MARTIN SALESMAN 7698 28.09.81 1250 1400 30 7698 BLAKE MANAGER 7839 01.05.81 2850 30 7782 CLARK MANAGER 7839 09.06.81 2450 10 : : : : : : : :
Scheint nicht zu funktionieren ... der Grund ist der Session-Parameter QUERY_REWRITE_INTEGRITY,
der defaultmäßig auf ENFORCED steht. Das bedeutet, die Datenbank führt generell nur solche
Rewrites aus, die von ihr selbst als gleichwertig angesehen werden - und das sind die hier deklarierten
nicht. Man muss der Datenbank nochmals sagen, dass sie auch solche Rewrites ausführen soll - auch
wenn dadurch andere oder veraltete Daten zurückgegeben werden.
SQL> alter session set query_rewrite_integrity = 'STALE_TOLERATED'; Session altered. SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- -------- ----- ----- ------ 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES : : : : : : : :
Man sieht, dass die Abfrage intern umgeschrieben wurde. An der Tabelle wurde
nix geändert - es ist auch keine View vorhanden. Man sieht schon, dass das ein
sehr mächtiges Feature ist, mit dem man auch Kollegen in den Wahnsinn treiben kann.
Daher sollte das natürlich vorsichtig eingesetzt werden. Den hier gezeigten Effekt kann
man (wenn man das braucht) besser mit Views oder gar der Virtual Private Database
realisieren.
Wenn es um Performance geht, hat das Feature aber absolut seine Berechtigung. Auch
ohne den mitunter recht komplexen Setup von Materialized Views kann eine teure SQL-Abfrage
auf einen einfachen Tabellen-SELECT umgeschrieben werden - selbst wenn die Tabelle
gar keine Materialized View ist.
drop table empjoindept
/
create table empjoindept as
select e.empno, e.ename, e.sal, d.dname, d.loc
from emp e join dept d on (e.deptno = d.deptno)
/
begin
sys.dbms_advanced_rewrite.drop_rewrite_equivalence(
name => 'MEIN_TEST'
);
end;
/
begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
name => 'MEIN_TEST',
source_stmt => 'select e.empno, e.ename, e.sal, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno',
destination_stmt => 'select * from empjoindept',
validate => true,
rewrite_mode => 'GENERAL'
);
end;
/
Führt man nun diesen SELECT (mit Join) aus, wird tatsächlich die Tabelle EMPJOINDEPT
selektiert ...
Ausführungsplan ---------------------------------------------------------- Plan hash value: 3055423993 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 700 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMPJOINDEPT | 14 | 700 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------
Es gibt allerdings noch ein paar Einschränkungen ...
- Bindevariablen werden nicht unterstützt. Man kann also kein generisches Rewrite deklarieren.
- Ausdrücke wie SYSDATE, ROWNUM, USER und andere können ebenfalls nicht genutzt werden
- Wenn die gleiche Session eine Änderung gemacht und das COMMIT noch nicht erfolgt ist, ziehen die Rewrites nicht.
- Ein Join sollte beim Deklarieren des Rewrite mit der "alten" Join-Syntax gemacht werden; beim tatsächlichen Abfragen funktioniert auch die neue (natürlich darf REWRITE_MODE dann nicht auf TEXT_MATCH stehen - GENERAL wäre eine Alternative).
- Nur SELECT-Abfragen können umgeschrieben werden - DML-Kommandos sind nicht unterstützt.
Man merkt schon, dass das Feature für den DWH-Bereich eingeführt wurde - die Anwendungsmöglichkeiten
sind begrenzt - aber bei dem einen oder anderen komplexen SELECT kann es vielleicht gute Dienste leisten.
