Oracle Scratchpad

May 7, 2014

Quiz Night

Filed under: Indexing,Oracle — Jonathan Lewis @ 2:18 pm BST May 7,2014

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 11.2.0.4:

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.

Answer

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 0×00. (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.

 

11 Comments »

  1. Home time 12:39, local time 19:39 – I’ve opened the posting for comments in case anyone wants to race in with an answer.

    Comment by Jonathan Lewis — May 8, 2014 @ 12:39 pm BST May 8,2014 | Reply

    • Hi Jonathah.
      As explanation of Martin answer…:)
      Descending index is a function-based index. Function sys_op_descend in use to convert original column value into descendent format. If NULL passed into function, it returns “00″ and this valur stored in index. So the reason why descendent index creation failed – column v1 has more than 1 NULL’s. Normal ascending index uses original value, so NULL not stored in tree structure so no duplicate keys.

      Comment by Andrey — May 8, 2014 @ 12:46 pm BST May 8,2014 | Reply

  2. create table t1(v1 number);

    insert into t1(v1) values (NULL);

    insert into t1(v1) values (NULL);

    Comment by Martin Preiss — May 8, 2014 @ 12:43 pm BST May 8,2014 | Reply

    • a descending index is a FBI on a column combining the original value with an appended 0xff – http://richardfoote.wordpress.com/2011/09/09/descending-indexes-solution-yellow-submarine/#comment-32792.

      Comment by Martin Preiss — May 8, 2014 @ 12:46 pm BST May 8,2014 | Reply

      • Hm, it seems that 0xFF is appended only if length less than 4000:

        SQL> col a format a10;
        SQL> col b format a10;
        SQL> select utl_raw.substr(sys_op_descend(lpad('x',40  ,'x')),-1) a
          2        ,utl_raw.substr(sys_op_descend(lpad('x',4000,'x')),-1) b
          3   from dual
          4  /
        
        A          B
        ---------- ----------
        FF         87
        
        1 row selected.
        

        Comment by Sayan Malakshinov — May 8, 2014 @ 1:21 pm BST May 8,2014 | Reply

      • And exactly appending 0xFF to index value causes errors:
        from my tests with ORA-1428:

        SQL> create table xt_test(v varchar2(4000));
        SQL> insert into xt_test values(rpad('a',4000,'a'));
        SQL> insert into xt_test values(rpad('b',3999,'b'));
        SQL> create index ix_xt_test on xt_test(v desc);
        SQL>
        SQL> column column_name new_value new_col;
        SQL> column a format a10;
        SQL> select column_name from user_tab_cols where table_name='XT_TEST' and column_name!='V';
        
        COLUMN_NAME
        ------------------------------------------------------------------------------------------
        SYS_NC00002$
        
        1 row selected.
        
        SQL> select utl_raw.substr(&new_col,3999) a from xt_test;
        
        A
        ----------
        9E9E
        9DFF
        
        2 rows selected.
        

        Comment by Sayan Malakshinov — May 8, 2014 @ 1:30 pm BST May 8,2014 | Reply

  3. Jonathan,

    in addition to simple answer about NULLs, I’ve also found another limitation for SYS_OP_DESCEND and SYS_OP_UNDESCEND:
    We cannot use such indexes for strings with length equal to 4000 bytes because of size limitation:

    SQL> create table xt_test(v varchar2(4000));
    
    Table created.
    
    SQL> insert into xt_test values(rpad('a',4000,'a'));
    
    1 row created.
    
    SQL> create index ix_xt_test on xt_test(v desc);
    
    Index created.
    
    SQL> select * from xt_test where v=rpad('a',4000,'a');
    select * from xt_test where v=rpad('a',4000,'a')
                  *
    ERROR at line 1:
    ORA-01428: argument
    '9e9e9e9e9e9e9e9e9e9e9e9e9e <SKIPPED>
    
    
    SQL> select v from xt_test t where v > 'a' order by v;
    select v from xt_test t where v > 'a' order by v
                  *
    ERROR at line 1:
    ORA-01428: argument
    '9e9e9e9e9e9e9e9e9e9e9e9e9e <SKIPPED>
    
    
    SQL> column column_name new_value new_col;
    SQL> select column_name from user_tab_cols where table_name='XT_TEST' and column_name!='V';
    
    COLUMN_NAME
    ------------------------------------------------------------------------------------------
    SYS_NC00002$
    
    1 row selected.
    
    SQL> select SYS_OP_UNDESCEND(&new_col) from xt_test;
    select SYS_OP_UNDESCEND(SYS_NC00002$) from xt_test
                                               *
    ERROR at line 1:
    ORA-01428: argument
    '9e9e9e9e9e9e9e9e9e9e9e9e9e <SKIPPED>
    

    Full test case:

    create table xt_test(v varchar2(4000));
    insert into xt_test values(rpad('a',4000,'a'));
    create index ix_xt_test on xt_test(v desc);
    select * from xt_test where v=rpad('a',4000,'a');
    select v from xt_test t where v > 'a' order by v;
    column column_name new_value new_col;
    select column_name from user_tab_cols where table_name='XT_TEST' and column_name!='V';
    select SYS_OP_UNDESCEND(&new_col) from xt_test;
    

    Comment by Sayan Malakshinov — May 8, 2014 @ 1:03 pm BST May 8,2014 | Reply

  4. Hi Jonathan,

    given that null values are stored in a non-unique descending index (as 0×00), is it possible to use that to our advantage if we have queries which also search for nulls? I know there are other techniques to achieve that, such as including a constant value/not nullable column in the index definition or using a function based index with an appropriate expression, but in your opinion, is there any use case where an index with a DESC sorted nullable column could be a better choice? The drawbacks I see are:
    - since the sys_op_descend function is undocumented it’s not safe to use it in production (or is there a way to not use it in the WHERE clause if explicitly searching for nulls?)
    - the concerns about index space utilization (50-50 splits) and reduced accuracy of the cardinality estimates as pointed out by the link to the Richard Foote’s blog that Martin already posted

    A simple testcase of what I mean:

    create table t1 as
    select trunc((rownum-1)/15) col1,
    trunc((rownum-1)/15) col2,
    rpad (‘a’,50,’a’) padding
    from dual
    connect by level true);

    select *
    from t1
    where sys_op_descend(col1) = sys_op_descend(null);

    ————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————
    | 0 | SELECT STATEMENT | | | | 2 (100)| |
    | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 65 | 2 (0)| 00:00:01 |
    |* 2 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
    ————————————————————————————

    Predicate Information (identified by operation id):
    —————————————————

    2 – access(“T1″.”SYS_NC00004$”=HEXTORAW(’00′) )

    create index i2 on t1 (col2 desc, col1);

    select *
    from t1
    where sys_op_descend(col2) = sys_op_descend(null)
    and col1 is null;

    ————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————
    | 0 | SELECT STATEMENT | | | | 2 (100)| |
    | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 65 | 2 (0)| 00:00:01 |
    |* 2 | INDEX RANGE SCAN | I2 | 1 | | 1 (0)| 00:00:01 |
    ————————————————————————————

    Predicate Information (identified by operation id):
    —————————————————

    2 – access(“T1″.”SYS_NC00005$”=HEXTORAW(’00′) AND “COL1″ IS NULL)

    Thank you in advance and regards

    Comment by Jure Bratina — May 9, 2014 @ 12:52 pm BST May 9,2014 | Reply

    • Sorry for the unformatted source code, I used the sourcecode tags in square brackets….

      Comment by Jure Bratina — May 9, 2014 @ 12:57 pm BST May 9,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,877 other followers