Re: Primary Key and Indexes, are they necessary?

Yes we are experiencing performance issues, especially for report generation.

John Chen

HansF wrote:
> On Tue, 13 Jun 2006 17:11:42 -0700, jchen.com wrote:
>
> > We have an Oracle aplication (developed by a vendor) which has about
> > 150 tables.
> > However there is no primary keys on any tables. There are only about 30
> > non-unique
> > indexes (some of them are added after we requested).
> >
> > The vendor responsed that the reasons they do not add the primary key
> > and nessary
> > indexes are:
> > 1. The index will slow down the data entry
>
> Indexes do slow down data entry. However, in many cases the slow down may
> be totally imperceptible. The benefits of indexes may be sufficient to
> support creating them.
>
> Indexes should always be evaluated and have a reason for existing. There
> should be 'before and after' documented performance and impact analysis.
>
> In particular, the impact on data entry should be documented.
>
> But ... Unless you are dealing with high volume data entry (tens or
> hundreds of transactions per second) this may not matter.
>
> > 2. While adding primary keys can enhance performance in Oracle DB, it
> > can also degrade performance. For example, if a code table contains
> > fewer than 30 rows (not sure the exact ut off), it is just as fast if
> > not faster to do a full table scan for the information you are looking
> > up. Especially, if the DB is not fragmented. Doing an indexed or
> > Primary key look-up in a able of that size would cause jumping of the
> > disk read/write head, while a sequence read can complete much faster.
>
> There is no exact cut off. But it is true that IN SOME CASES it is better
> to not have an index for this reason.
>
> However, performance is not the only reason for having indexes. Avoiding
> duplicates and having bad data as a result are among other reasons I use
> for indexes.
>
> And having foreign key constraints without indexes can be a very quick
> way to introduce major performance issues.
>
> And some queries can be totally solved by an index and do not require
> looking at the table - meaning no jumping around.
>
> And Index Organized Tables may accomplish both indexing and table data,
> and in some cases are far superior to a regular table with or without
> indexes. (And in some cases are far inferior.)
>
> In Oracle 10g, the Cost Based Optimizer has been improved to the point
> that I would not be concerned about this issue as long as statistics are
> maintained properly.
>
> These are all reasons for having a properly trained DBA - a DBA is
> supposed to test and evaluate these kinds of things.
>
> > 3. They have several individuals with over 20 years of experience
> > working with Oracle meaning they have more experiences on Oracle
> > databases).
> >
> > Questions: Are these valid arguments? Should the primary key and
> > necessary indexes be added or not?
>
> There are many organizations that have people who have 20+ years of
> Oracle experience. In some cases, they have not learned anything since
> Oracle 5 (or Oracle 6 or Oracle7) and are making major mistakes because
> things have changed while they have not changed. This may or may not be
> true of your vendor. Although it is always a good idea to question a
> vendor with the attitude "I know better, so shut up, trust us and give us
> money"
>
> I know some people with 2 years Oracle experience who are better at Oracle
> than others with 10 years. And vice versa ...
>
>
>
> All that said - what is your real reason for asking. Are you
> experienceing performance problems?
>
> --
> /Hans ... mailto: Fuzzy_dot_GreyBreard_at_gmail_dot_com
> Top posting in newsgroups is a sure way to stop my replies!
> I reserve the right to change my mind on anything. Especially when
> confronted with facts that are better than the ones I use right now.
Received on Tue Jun 13 2006 - 20:40:44 CDT