| which index is faster [message #391362] |
Wed, 11 March 2009 23:25  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I have one table and having 4 columns as Primary Key. So index will be created with those 4 columns.I have query with only one column out of these 4 columns. If I create one more index with this one column.
Which index will be faster?
For Example:
create table t1(id1,id2,id3,id4,id5,name);
primary key(id1,id2,id3,id4)
If I create a index on id1 column.
Which index is faster?
Please advice.
|
|
|
|
|
|
| Re: which index is faster [message #391388 is a reply to message #391364] |
Thu, 12 March 2009 01:12  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Oracle can use the leading columns of an index to scan, so an index on C1,C2,C3,C4 can service queries that use (C1), (C1 and C2), (C1, C2 and C3), or (C1, C2, C3 and C4).
It is usually redundant to have an index that is a leading subset of another index.
Having said that, if a concatenated PK/UK index contains really long strings and uses LOTS of space, then a leading subset index may be warranted.
Also, a BITMAP index on a leading subset of a b-tree index can be justified.
Neither of these cases are likely to apply to you, so unless you have some irrefutable evidence to the contrary, don't create another index.
Ross Leishman
|
|
|
|
| Re: which index is faster [message #391406 is a reply to message #391362] |
Thu, 12 March 2009 03:12 |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Thnq very much Ross.
Actually I have only four columns those
ID VARCHAR2(128) NOT NULL,
ID1 VARCHAR2(50) NOT NULL,
START_TIME DATE NOT NULL,
END_TIME DATE NOT NULL,
These four columns are in PK index. If I create a index on Id then is there any chance to decrease the performance or will it increase the performance?
Because I have most of the queries basing on ID only.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
