15. Dezember 2010

SQL Queries "magisch" umschreiben: DBMS_ADVANCED_REWRITE

The "magic" rewrite of SQL Queries DBMS_ADVANCED_REWRITE
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.
In this blog posting I'll write about a more exotic feature of the Oracle database. You can let the kernel rewrite SQL queries for you. This is based on the materialized view technology: When you have a materialized view for a specific (complex) SQL query and the optimizer "knows" about this, it will rewrite your query to use the materialized view instead of the original tables. In practice, it's not that easy - there are some requirements which the materialized views must meet. But that is another topic - I'll pick a particular feature here: the DBMS_ADVANCED_REWRITE (Documentation) package to create your own rewrites - even without materialized views.
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 ...
With this package you can instruct the optimizer to rewrite one SQL query to another. By default no database user can execute this package - and even the public synonym is missing. So in order to play with it you first need to grant the EXECUTE privilege to your database user (here: SCOTT). In addition to this, SCOTT needs the CREATE MATERIALIZED VIEW privilege.
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) ...
Now you can create your first Query Rewrite Equivalence. An example might look like this (don't ask about the sense for these examples) ...
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.
The parameters SOURCE_STMT and DESTINATION_STMT determine which SQL query is being rewritten to which other SQL statement. This is a declaration to the Oracle optimizer that those two SQL queries are equivalent and that the DESTINATION_STMT is more favorable. If the VALIDATE argument is set to TRUE the database will check whether the two SQL queries really return the same results. If set to false, the database will not perform that check. The last argument REWRITE_MODE determines when to use the query equivalence. The example here (TEXT_MATCH) requires the query text to be identical to the declaration. The equivalences declared with DBMS_ADVANCED_REWRITE have high priority - the optimizer will use them even if it considers the original query as more effective. Let's test the declaration.
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.
That seems not to work - we have forgotten to set the session parameter QUERY_REWRITE_INTEGRITY, which is set to ENFORCED by default. That means the database uses query rewrite only if the optimizer is sure that the same data is being returned. The parameter must be set to TRUSTED or STALE_TOLERATED in order to use the declared equivalences. STALE_TOLERATED means that query rewrite even may return stale or incorrect data.
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.
You see that the query has been rewritten internally. The table has not been changed; there is also no view involved. It's obvious that this is a very powerful feature which can also lead to a lot of confusion - so use it with care. For the above example, Virtual Private Database or a View would, of course, have been the better choice.
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.
But DBMS_ADVANCED_REWRITE might be an approach to tackle performance problems. Think about a complex SQL query (Joins, Computations) which you like to have precomputed, but without having all the setup required for materialized views. The following example does a query rewrite from a query with a join to a simple select on a single table.
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 ...
Executing the query (with join) results in a single-table-select, as the execution plan indicates.
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.
This feature has some restrictions ....
  • Bind variables are not supported. So you cannot declare a equivalence with e.g. a variable in the WHERE clause
  • Expressions like SYSDATE, ROWNUM, USER und others are not supported - ORA-30353
  • If the involved tables have uncommitted changes in your session, the query rewrite will not work.
  • When you declare a Query Rewrite Equivalence with a join, use the "old" join syntax - the ANSI syntax will not work. But you can use the ANSI syntax while querying - the previously declared equivalende with the old syntax will be used. Of course, you need to set REWRITE_MODE to another value then TEXT_MATCH - GENERAL would be an alternative.
  • Only SELECT queries are supported. DML statements will not work.
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.
These restrictions make it obvious that the feature has been designed for Data Warehouse usage. The usage scenarios are limited - but in particular cases it might be a solution to tackle SQL performance problems.

Keine Kommentare:

Beliebte Postings