"Rekursive" Table Functions: Ohne Objekttypen

English title: Recursive able Functions - without object types!

Erst vor kurzem hatte ich ein wenig was über die neue rekursive WITH-Klausel gepostet. Diese ist ab Version 11.2 verfügbar und erlaubt es, hierarchische Abfragen ohne START WITH - CONNECT BY zu formulieren.

Only a short time ago I posted about the new recursive subqueries, which are available in 11.2 and which allow to do recursive queries without START WITH - CONNECT BY.

Interessant ist, dass diese Technik es auch erlaubt, Table Functions auf eine andere Art zu bauen - man kann sich so eine SQL-Abfrage schreiben, die mit Hilfe der Rekursion zusätzliche Tabellenzeilen generieren kann. Hier ein Beispiel: Den Tilgungsplan, der hier als "klassische" Table Function bereitsteht, kann man auch mit einer rekursiven WITH-Klausel bauen. Das sähe dann so aus ...

The interesting bit is that this kind of query could also be used as a replacement for table functions - the recursion allows to create new, "table-independent" rows in a query result. And here is an example: I've done the table function for the "mortgage plan" as a recursive query. This query looks like this ...

col kapital format 999999990D00
col zinsen format 999999990D00
col tilgung format 999999990D00
col restwert format 999999990D00

with konstanten (datum, kapital, zinssatz, rate, rhythmus) as (
  select 
    trunc(sysdate, 'MONTH') datum,
    100000 kapital,
    5 zinssatz,
    600 rate,
    1 rhythmus
  from dual
), 
tilgungsplan (datum, kapital, zinsen, tilgung, restwert) as (
  select 
    to_char(k.datum) datum,
    k.kapital, 
    k.kapital * k.zinssatz / (100 * (12 / k.rhythmus)) zinsen,
    k.rate - k.kapital * k.zinssatz / (100 * (12 / k.rhythmus)) tilgung,
    k.kapital - ( k.rate - k.kapital * k.zinssatz / (100 * (12 / k.rhythmus))) restwert
  from konstanten k
  union all (
   select 
    to_char(add_months(to_date(s.datum),k.rhythmus)) datum,
    s.restwert kapital,
    s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)) zinsen,
    least(
      k.rate - s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)),
      s.restwert
    ) tilgung,
    s.restwert - ( 
      least(
        k.rate - s.restwert * k.zinssatz / (100 * (12 / k.rhythmus)),
        s.restwert
      )
   ) restwert
   from konstanten k, tilgungsplan s
   where restwert > 0 
  )
)
select * from tilgungsplan
/

Der Vorteil ist, dass man keine Objekttypen zum Beschreiben der Ergebnismenge mehr braucht - man kann eine solche Abfrage mit nichts weiter als einem CREATE SESSION-Privileg ausführen. Allerdings sind die "klassischen" Table Functions (im Moment noch) wesentlich schneller - wer möchte, kann es ja vergleichen. Man kann auf jeden Fall festhalten, dass man mit der rekursiven WITH-Klausel mehr machen kann, als Parent-Child-Beziehungen abzufragen ...

As an advantage you don't need to create object types (which you need for pipelined table functions) - so this query could be executed with just the CREATE SESSION privilege. But (currently) this query is significantly slower than the pipelined function. And the result of all this ...? Using the new recursive WITH clause you can do much more than just querying parent-child relationships.

Neues Feature für "classic" import: DATA_ONLY

English title: New Feature for "classic" imp: DATA_ONLY

In Oracle 11.2 gibt es beim "klassischen" Import-Werkzeug einen sehr nützlichen neuen Parameter: DATA_ONLY. Damit wird das Import-Utility angewiesen nur die Daten und keinerlei Metadaten wie Tabellen, Typen, PL/SQL-Prozeduren oder Trigger zu importieren (Dokumentation). Wenn die Tabellen, in die die Daten importiert werden sollen, nicht da sind, wird ein Fehler ausgelöst. Ruft man imp help=yes auf, so wird der neue Parameter angezeigt ...

In Oracle 11.2 there is a new feature for the "classic" import utility - and this is IMHO a very helpful one: DATA_ONLY. When specified as DATA_ONLY=Y the import utility processes only the data of an import file and skips all the metadata. That means, no tables, types, PL/SQL objects or other database objects are being created. When a table for the data is not present, import raises an error message. Calling import with help=yes shows the new parameter.

$ imp help=yes

:
FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames
SHOW     just list file contents (N) TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           INCTYPE      incremental import type
INDEXES  import indexes (Y)          COMMIT       commit array insert (N)
ROWS     import data rows (Y)        PARFILE      parameter filename
LOG      log file of screen output   CONSTRAINTS  import constraints (Y)
DESTROY                overwrite tablespace data file (N)
:
RESUMABLE_TIMEOUT      wait time for RESUMABLE
COMPILE                compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION  import streams general metadata (Y)
STREAMS_INSTANTIATION  import streams instantiation metadata (N)
DATA_ONLY              import only data (N)
VOLSIZE                number of bytes in file on each volume of a file on tape
:

Früher hat man sich, wenn die Tabellen schon da waren mit IGNORE=Y geholfen, DATA_ONLY=Y ist aber noch besser geeignet - insbesondere, wenn man die Datenbankobjekte ohnehin vorher per Skript eingespielt hat. Die Data Pump kann das natürlich auch - und vieles mehr - daher empfehle ich generell die Nutzung der Data Pump. Aber man kann es sich ja auch nicht immer aussuchen - ab und zu hat man eben ein altes, klassisches Export-File und muss das importieren. Vielleicht ist diese neue Option dann für den einen oder anderen nützlich ...

In the past we worked with IGNORE=Y when the tables were already present. But import then recreated all PL/SQL objects then. So DATA_ONLY is much better suited in cases where all the metadata is already present and we just want to import table rows. In generel I'd recommand to use Data Pump instead of the "classic" export and import utilities - with Data Pump you can adjust the export and import process in a much more fine granular manner. But sometimes we just have an "old" dumpfile and in those cases this new option might be helpful ...