Oracle Scratchpad

March 4, 2013

Duplicate indexes ?

Filed under: Indexing,Oracle,Troubleshooting — Jonathan Lewis @ 5:39 pm BST Mar 4,2013

I don’t think this is likely to happen on a production system (until 12c) – but look what you can do if you try hard enough:

  1  select
  2     index_name, column_name from user_ind_columns
  3  where
  4     table_name = 'T1'
  5  order by
  6*    index_name , column_position
SQL> /

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
T1_I1                N1
                     V1

T1_I2                N1
                     V1


4 rows selected.

That’s a straight cut-n-paste from an Oracle 11.1.0.7 SQL*Plus session. (You can tell I typed it in real time because I missed the return before the FROM, and couldn’t be bothered to go back and do it again ;) )

20 Comments »

  1. A partitioned table?

    Comment by Colin 't Hart — March 4, 2013 @ 5:46 pm BST Mar 4,2013 | Reply

  2. both tables t1 belong to different schemas?

    Comment by Maxim — March 4, 2013 @ 6:16 pm BST Mar 4,2013 | Reply

    • Is this possible when querying from user_ind_columns?

      Comment by Colin 't Hart — March 4, 2013 @ 6:27 pm BST Mar 4,2013 | Reply

      • SQL> select * from v$version;

        BANNER
        ——————————————————————————–
        Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
        PL/SQL Release 11.2.0.3.0 – Production
        CORE 11.2.0.3.0 Production
        TNS for Linux: Version 11.2.0.3.0 – Production
        NLSRTL Version 11.2.0.3.0 – Production

        SQL> show user
        USER is “SCOTT”
        SQL> create table t1(n1 number,v1 varchar2(100));

        Table created.

        SQL> create index t1_i1 on t1(n1,v1);

        Index created.

        SQL> create table hr.t1(n1 number,v1 varchar2(100));

        Table created.

        SQL> create index t1_i2 on hr.t1(n1,v1);

        Index created.

        SQL>
        SQL> select
        2 index_name, column_name from user_ind_columns
        3 where
        4 table_name = ‘T1′
        5 order by
        6 index_name , column_position;

        INDEX_NAME COLUMN_NAM
        ———- ———-
        T1_I1 N1
        V1
        T1_I2 N1
        V1

        Comment by Maxim — March 4, 2013 @ 6:45 pm BST Mar 4,2013 | Reply

        • Interesting that we recently discussed about creating indexes on the one table with same index name but in different schemes. The only reason was a hinting with that index for different users :)

          Comment by Sayan Malakshinov — March 4, 2013 @ 6:51 pm BST Mar 4,2013

  3. I got same output but with one trick :) How can i hide my answer in a spoiler?

    Comment by Sayan Malakshinov — March 4, 2013 @ 6:22 pm BST Mar 4,2013 | Reply

  4. create index id1 on index_test(id1,id2);
    create index id2 on index_test(id1,id2) nosegment;

    Regards
    GregG

    Comment by goryszewskig — March 4, 2013 @ 6:40 pm BST Mar 4,2013 | Reply

  5. yet another one (didn’t know – tables and cluster belong to different namespace):
    create cluster t1(n1 number,v1 varchar2(100));
    create index t1_i1 on cluster t1;
    create table t1(n1 number,v1 varchar2(100)) cluster t1(n1,v1)
    create index t1_i2 on t1(n1,v1);

    Comment by Maxim — March 4, 2013 @ 7:10 pm BST Mar 4,2013 | Reply

  6. Excellent response – it’s amazing how many options you can come up with when several people are given the same problem. The most interesting one (possibly) is the NOSEGMENT index (I hadn’t realised that you could do that) because the columns appear in dba_ind_columns, but the index doesn’t appear in dba_indexes.

    I have thought of one more solution – there may be oothers – but it’s definitely not one that you would expect to see in a production system.

    Comment by Jonathan Lewis — March 4, 2013 @ 8:54 pm BST Mar 4,2013 | Reply

  7. drop table t1 purge;
    
    create table t1( "N1
    V1" number,
    "N1
    V1
    " number);
    
    create index t1_i1 on t1("N1
    V1");
    
    create index t1_i2 on t1("N1
    V1
    ");
    
    sokrates@11.2 > REM now I have to cheat, so
    sokrates@11.2 > set feedback off
    sokrates@11.2 > REM end of cheat
    sokrates@11.2 > select index_name,column_name from user_ind_columns where table_name='T1' order by index_name, column_position;
    
    INDEX_NAME           COLUMN_NAME
    -------------------- --------------------
    T1_I1                N1
                         V1
    
    T1_I2                N1
                         V1
    

    Comment by Sokrates — March 5, 2013 @ 12:30 pm BST Mar 5,2013 | Reply

    • Sokrates,

      Cunning and devious, and definitely in the region of “unlikely to be seen on a production system” but it doesn’t pass the test because my output shows: “4 rows selected”.

      Comment by Jonathan Lewis — March 5, 2013 @ 1:02 pm BST Mar 5,2013 | Reply

      • Combinig both:

        16:14:09 @sd10>create table t1(n1 number,"V1
        16:14:18   2  " varchar2(2), v1 varchar2(3));
        
        Table created.
        
        Elapsed: 00:00:00.01
        16:14:22 @sd10>create index t1_i1 on t1(n1,"V1
        16:14:28   2  ");
        
        Index created.
        
        Elapsed: 00:00:00.01
        16:14:32 @sd10>create index t1_i2 on t1(n1,v1) ;
        
        Index created.
        
        Elapsed: 00:00:00.00
        16:14:37 @sd10>select index_name,column_name from user_ind_columns
        16:14:43   2  where table_name='T1'
        16:14:47   3  order by index_name, column_position;
        
        INDEX_NAME                     COLUMN_NAME
        ------------------------------ ------------------------------
        T1_I1                          N1
        T1_I1                          V1
        T1_I2                          N1
        T1_I2                          V1
        
        4 rows selected.
        
        Elapsed: 00:00:00.04
        

        Comment by Владимир Андреев — March 5, 2013 @ 3:19 pm BST Mar 5,2013 | Reply

  8. Jonathan,

    my first idea was dbms_redefinition but I think the following “solution” is also “unlikely to be seen on a production system” …:

    drop table t1;
    
    create table t1 (
      n1 number
    , v1 number
    );
    
    create index t1_i1 on t1(n1, v1);
    create index t1_i2 on t1(v1, n1);
    
    create view user_ind_columns
    as
    select index_name
         , table_name
         , column_name
         , case when column_name = 'N1' then 1 else 2 end column_position
      from sys.user_ind_columns;
    
    select index_name
         , column_name
      from user_ind_columns
     where table_name = 'T1'
     order by index_name 
            , column_position;
    
    INDEX_NAME                     COLUMN_NAME
    ------------------------------ -----------
    T1_I1                          N1
                                   V1
    T1_I2                          N1
                                   V1
    

    Regards

    Martin

    Comment by Martin Preiss — March 5, 2013 @ 3:53 pm BST Mar 5,2013 | Reply

    • Martin,

      I think that one is called an imaginative way of tendering your resignation ;)
      I think I’m going to disqualify you for tampering with intent to discredit data dictuinary views.

      Mind you, I think the xxx_indexes views may need some adjustment so that nosegment indexes can be seen; there’s a predicate bitand(flags,4096)= 0″ that hides them – and I can’t find any simple, “legal”, way to see them other than by inference that their columns exist when they can’t be seen.

      Comment by Jonathan Lewis — March 5, 2013 @ 7:11 pm BST Mar 5,2013 | Reply

      • the substantial answers had already been given – so I had to find something different … – and rereading my suggestion I see that I wrote dbms_redefinition but was thinking about dbms_advanced_rewrite … to get a second reason to be disqualified…

        Comment by Martin Preiss — March 5, 2013 @ 9:00 pm BST Mar 5,2013 | Reply

  9. [...] Jonathan Lewis has an interesting blog post about duplicate indexes. [...]

    Pingback by Latest data Industry news round up, Log Buffer #310 — March 15, 2013 @ 2:25 pm BST Mar 15,2013 | 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,905 other followers