Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Primary Key and Indexes, are they necessary?
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
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
cut 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
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).
Questions: Are these valid arguments? Should the primary key and necessary indexes
be added or not?
Thank you in advance.
John Chen
jochen_at_nygh.on.ca