|
|
|
|
|
|
Re: Composite Index [message #438071 is a reply to message #438040] |
Thu, 07 January 2010 01:00   |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Quote:
Composite index means the index created manaully by the user called composite.
What if we create unique constraint on 3 columns?
SQL> create table uk_demo2(
id number constraint uk_demo2pk primary key
, deptno number
, name varchar2(30)
, constraint uk_demo2uk unique ( deptno, name )
);
Database SQL Reference
Regards,
Ved
[Updated on: Thu, 07 January 2010 01:05] Report message to a moderator
|
|
|
|
Re: Composite Index [message #438078 is a reply to message #438071] |
Thu, 07 January 2010 01:17   |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Quote:
Composite index means the index created manaully by the user called composite.
How about this?
Sorry, was a typo in my previous post.
hr @orafaq> create table uk_demo3 (
2 id number not null primary key
3 , deptno number
4 , name varchar2(30)
5 ,constraint uk_demo2k unique ( deptno, name )
6 );
Table created.
Elapsed: 00:00:07.00
hr @orafaq> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='UK_DEMO2';
INDEX_NAME
------------------------------
SYS_C003112
UK_DEMO2UK
hr @orafaq> SELECT constraint_name, constraint_type, index_name, generated FROM user_constraints
2 WHERE table_name ='UK_DEMO2';
CONSTRAINT_NAME C INDEX_NAME GENERATED
------------------------------ - ------------------------------ --------------
SYS_C003111 C GENERATED NAME
SYS_C003112 P SYS_C003112 GENERATED NAME
UK_DEMO2UK U UK_DEMO2UK USER NAME
Elapsed: 00:00:00.00
What I meant to say is that your statement is not entirely true.
When we create unique constraint on composite columns Index is created.
[Updated on: Thu, 07 January 2010 01:25] Report message to a moderator
|
|
|
Re: Composite Index [message #438094 is a reply to message #438078] |
Thu, 07 January 2010 01:49   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
Quote:When we create unique constraint on composite columns Index is created.
I would like rephrase it as when unique constraint is created then index would be created if there is no existing index with same column order on the table that could be reused to enforce the constraint.
scott@orcl> create table dummy(a int, b int, c int);
Table created.
scott@orcl> create index dummy_idx on dummy(a,b);
Index created.
scott@orcl> alter table dummy add constraint dummy_uniq unique(a,b);
Table altered.
scott@orcl> select index_name, uniqueness from user_indexes where table_name = '
DUMMY';
INDEX_NAME UNIQUENES
------------------------------ ---------
DUMMY_IDX NONUNIQUE
scott@orcl> drop index dummy_idx;
drop index dummy_idx
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
scott@orcl> alter table dummy drop constraint dummy_uniq;
Table altered.
scott@orcl> alter table dummy add constraint dummy_uniq unique(b,c);
Table altered.
scott@orcl> select index_name, uniqueness from user_indexes where table_name = '
DUMMY';
INDEX_NAME UNIQUENES
------------------------------ ---------
DUMMY_IDX NONUNIQUE
DUMMY_UNIQ UNIQUE
2 rows selected.
scott@orcl>
|
|
|
|
|
Re: Composite Index [message #438192 is a reply to message #438040] |
Thu, 07 January 2010 08:05   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
srikkanthms86 wrote on Wed, 06 January 2010 23:38Hai
Composite index means the index created manaully by the user called composite.
Now THAT is funny.
|
|
|
|
Re: Composite Index [message #438261 is a reply to message #438192] |
Thu, 07 January 2010 12:57   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
joy_division wrote on Thu, 07 January 2010 09:05srikkanthms86 wrote on Wed, 06 January 2010 23:38Hai
Composite index means the index created manaully by the user called composite.
Now THAT is funny.
Why I stated it was funny, because with the broken grammar in this sentence, to me it means that a composite index is an index created by a user named "composite." or an index by the name of "composite" was created by a user
|
|
|
|