Mittwoch, Mai 07, 2014

Descending Index Quiz

Jonathan Lewis hat heute mal wieder eine Quizfrage gestellt und ausnahmsweise bin ich ziemlich schnell auf die Lösung gekommen. Die Frage lautet: wieso liefert ein descending Index einen duplicate Key Fehler, während ein entsprechender aufsteigender Index fehlerlos angelegt werden kann:

SQL> create unique index t1_i1 on t1(v1 desc);
create unique index t1_i1 on t1(v1 desc)
                                *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
 
 
SQL> create unique index t1_i1 on t1(v1);
 
Index created.

Die zugehörige Tabellendefinition könnte ungefähr folgendermaßen aussehen:

create table t1(v1 number);

insert into t1(v1) values (NULL);

insert into t1(v1) values (NULL);

Die Erklärung dafür lautet: ein descending index ist ein FBI (function based index) auf einer zusätzlichen generierten Spalte, bei der ein 0xFF an das Einerkomplement jedes Werts der Ursprungsspalte angehängt wird, und diese Ergänzung macht aus der NULL dann einen Wert, der als Duplikat betrachtet wird. Den normalen Index ohne die desc-Angabe interessieren die NULLS nicht, was die fehlerlose Anlage erklärt. Und den entscheidenden Hinweis zum Thema habe ich in Richard Footes hier verlinktem Artikel gefunden (und dort in einem Kommentar des Herrn Lewis).

Kommentare:

  1. Der SQL Server ist an der Stelle noch weniger ANSI-konform
    http://connect.microsoft.com/SQLServer/feedback/details/299229

    AntwortenLöschen
  2. Hallo Andrej,
    danke für die Ergänzung - das hatte ich auch noch in Erinnerung. Wobei ich im Oracle-Fall die Uneinheitlichkeit des Verhaltens bei der Index-Anlage fast noch seltsamer finde - auch wenn sie technisch erklärbar ist.
    Gruß
    Martin

    AntwortenLöschen