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 ;) )
A partitioned table?
Comment by Colin 't Hart — March 4, 2013 @ 5:46 pm UTC Mar 4,2013 |
both tables t1 belong to different schemas?
Comment by Maxim — March 4, 2013 @ 6:16 pm UTC Mar 4,2013 |
Is this possible when querying from user_ind_columns?
Comment by Colin 't Hart — March 4, 2013 @ 6:27 pm UTC Mar 4,2013 |
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 UTC Mar 4,2013 |
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 UTC Mar 4,2013
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 UTC Mar 4,2013 |
[spoiler]https://gist.github.com/xtender/5084294 – Am i right? [/spoiler]
Comment by Sayan Malakshinov — March 4, 2013 @ 6:29 pm UTC Mar 4,2013 |
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 UTC Mar 4,2013 |
Thanks, i forgot about this old feature :)
Comment by Sayan Malakshinov — March 4, 2013 @ 6:45 pm UTC Mar 4,2013 |
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 UTC Mar 4,2013 |
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 UTC Mar 4,2013 |
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 V1Comment by Sokrates — March 5, 2013 @ 12:30 pm UTC Mar 5,2013 |
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 UTC Mar 5,2013 |
Combinig both:
Comment by Владимир Андреев — March 5, 2013 @ 3:19 pm UTC Mar 5,2013 |
And to get a 100% match:
break on index_name skip 1
Comment by Владимир Андреев — March 5, 2013 @ 3:25 pm UTC Mar 5,2013
Vladimir,
That’s how I got my first result, although it looks as if you’ve included a linefeed in your quoted column name, and I just added an invisible space – viz: v1 and “V1 “.
Comment by Jonathan Lewis — March 5, 2013 @ 7:14 pm UTC Mar 5,2013
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 V1Regards
Martin
Comment by Martin Preiss — March 5, 2013 @ 3:53 pm UTC Mar 5,2013 |
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 UTC Mar 5,2013 |
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 UTC Mar 5,2013 |
[...] 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 UTC Mar 15,2013 |