Re: Primary Key and Indexes, are they necessary?
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- Date: 14 Jun 2006 02:29:25 -0700
jchen.com@xxxxxxxxx wrote:
We have an Oracle aplication (developed by a vendor) which has aboutSo enforcing data integrity in application. A nice way to create mess
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).
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
Everything uses some resources. Oracle as such is a big waste of
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.
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
.
- Follow-Ups:
- Re: Primary Key and Indexes, are they necessary?
- From: jchen . com
- Re: Primary Key and Indexes, are they necessary?
- References:
- Primary Key and Indexes, are they necessary?
- From: jchen . com
- Primary Key and Indexes, are they necessary?
- Prev by Date: Re: Primary Key and Indexes, are they necessary?
- Next by Date: Min length of DBMS_CRYPTO.encrypt output
- Previous by thread: Re: Primary Key and Indexes, are they necessary?
- Next by thread: Re: Primary Key and Indexes, are they necessary?
- Index(es):
Relevant Pages
|
