16. Dezember 2008

Subquery oder Inline View? Die SQL WITH Klausel und deren Ausführung

English title: Subquery or inline view: The WITH clause and its execution

Als letztes Posting für dieses Jahr möchte ich ein wenig was über die WITH-Klausel erzählen; ich selbst nutze sie ziemlich häufig und auch sehr gerne - aber immer wieder bekomme ich dann Fragen wie: "Was ist denn das ...?". Zunächst ein Beispiel - damit ist alles leichter ...
My last posting for this year will be about the SQL WITH clause. I use it very frequently but also get often questions like "What's that?. We'll start with an example:
with dept_sal as (
  select d.deptno, d.dname, sum(e.sal) sum_sal
  from dept d join emp e on (d.deptno = e.deptno)
  group by d.deptno, d.dname
)
select deptno, dname, sum_sal
from dept_sal 
where sum_sal > 2000
Wie man sieht, ist die WITH-Klausel ein etwas andere Form der Subquery; die "Subquery" erhält mit der WITH-Klausel einen Namen und kann unter diesem wie eine View nachfolgend beliebig wiederverwendet werden. Daher wird die Subquery innerhalb einer WITH-Klausel auch Inline View genannt. Und natürlich gehen auch mehrere WITH-Klauseln in einer Abfrage:
The WITH clause is, as you can see, another form of a subquery. The subquery gets a name (which is valid only for this particular SQL query) and can be reused under this name as often as the developer wants. Since this is very similar to a view the query inside a WITH clause is also called an inline view.
with dept_sal as (
  select d.deptno, d.dname, sum(e.sal) sum_sal
  from dept d join emp e on (d.deptno = e.deptno)
  group by d.deptno, d.dname
), dept_sal_mit_avg as (
  select 
    deptno, dname, sum_sal,
    avg (sum_sal) over (order by dname) dept_sum_sal_avg
  from dept_sal
)
select deptno, dname, sum_sal
from dept_sal_mit_avg 
where sum_sal > dept_sum_sal_avg
/
Ein direkter Vorteil der WITH-Klausel gegenüber einer "normalen" Subquery ist, dass man die Inline Views mehrfach wiederverwenden kann. Eine Subquery müsste man mehrfach in die Abfrage hineinschreiben. Persönlich bin ich der Ansicht, dass komplexere Abfragen mit Inline Views übersichtlicher bleiben als mit Subqueries - aber dazu gibt es bestimmt auch andere Ansichten.
One direct advantage of the WITH clause over a traditional subquery is that it can be used multiple times. A subquery would have to created as view objects or the actual query would have to be repeated. And my personal thinking is that complex queries are much better readable with inline views instead of subqueries.
Schauen wir uns nun noch an, wie der Optimizer mit einer WITH-Klausel umgeht. Das ist vor allem dann interessant, wenn man deren größten Vorteil nutzen, eine Inline View also mehrfach in einer Abfrage nutzen möchte. Dazu ein Beispiel.
Now let's have a look how the optimizer deals with the inline view. This is particular interesting when we use the inline view multiple times and the inline view query is expensive. The following example illustrates this:
set serveroutput on

create or replace function doit return number as
begin
  dbms_output.put_line('function called.');
  return 1;
end;
/

