Re: Primary Key and Indexes, are they necessary?

> 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

Maybe, maybe not. In most cases, the index will not slow down the data entry. In rare cases, it will. Most of those rare cases occur when there are many indexes on the database receiving new rows of data.

> 2. While adding primary keys can enhance performance in Oracle DB, it
> can also degrade
> performance.

Duh...That's kind of like my weather man saying that tomorrow it may rain, or it may not.

> For example, if a code table contains fewer than 30
> rows (not sure the exact
> cut off), it is just as fast if not faster to do a full table scan
> for the information you are looking

True, but if you statistics are up to date, then the Optimizer will know that there are only 30 rows in the table and will perform a full table scan and ignore the index. The Optimizer is pretty intelligent these days and will take this type of case into account.

> up. Especially, if the DB is not fragmented.

Fragmentation has nothing to do with adverse performance. If your vendor is saying that fragmentation is killing your performance, then they are living in the dark ages.

 > Doing an indexed or
> Primary key look-up in a
> table of that size would cause jumping of the disk read/write head,
> while a sequence read
> can complete much faster.
> 3. They have several individuals with over 20 years of experience
> working with Oracle
> (meaning they have more experiences on Oracle databases).

It sounds like they are still working on old Oracle versions.

> Questions: Are these valid arguments? Should the primary key and
> necessary indexes
> be added or not?

Let's back up a bit.....

Primary keys serve one major purpose which your vendor seems to have totally forgotten about. PK's are a constraint which is used to enforce your business rules. The PK says that the attribute (or collection of attributes) uniquely identifies an instance of the real-world entity that the table is modeling. Without a PK, the table of EMPLOYEES would allow multiple employees to have the same employee ID. Without a PK, a table of INVOICES would allow multiple invoices to have the same invoice ID. Without a PK constraint, a table of DEPARTMENTS would be allowed to have the same DEPARTMENT_NAME...get the idea? PK constraints are designed to enforce a business rule, which has nothing to do with performance.

That being said, Oracle uses an index to help it enforce the PK constraint and the index for the PK constraint can be used to help query   performance.

Leaving out constraints (primary keys, foreign keys, check constraints, etc) is a sure-fire way to get data that violates your business rules. Constraints are good for your relational database. Absence of constraints is bad...very bad.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown