Re: Primary Key and Indexes, are they necessary?

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.