with do_func as (
  select doit val from dual
)
select val from do_func, emp
/
Das Beispiel ruft die Funtion doit in einer Inline View als SELECT .. FROM DUAL auf. Diese Inline View wird anschließend mit der Tabelle EMP per Join zusammengeführt. Nun ist die Frage: Wie oft wird doit ausgeführt. Die Codezeile mit DBMS_OUTPUT sagt es uns.
The function doit (which just writes a line with DBMS_OUTPUT) is being used in an inline view as SELECT ... FROM DUAL. Now this inline view gets joined with the EMP table (14 rows).Now the question: How often gets the function called?

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
:
:
function called.
Der Ausführungsplan:
Execution Plan:
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    14 |     5   (0)| 00:00:01 |
|   2 |   FAST DUAL        |      |     1 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------
Die Ausgabe function called sehen wir 14 mal; der Optimizer hat die Inline View also mit der "Hauptabfrage" gemerged und führt die Funktion entsprechend 14 mal aus. Man sieht es im Ausführungsplan: Nested Loops. Wenn die Funktion nun sehr teuer ist, ist genau dies u.U. nicht erwünscht - die Syntax der WITH-Klausel legt ja den Eindruck nahe, dass die Funktion nur einmal ausgeführt wird. Und das kann man auch erreichen: Im Prinzip kommen hierfür zwei Hints in Betracht: NO_MERGE, der in 11g neue RESULT_CACHE und das undokumentierte MATERIALIZE. Probieren wir zuerst NO_MERGE.
We see function called 14 times. The optimizer merged the inline view query with the "main" query and executed it using nested loops. Therefore the function is called for each row of the EMP table. If the function is very expensive this is not a very good execution strategy - and the syntax of the WITH clause leads to the assumption that the function is executed only one time. We'll try Optimizer hints to improve the behaviour: NO_MERGE, the in Oracle11g introduced RESULT_CACHE and the undocumented MATERIALIZE. First we try NO_MERGE.
with do_func as (
  select doit val from dual
)
select /*+ NO_MERGE (d) */ d.val from do_func d, emp e
/

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
function called.
Der Ausführungsplan:
Execution Plan:
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    14 |   182 |     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    14 |   182 |     5   (0)| 00:00:01 |
|   2 |   VIEW               |      |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    FAST DUAL         |      |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL  | EMP  |    14 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Die Funktion wurde nun nur noch zweimal ausgeführt. Das ist schon besser, aber man stellt sich schon die Frage, ob man die Datenbank nicht dazu bringen kann, die Funktion einmal auszuführen, sich das Ergebnis zu merken und dann weiterzuarbeiten. Und in Oracle11g kann man dies mit dem Result Cache erreichen. Wenn wir mit einer PL/SQL-Funktion arbeiten, muss diese allerdings als deterministisch deklariert sein. Deterministisch meint, dass gleiche Eingabeparameter zum gleichen Ergebnis führen. Ist das nicht der Fall, so bleibt nur der weiter hinten folgende undokumentierte MATERIALIZE-Hint.
The function is now called twice. This is much better but there is still the question whether it is possible that the optimizer executes the query once, "memorize" the result and continue. And this can be achieved in Oracle11g using the result cache. But when using a PL/SQL function, it is important that this is declared deterministic. Deterministic means that equal input parameters lead to equal function results. If your function is not deterministic then the undocumented MATERIALIZE hint might be a solution.
create or replace function doit return number deterministic as
begin
  dbms_output.put_line('function called.');
  return 1;
end;
/

with do_func  as (
  select /*+ result_cache */ doit val from dual
)
select /*+ result_cache */ d.val from do_func d, emp e
/

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
Die Funktion wird einmal ausgeführt. Der Ausführungsplan:
The function is being executed one. The Execution Plan:
----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |    14 |   182 |     5   (0)| 00:00:01 |
|   1 |  RESULT CACHE         | d0r3jha88bjgjgrpt9rgv2aw98 |       |       |            |          |
|   2 |   MERGE JOIN CARTESIAN|                            |    14 |   182 |     5   (0)| 00:00:01 |
|   3 |    VIEW               |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |     RESULT CACHE      | 3q99fnp78n4wr4nbdpj223u8b9 |       |       |            |          |
|   5 |      FAST DUAL        |                            |     1 |       |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | EMP                        |    14 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; dependencies=(SCOTT.EMP, SCOTT.DOIT); 
       name="with do_func  as (select /*+ result_cache */ doit val from dual)
             select/*+ result_cache */  d.val from do_func d, emp e"

   4 - column-count=1; dependencies=(SCOTT.DOIT); attributes=(single-row); 
       name="select /*+ result_cache */ doit val from dual"
