Home » RDBMS Server » Performance Tuning » which index is faster (oracle 10g)
which index is faster [message #391362] Wed, 11 March 2009 23:25 Go to next message
grpatwari
Messages: 257
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 #391364 is a reply to message #391362] Wed, 11 March 2009 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Which index will be faster?
Somewhat a nonsensical question.
Answer depends MULTIPLE factors.
What question is being asked & needs to be answered.
Distribution of data within (or not) indexed columns.
Any single answer provided, I can produce a case to refute it.
Re: which index is faster [message #391388 is a reply to message #391364] Thu, 12 March 2009 01:12 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
grpatwari
Messages: 257
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.
Re: which index is faster [message #391425 is a reply to message #391362] Thu, 12 March 2009 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
The only way to know for sure is to try it (on a test instance).
Re: which index is faster [message #391436 is a reply to message #391406] Thu, 12 March 2009 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think BlackSwan already answered to this:
Quote:
Answer depends MULTIPLE factors.
What question is being asked & needs to be answered.
Distribution of data within (or not) indexed columns.

Regards
Michel
Re: which index is faster [message #391962 is a reply to message #391362] Sun, 15 March 2009 03:55 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Just like BlackSwan wrote: it may help and may not,

however it will for sure hurt your DMLs (Insert/Update/Delete) operations and increase locking issues.

HTH
Re: which index is faster [message #392797 is a reply to message #391362] Thu, 19 March 2009 05:29 Go to previous message
jayanta.senapati
Messages: 1
Registered: March 2009
Junior Member
here, it depends which column in which way you
are using in your query. sometimes multiple index on a column
might not get used if the column is not there in your
query predicate!!!

Previous Topic: Is there any way to rebuilds indexes online in Oracle SE !!
Next Topic: show parameter session (merged)
Goto Forum:
  


Current Time: Sun Dec 04 13:07:44 CST 2016

Total time taken to generate the page: 0.25495 seconds