"Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message

--

Indexes on foreign keys may be a good thing, but saying that all foreign
keys should be indexed can cause of lot indexes to be created that are never
used.

For example, assume that a division_code has only 3 unique values in an
sales_transaction table, and the division_code is a foreign key to the
division_table to insure that a valid division_code is always used. It is
very unlikely that an single-column index on division_code would be used
when accessing the sales_transaction table, but the overhead of maintaining
the index exists for each row inserted.

The index on the division_code foreign key could be useful if the parent
division_code table values were changed or deleted, but that is would likely
be a rare situation, and not a scenario that one would want to optimize at
the expense of inserting rows into the sales_transaction table.

I often find that by eliminating these kind of unnecessary indexes on
foreign keys (usually automatically generated by modeling tools) that I can
eliminate about half the indexes in a database, and not effect query
performance at all (but greatly improving insert performance).

Hi Daniel

I think your point however that "Indexes serve one and only one purpose in a
database unless they have been built as part of constraint creation such as
a PK or UC. That is to speed up a DML statement" is either totally incorrect
or is highly misleading at best.

Indexes serve the purpose of speeding up the *retrieval* of data and
although I agree they may benefit updates and deletes, they also rather
benefit select statements as well (which is clearly not a DML statement).
Also indexes potentially hurt insert performance which clearly is a DML
statement (unless the insert has a subquery of course in which case the
index may benefit the *retrieval* of data in relation to the subquery). I
therefore totally disagree with your above point that indexes only having
the one purpose of speeding up DML.

In answer to the OP's question of when to create indexes, it therefore
depends on whether such an index would actually benefit the *retrieval* of
data and so questions such as: are the columns frequently used in WHERE
conditions, what's the cardinality of the columns, could sorting operations
be avoided, etc. etc. etc. need to answered.

In relation to whether or not have separate indexes or concatenated indexes,
it then rather depends on the makeup of the WHERE conditions and the such
and so questions such as: are the columns usually referenced together in
where conditions, are some columns likely not be referenced or be unknown,
do you frequently access only an additional column or two in addition to the
columns in where conditions, etc. etc. etc. The advantages of having
separate indexes on columns is that they can be useful for a wider range of
where predicates but the disadvantages are that they may not be as efficient
as a corresponding concatenated index as it may require more index probes or
table lookups. The advantages of concatenated indexes are that it could be
more efficient than corresponding separate indexes and could potentially
negate table lookups altogether if the index columns are sufficient for all
the referenced columns of the statement but the disadvantages are that it's
somewhat reliant on the leading column being referenced (index skip scanning
may help but it's rare and generally a poor substitute).

So there's quite a lot to it all and IMHO, it's actually quite a reasonable
question for a newbie to ask.

Also if I may say, again IMHO, every time I have a peep back here, there
appears to be too much ranting going on and perhaps not enough friendly and
helpful advice being offered :(

Just my impression of course ...

Cheers

Richard

(http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/sqlplsq
l.htm#i18503),

Isn't that what I said?  Or haven't you been paying attention?

RE: