Re: Dumb Question regarding Indexes

From: Andreas Sheriff <spamcontrol_at_iion.com>
Date: Fri, 29 Jul 2005 23:08:41 -0700
Message-ID: <zREGe.27423$bp.18756@fed1read03>

"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/sqlplsq l.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)

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

RE:
> > 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

-- 

Andreas Sheriff
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer
----
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding, if you don't eat your meat?"

DO NOT REPLY TO THIS EMAIL
Reply only to the group.

Received on Sat Jul 30 2005 - 01:08:41 CDT