This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes Can you have 3 indexes in one table? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can you have 3 indexes in one table?" Watch "Can you have 3 indexes in one table?" New topic
Author

Can you have 3 indexes in one table?

Ivan Jouikov
Ranch Hand

Joined: Jul 22, 2003
Posts: 269
Hey!

This is more of an SQL question. Can I do the following:



???

The idea is that I want to sometimes search by name, and sometimes by value.

Will this create 3 separate trees for this table?
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

We can have 3 index on one table but I am not sure about query given by you would work or not .

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Besides, any Cons and Pros,
No. of indexes for a table can be equal to No. of columns in that table.
S Herod
Greenhorn

Joined: Apr 25, 2005
Posts: 10
You can have more indexes than columns..

Oracle allows you to have multi column indexes (indeed, in some situations, they are preferred). So, you could have an index for each column, and indexes for combo's of columns.

Whats the down side? More indexes means longer insert times, but frankly, this is far, far outweighted by the benefits.

Keep in mind, that table size is important, for small tables (and the def of small is open to interpretation , an index may have no affect, or make access ever so slightly slower.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by S Herod:
You can have more indexes than columns..


Yes we can have more index than column if we go for composite index.

but Ivan is creating one index per column and what adeel meant is that if he goes on same way creating one index per column then he can have as much indexes as there are columns in table.

Shailesh
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Originally posted by Shailesh Chandra:
Yes we can have more index than column if we go for composite index.

but Ivan is creating one index per column and what adeel meant is that if he goes on same way creating one index per column then he can have as much indexes as there are columns in table.


Shailesh, thanks for the support. But I think my sentence is still valid, because i wrote "can be".

But ofcourse I meant the same.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

I too had thought in S Herod's way but later I concluded to my previous post .

Shailesh
Neeraj Dheer
Ranch Hand

Joined: Mar 30, 2005
Posts: 225
In MS-SQL, there is the concept of 'clustered' and 'non-clustered' indices.

1. there can be only ONE clustered index per table. and is NORMALLY put on the primary key. This is the index according to which the data is physically sorted in the table. So this index should be created on the most searched-on column(s).

2. there can be ANY number of non-clustered indices. data is only logically sorted for this index and this is the index normally used on foreign key(s).

So, in your case, yes, you can have as many indices as you want, although putting the clustered index on the most searched on column(s) will be the most efficient.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can you have 3 indexes in one table?
 
Similar Threads
Resource annotaion. doubts
MySQL indexing - is it a tree?
Using Excel to generate test data for Sql tables
No metadata was found for type "class java.util.ArrayList". The class is not enhanced
Problems using GenerationType.IDENTITY for in-memory databases