5 Replies Latest reply: Jan 4, 2013 11:59 AM by rp0428 RSS

    Unique Index which is case insensitive

    960593
      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
          Check DBA_IND_EXPRESSIONS or ALL_IND_EXPRESSIONS
          • 2. Re: Unique Index which is case insensitive
            BluShadow
            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
              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
                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
                  >
                  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.