Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Dumb Question regarding Indexes
"DA Morgan" <damorgan_at_psoug.org> wrote in message
news:1122686139.40952_at_yasure...
> Andreas Sheriff wrote:
> > You update or delete one row out of a 5,000,000,000 row table by primary > key and tell me the index didn't help. I'll be watching for your > benchmark.
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 Received on Fri Jul 29 2005 - 23:47:51 CDT