Indexes in Oracle

Question

As always, I apologize if this is a stupid question (two questions, actually). I'm not a DBA, so I know very little about indexes. My questions are:

1. Is there any cutoff point (in terms of number of rows) at which an index would be pointless? For example, is there any benefit to an index on a lookup table with 10-20 rows?

2. I've read some things about covering indexes in Oracle, and the concept makes sense in that the data can be retrieved directly from the index and a trip to the table is unnecessary. How can I tell if an index is a covering index? Is this a value set when the index is created, or by default based on the rows that the index includes?

I hope this makes sense.

Answer 1

Richard Foote has a series of blog posts on indexes for small tables. The short answer is probably not (but the long answer is much more interesting).

A covering index is a general term for an index that contains all the columns that are part of either the SELECT list or the WHERE clause for a table. It is not a property of an index-- any index can be a covering index for some query. It is something that is specific to a query and to the indexes available to the optimizer.

Answer 2

1. Small tables can be created as an "indexed oriented table" in which case the table and the index are the same "object" in storage. I believe a primary key is required for an IOT. Before IOTs were available it was in many cases a judgement call. Certainly, if the index was almost as big or bigger than the table it's useless unless it's being used to enforce uniqueness.

   another Richard Foote link

2. You have to look at the query being run and compare it to the index. An index on (A, B) covers:

   select B from T where A = 3