is there anyway index on a is not redundant?

[beginner]

out database have a table test( a, b, c,d);

we have unique index on a,b
and have a nonunique index on a.

i think nonunique index on a is redundant
can somebody think of any reason that we need index on a
while we have index on (a,b).

Thanks

[prafful]

I see no need of having index on 'a' as in your unique index 'a' is a leading column, so if you filter on just 'a' your unique index will still be used.

Hope this helps.

[beginner]

what if index on a and b is pretty big,
and index on a is small?

if i want to query by using column a,
maybe index on a is faster than index on a and b?

am i right?

[pando]

Isn't this easy to compare? Ran the queries and compare the cost and the execution plan if you see only the composite index is used even the predicate is column a only then drop the extra index

btw with CBO the order of columns doesnt matter anymore

[rkhatri]

by book, the search on the composite index should also be fast, because it is btree search, it is not sequential.

There will be very less difference in the performance.

Thanks,
Rajesh

[chrisrlong]

Originally posted by beginner
what if index on a and b is pretty big,
and index on a is small?

if i want to query by using column a,
maybe index on a is faster than index on a and b?

am i right?

Yes, you are. It will take longer to load the a,b index because it is larger on disk. And if it has, say, 16 times as many rows in it, then it will take ~4 more reads per record as well.

In general, however, these minor savings do not justify the creation of an essentially redundant index. Using an index versus not using one can be a difference of several magnitude. The difference then between two indexes, as described, is maybe a few percent - generally not worth the wasted overhead of the redundant index. Also, the cache will now have to hold both indexes, reducing the hit ratio for each index individually. Therefore, the gains are usually offset at this point.

So, they better be wayyyy different size indexes and 'a' by itself better be selected a whole lot in order to justify the existence of the 'a' index, IMHO.

A stab in the dark as two how that came to be: Oracle has some issues when FKs are not indexed. Therefore, some people set the 'always index FKs' option in ErWin. Then, if column a is an FK, you will get an index automatically on that column. If a,b is the PK, then that index is also automatically created. You now have an essentially redundant index.

- Chris

[Edited by chrisrlong on 09-18-2002 at 08:01 PM]