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.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Fri Jul 29 2005 - 20:16:07 CDT
