Re: Dumb Question regarding Indexes

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 29 Jul 2005 23:12:53 -0700
Message-ID: <1122703945.863829@yasure>

Richard Foote wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1122686139.40952_at_yasure...
>

>>Andreas Sheriff wrote:
>>
>>>"DA Morgan" <damorgan_at_psoug.org> wrote in message 
>>>news:1122653765.484188_at_yasure...
>>>
>>>
>>>>Perm wrote:
>>>>
>>>>
>>>>>I am a bit confused about creating indexes. If I have a table with 5
>>>>>columns, do I create an index for each column, or create an index for
>>>>>all columns in the same index, or what??
>>>>>
>>>>>I have noticed some applications create multiple indexes for a certain
>>>>>table, each containing 1 or more columns from the table, and some
>>>>>indexes contain the same columns as others...
>>>>>
>>>>>Thx for any info.
>>>>>BP
>>>>
>>>><RANT>
>>>>You do no such thing.
>>>>
>>>>What you do is go to http://tahiti.oracle.com
>>>>Look up Indexes and read the concept books
>>>>
>>>>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.
>>>>
>>>>You have no Oracle version
>>>>You have no Explain Plan
>>>>You have no trace file
>>>>You have no WHERE clause
>>>>You have no basis to even discuss creating any index whatsoever
>>>>
>>>>The first question that should be on the table is are there already
>>>>indexes created by a primary key or unique constraint?
>>>>
>>>>You should seriously consider taking a beginning Oracle class.
>>>></RANT>
>>>>-- 
>>>>Daniel A. Morgan
>>>>http://www.psoug.org
>>>>damorgan_at_x.washington.edu
>>>>(replace x with u to respond)
>>>
>>>
>>><flame level="medium" target="Daniel A. Morgan">
>>>Indexes do not speed up DML 
>>>(http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/sqlplsql.htm#i18503), 
>>>generally.  They speed up SELECT statements, specifically.  Indexes 
>>>actually slow down UPDATE, DELETE, and INSERT statements because the 
>>>index also has to be updated, deleted, and inserted, as well as the data 
>>>in the base table, though using indexes, the RDBMS can locate faster 
>>>which data to update and delete.
>>>In general, indexes help locate data faster, and if the data you need 
>>>through a select statement is already in the index, then Oracle doesn't 
>>>bother to read the base table; It just uses the data from the index.
>>>
>>><focus target="Perm">
>>>Look up Index Organized Tables (IOT).  An IOT combines an index and a 
>>>table for a faster means of access to data.
>>>Clusters are also good for tables that are typically used in a join, and 
>>>materialized views recomputed data so access is faster still.
>>></focus>
>>></flame>
>>
>>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

Thanks for the correction. I did an appallingly bad job of saying what you said. Of course it is about data access.

I'd like to claim it was because of jet lag having just returned from Hawaii but the truth is I was just sloppy.

Again ... thanks.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Sat Jul 30 2005 - 01:12:53 CDT