Dienstag, Februar 07, 2012

Interval Partitioninig und das Ende aller Tage

Dass das Interval Partitioning leider noch ein paar unerfreuliche Bugs enthält, habe ich gelegentlich schon mal erwähnt. Heute ist mir aufgefallen, dass die Verwendung des sehr beliebten Datums-Default-Werts '31.12.9999' bei einer nach einer Datumsangabe intervall partitionierten Tabelle nicht ganz das gewünschte Ergebnis bringt:

create table test_interval_max_default ( mydate date)
partition by range (mydate)
interval (NUMTOYMINTERVAL(1,'MONTH'))
(partition test_p1 values less than (to_date('20120201', 'yyyymmdd')));

Tabelle wurde erstellt.

insert into test_interval_max_default (mydate) values (to_date('30.11.9999', 'dd.mm.yyyy'));

1 Zeile wurde erstellt.

insert into test_interval_max_default (mydate) values (to_date('01.12.9999', 'dd.mm.yyyy'));

insert into test_interval_max_default (mydate) values (to_date('01.12.9999', 'dd.mm.yyyy'))
            *
FEHLER in Zeile 1:
ORA-01841: (Volles) Jahr muss zwischen -4713 und +9999 liegen und darf nicht 0 sein

Nun ist '31.12.9999' sicher ein ziemlich unglücklicher Default-Wert - nicht zuletzt, weil er den CBO, der in Abwesenheit von Histogrammen eine Gleichverteilung der Werte zwischen Minimum und Maximum annimmt, zur massiven Fehleinschätzung von Kardinalitäten führen kann -, aber dass man ihn einfach nicht einfügen kann, erscheint doch etwas drastisch. In diesem Fall handelt es sich aber nicht um einen Bug, sondern um ein dokumentiertes Verhalten:
Note, however, that using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.
Es bleibt also nur die Verwendung eines anderen Default-Werts für "das Ende aller Tage" oder der Verzicht auf Interval Partitioning.

Nachtrag 09.02.2012: Der Fall ist tatsächlich sogar noch unerfreulicher: bereits eine Query mit einer entsprechenden Bedingung ruft ora-01841 hervor:
SQL> select * from test_interval_max_default where mydate = to_date('31.12.9999', 'dd.mm.yyyy');
select * from test_interval_max_default where mydate = to_date('31.12.9999', 'dd.mm.yyyy')
              *
FEHLER in Zeile 1:
ORA-01841: (Volles) Jahr muss zwischen -4713 und +9999 liegen und darf nicht 0 sein
Angesichts dieses Verhaltens schwindet meine Zuneigung zum Interval Partitioning allmählich - obwohl es eigentlich eine so schöne Idee ist. Nun ja, vielleicht funktioniert es mit Version 12 oder 14.

Kommentare:

  1. "Gut gemeint", könnte man auch über das Interval Partitioning sagen... bei fast allen bisherigen Betrachtungen als Alternative zu skriptgesteuerter Partitionierung habe ich diese Alternative wieder verwerfen müssen.
    Jetzt würde mich aber noch interessieren, was ein glücklicherer Default-Wert anstelle des '31.12.9999' wäre?
    Viele Grüße, Uwe Küchler

    AntwortenLöschen
  2. ja, "gut gemeint" trifft's recht genau.

    Das Thema "Default-Wert" ist natürlich schwierig. Ich hätte gerne etwas, das dem cbo keinen unnötigen Ärger macht. Tom Kyte hat sich immer wieder für NULL statt 31.12.9999 ausgesprochen (und liefert unter http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1925158700346862036 eine ganz interessante Variante zum Thema mit einer indizierten virtuellen Spalte). Manche Leute mögen auch den 31.12.2099 (was ich persönlich eher unerfreulich finde); und in DWH-Kreisen kommen die Kimball-Leute gerne mit Smart-Keys (also int-Werten statt date-Angaben), was der Herr Kyte (und der cbo) wahrscheinlich noch weniger mögen.

    Tatsächlich bin ich in der Regel am 31.12.9999 hängengeblieben - vielleicht auch nur, weil's mich irgendwie an "In the Year 2525" (Zager & Evans, 1969) erinnert ...

    Viele Grüße

    MP

    AntwortenLöschen