Okay – so it’s not night time in my home time-zone, but I’m in Singapore at the moment so it’s night time.
A very simple little quiz – so I’ve disabled comments for the moment and will re-enable them tomorrow morning to allow more people to have a chance to see the question without the solution.
Explain the anomaly displayed in the following “cut-n-paste” from a session running SQL*Plus on 22.214.171.124:
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.
Well it didn’t take long for an answer and several bits of related infomration to show up – as Martin pointed out, all I have to do is insert NULL into the table twice.
To create an entry in a descending index, Oracle takes the 1’s-complement of each column and appends an 0xFF byte to each column – except in the case of a null column where the null is replaced with a 0x00. (And, as Sayan points out, funny things happen if you have a varchar2() column which has already reached the 4,000 byte limit)
The point of the 1’s-complement is that if you walk through the stored values in ascending order you’re walking through the original values in descending – provided you have the 0xFF on the end of each non-null entry.