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).