index with multiple columns - ok when doing query on only one column?

Question

If I have an table

create table sv ( id integer, data text )

and an index:

create index myindex_idx on sv (id,text)

would this still be usefull if I did a query

select * from sv where id = 10

My reason for asking is that i'm looking through a set of tables with out any indexes, and seeing different combinations of select queries. Some uses just one column other has more than one. Do I need to have indexes for both sets or is an all-inclusive-index ok? I am adding the indexes for faster lookups than full table scans.

Example (based on the answer by Matt Huggins):

select * from table where col1 = 10
select * from table where col1 = 10 and col2=12
select * from table where col1 = 10 and col2=12 and col3 = 16

could all be covered by index table (co1l1,col2,col3) but

select * from table where col2=12

would need another index?

Answer 1

I assume id is primary key. There is no point in adding a primary key to the index, as this will always be unique. Adding something unique to something else will also be unique.

Add a unique index to text, if you really need it, otherwise just use id is uniqueness for the table.

If id is not your primary key, then you will not be guaranteed to get a unique result from your query.

Regarding your last example with lookup on col2, I think you could need another index. Indexes are not a cure-all solution for performance problems though, sometimes your database design or your queries needs to be optimized, for instance rewritten into stored procedures (while I'm not totally sure Oracle has them, I'm sure there's an Oracle equivalent).

Answer 2

It should be useful since an index on (id, text) first indexes by id, then text respectively.

• If you query by id, this index will be used.
• If you query by id & text, this index will be used.
• If you query by text, this index will NOT be used.

Edit: when I say it's "useful", I mean it's useful in terms of query speed/optimization. As Sune Rievers pointed out, it will not mean you will get a unique record given just ID (unless you specify ID as unique in your table definition).

Answer 3

Oracle supports a number of ways of using an index, and you ought to start by understanding all of them so have a quick read here: http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14211/optimops.htm#sthref973

Your query select * from table where col2=12 could usefully leverage an index skip scan if the leading column is of very low cardinality, or a fast full index scan if it is not. These would probably be fine for running reports, however for an OLTP query it is likely that you would do better to create an index with col2 as the leading column.

Answer 4

If the driver behind your question is that you have a table with several columns and any combination of these columns may be used in a query, then you should look at BITMAP indexes.

Looking at your example:

select * from mytable where col1 = 10 and col2=12 and col3 = 16

You could create 3 bitmap indexes:

create bitmap index ix_mytable_col1 on mytable(col1);
create bitmap index ix_mytable_col2 on mytable(col2);
create bitmap index ix_mytable_col3 on mytable(col3);

These bitmap indexes have the great benefit that they can be combined as required.

So, each of the following queries would use one or more of the indexes:

select * from mytable where col1 = 10;

select * from mytable where col2 = 10 and col3 = 16;

select * from mytable where col3 = 16;

So, bitmap indexes may be an option for you. However, as David Aldridge pointed out, depending on your particular data set a single index on (col1,col2,col3) might be preferable. As ever, it depends. Take a look at your data, the likely queries against that data, and make sure your statistics are up to date.

Hope this helps.