Home » SQL & PL/SQL » SQL & PL/SQL » Composite Index
Composite Index [message #438007] Wed, 06 January 2010 13:46 Go to next message
deepakhota
Messages: 9
Registered: December 2009
Location: Bangalore
Junior Member
What is Composite index???
Re: Composite Index [message #438008 is a reply to message #438007] Wed, 06 January 2010 13:50 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Composite Index

Create index on table1(col1,col2);
Re: Composite Index [message #438009 is a reply to message #438007] Wed, 06 January 2010 13:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+Composite+index

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Composite Index [message #438013 is a reply to message #438007] Wed, 06 January 2010 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Database SQL Reference

Regards
Michel
Re: Composite Index [message #438040 is a reply to message #438007] Wed, 06 January 2010 22:38 Go to previous messageGo to next message
srikkanthms86
Messages: 6
Registered: January 2010
Location: chennai
Junior Member
Hai
Composite index means the index created manaully by the user called composite.
Re: Composite Index [message #438070 is a reply to message #438040] Thu, 07 January 2010 01:00 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
what ?

ARE You sure?

sriram Smile
Re: Composite Index [message #438071 is a reply to message #438040] Thu, 07 January 2010 01:00 Go to previous messageGo to next message
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 #438073 is a reply to message #438071] Thu, 07 January 2010 01:02 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
Choosing Composite Indexes

A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:

Better read this

sriram Smile
Re: Composite Index [message #438078 is a reply to message #438071] Thu, 07 January 2010 01:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #438105 is a reply to message #438007] Thu, 07 January 2010 02:54 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
/forum/fa/1620/0/ If I will go through this thread, then I would get confused and probably will forget the correct one.

regards,
Delna

[Updated on: Thu, 07 January 2010 02:55]

Report message to a moderator

Re: Composite Index [message #438124 is a reply to message #438105] Thu, 07 January 2010 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I concur.

Regards
Michel
Re: Composite Index [message #438192 is a reply to message #438040] Thu, 07 January 2010 08:05 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
srikkanthms86 wrote on Wed, 06 January 2010 23:38
Hai
Composite index means the index created manaully by the user called composite.


Now THAT is funny.
Re: Composite Index [message #438194 is a reply to message #438192] Thu, 07 January 2010 08:10 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
http://www.orafaq.com/forum/mv/msg/153778/438078/146068/#msg_438078
was a reply to srikkanthms86's post.

Composite Index created implicitely if unique contraint is created on composite columns.

[Updated on: Thu, 07 January 2010 08:20]

Report message to a moderator

Re: Composite Index [message #438261 is a reply to message #438192] Thu, 07 January 2010 12:57 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
joy_division wrote on Thu, 07 January 2010 09:05
srikkanthms86 wrote on Wed, 06 January 2010 23:38
Hai
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
Re: Composite Index [message #438264 is a reply to message #438261] Thu, 07 January 2010 13:08 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Smile sometimes it happens...the thing is that the entire statement was wrong.

Regards,
Ved

[Updated on: Thu, 07 January 2010 13:14]

Report message to a moderator

Previous Topic: How to identify the locks in oracle db objects? i dont have access to check the v$ objects..
Next Topic: Dynamic SQL + Cursor + Bind variable isn't working
Goto Forum:
  


Current Time: Sat Feb 15 01:15:28 CST 2025