Re: Primary Key and Indexes, are they necessary?

jchen.com_at_gmail.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).
So enforcing data integrity in application. A nice way to create mess in db. DB validates data integrity faster, db validates data integrity more reliable. Using DB for just data waste isn't the best way to use it.
Probably a good starting point
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6091237036109 and one from these groups
http://groups.google.ca/group/comp.databases.theory/browse_frm/thread/609b28588c6c8a44/a449f62f434e26ea
>
> 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.

Everything uses some resources. Oracle as such is a big waste of resources. Why use Oracle. If they are soooooo worried about performance why use Oracle. MySQL would be faster.

> 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.

See http://www.jlcomp.demon.co.uk/myths.html look for Tables of only 1 or 2 blocks do not need indexing

And regarding your problems with reports. Firstly they should eliminate some common problems like using RBO, which seems possible for such "advanced people with 20 years of experience" and then tune each report individually and watch out again for common problems. Reports usually tend to use all data or most of the data in tables so probably RBO and index access paths are slowing down them. Probably full scans with hash joins would work better. Of course that's just a guess, but I'v experienced that in my life with a person with much more experience with Oracle than me ;)

Gints Plivna
http://www.gplivna.eu Received on Wed Jun 14 2006 - 04:29:25 CDT