This discussion is archived
5 Replies Latest reply: Jan 4, 2013 9:59 AM by rp0428 RSS

Unique Index which is case insensitive

960593 Newbie
Currently Being Moderated
Hi Guys,


I have created an unique index which is case insensitive that means both 'abc' and 'ABC' will not be allowed.
I have used this syntax to create

CREATE UNIQUE INDEX U01_ORG_TIER ON ORG_TIER
(
UPPER(org_tier_name) ASC,
oh_template_id ASC
)
     TABLESPACE STATIC_NDX_TBS
     LOGGING;

INDEX_NAME TABLE_NAME COLUMN_NAME
======== ======== ==========
U01_ORG_TIER     ORG_TIER     OH_TEMPLATE_ID     
U01_ORG_TIER     ORG_TIER     SYS_NC00006$     

And this is working fine however there are some issues with this.When I am checking the all_ind_columns the col org_tier_name is missing instead there is a column SYS_NC00006$ which is difficult to know the real col.

Is there any way to come around this.
Please suggest .


Thanks in advance

Regards,
Papi

Edited by: 957590 on Jan 4, 2013 2:07 AM
  • 1. Re: Unique Index which is case insensitive
    jeneesh Guru
    Currently Being Moderated
    Check DBA_IND_EXPRESSIONS or ALL_IND_EXPRESSIONS
  • 2. Re: Unique Index which is case insensitive
    BluShadow Guru Moderator
    Currently Being Moderated
    That's because it's a function based index i.e. the index is not on the actual column but on a function instead.
  • 3. Re: Unique Index which is case insensitive
    Bawer Journeyer
    Currently Being Moderated
    957590 wrote:
    Is there any way to come around this.
    Please suggest .

    Edited by: 957590 on Jan 4, 2013 2:07 AM
    first call this:
    call DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORG_TIER');
    than run this query:
    select * from user_tab_col_statistics where table_name='ORG_TIER';
    to change column name:
    alter table ORG_TIER rename column SYS_NC00006$ to NEW_HIDDEN_COL_NAME;
    Edited by: Bawer on 04.01.2013 11:34
  • 4. Re: Unique Index which is case insensitive
    stefan nebesnak Journeyer
    Currently Being Moderated
    See statement below..
    SELECT B.table_name, 
           B.index_name, 
           Substr(Trim(B.column_name), 1, 30), 
           A.column_expression 
    FROM   dba_ind_expressions A, 
           dba_ind_columns B 
    WHERE  A.index_name(+) = B.index_name 
           AND A.index_owner(+) = B.index_owner 
           AND A.table_name(+) = B.table_name 
           AND A.table_owner(+) = B.table_owner 
           AND A.column_position(+) = B.column_position 
           AND A.table_name = 'ORG_TIER'; 
  • 5. Re: Unique Index which is case insensitive
    rp0428 Guru
    Currently Being Moderated
    >
    That's because it's a function based index i.e. the index is not on the actual column but on a function instead.
    >
    Not quite - the index is on a 'hidden' column that holds the value resulting from the function.

    This code creates a fourth, hidden, column
    create table dept_copy as select * from dept
    
    CREATE UNIQUE INDEX ndx_dname ON dept_copy(UPPER(dname))
    
    alter table dept_copy rename column sys_nc00004$ to NEW_HIDDEN_COL_NAME
    It is this hidden column that is indexed.

    If you query tab$ for the table you will see INTCOLS=4 and KERNELCOLS=3. INTCOLS is 'internal columns' while KERNELCOLS is the original colum count.

    Then querying col$ for the table shows all four columns. And, if you rename the column as the other responder indicated you will see the NAME value in col$ change.

    You can also see the hidden columns in the XML metadata
    select dbms_metadata.get_xml('TABLE', 'DEPT_COPY', 'SCOTT') from dual
    
      <COL_LIST_ITEM>
       <OBJ_NUM>75260</OBJ_NUM>
       <COL_NUM>0</COL_NUM>
       <INTCOL_NUM>4</INTCOL_NUM>
       <SEGCOL_NUM>0</SEGCOL_NUM>
       <PROPERTY>65832</PROPERTY>
       <NAME>NEW_HIDDEN_COL_NAME</NAME>
       <TYPE_NUM>1</TYPE_NUM>
       <LENGTH>14</LENGTH>
       <NOT_NULL>0</NOT_NULL>
       <DEFLENGTH>14</DEFLENGTH>
       <DEFAULT_VAL>UPPER(&quot;DNAME&quot;)</DEFAULT_VAL>
       <CHARSETID>178</CHARSETID>
       <CHARSETFORM>1</CHARSETFORM>
       <BASE_INTCOL_NUM>4</BASE_INTCOL_NUM>
       <BASE_COL_TYPE>0</BASE_COL_TYPE>
       <SPARE1>0</SPARE1>
       <SPARE2>0</SPARE2>
       <SPARE3>14</SPARE3>
      </COL_LIST_ITEM>
    I'll leave it as an exercise for you to determine if this 'hidden' column exists in the datafiles.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points