Das Ergebnis der Inline View wurde im Result Cache gespeichert (im Ausführungsplan unter 4 erkennbar). Aber was ist, wenn die Funktion gar nicht deterministisch ist - also gleiche Eingabeparameter durchaus (vielleicht zu einer anderen Zeit) zu anderen Ergebnissen führen können)? Wenn die Abhängigkeiten in der Datenbank bekannt sind (bspw. das Funktionsergebnis hängt von den Inhalten einer Tabelle ab) könnte man die Abhängigkeiten deklarieren:
The inline view results were placed into the result cache ("4" in the execution plan). But what if the actual function is not deterministic? If the dependencies are known in the database (e.g. table contents) they can be declared in the function code as follows:
create or replace function doit return number result_cache relies_on ([table]) as
:
Wenn die Funktion nicht deterministisch ist und die Abhängigkeiten nicht bekannt sind (bspw. Zufallswerte, Abhängigkeit von der aktuellen Zeit), dann kann der Result Cache nicht genutzt werden. In diesen Fällen und in Oracle10g kann der undokumentierte MATERIALIZE hint eine Lösung sein.
If the function dependencies are not known in the database (random values, result depends on the current timestamp) then the result cache cannot be used. In those cases and in Oracle10g the undocumented MATERIALIZE hint might be a solution.
with do_func as (
  select /*+ MATERIALIZE */ doit val from dual
)
select d.val from do_func d, emp e
/

       VAL
----------
         1
:
:
         1

14 Zeilen ausgewählt.

function called.
Man sieht: Die Funktion wurde genau einmal ausgeführt. Schauen wir und den Ausführungsplan an:
The function is executed once. Look at the execution plan:
----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |    14 |   182 |     7   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | EMP                         |       |       |            |          |
|   3 |    FAST DUAL               |                             |     1 |       |     2   (0)| 00:00:01 |
|   4 |   MERGE JOIN CARTESIAN     |                             |    14 |   182 |     5   (0)| 00:00:01 |
|   5 |    VIEW                    |                             |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6604_40E25BF1 |     1 |    13 |     2   (0)| 00:00:01 |
|   7 |    BUFFER SORT             |                             |    14 |       |     5   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL      | EMP                         |    14 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Der Optimizer "merkt" sich das Ergebnis wirklich in einer temporären Tabelle. Ihr könnt auch nachsehen ... (als SYS)
The optimizer "memorizes" the query result in a temporary table. You can describe it (as SYS).
SQL> desc SYS_TEMP_0FD9D6604_40E25BF1
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 C0                                                 NUMBER
Gerade bei sehr teuren Funktionen (Beispiele wären Funktionen, welche Daten von extern laden) kann dies sehr nützlich sein. Beachtet aber bitte, dass der Hint MATERIALIZE von Oracle nicht dokumentiert ist; man müsste ihn also in jedem neuen Release testen ... Die Nutzung eines undokumentierten Hints sollte stets die "Ultima Ratio" sein.
This is extemely useful for long running or expensive subquries. But keep in mind that Oracle does not document this hint - so you have to test it in new releases. The usage of an undocumented hint should always the the "ultima ratio" - the choice when other things fail.
So - das war's für dieses Jahr. In zwei Wochen iss 'rum - und nächstes Jahr sieht man sich wieder. Bis dahin schöne Feiertage, kommt gut ins Neue Jahr und lasst den Rechner ein paar Tage aus.
That's it ... for 2008. In two weeks the year is over - Season's greetings and in 2009 we'll see each other again.

Kommentare:

Sabine Heimsath hat gesagt…

Danke Carsten, nach sowas hab ich (unbewusst) immer gesucht - schön, dass es schon da ist! :-)

Und wo ich schon gerade da bin, wünsche ich Dir und Deiner Familie ein schönes Weihnachtsfest und eine erholsame Urlaubszeit!

Viele Grüße,
Sabine

Anonym hat gesagt…

Danke sehr. Der Artikel ist recht interessant und wird gut umschrieben. Sehr informativer blog.

Marc Giombetti hat gesagt…

Danke für das Beispiel. Ich habe ein kleines Tutorial zusammengestellt wo ich die WITH clause verwende um temporäre statische Tabellen zu erzeugen:

http://www.giombetti.com/2014/02/14/using-sql-with-clause-to-create-temporary-static-tables-at-query-time/

Beliebte Postings