Re: primary keys and dictionary overhead
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: "oracledbaquestions@xxxxxxxxx" <oracledbaquestions@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 23 Oct 2011 11:50:58 -0700 (PDT)
>> As far as UK constraints. We use unique indexes. We don't define them as
>> constraints. Functionally its the same thing, but they don't show up in the
>> DBA_CONSTRAINTS views. I am not sure how much of a difference that makes in
>> data dictionary activity. I have not checked yet.
The definition of a constraint *IS* a big deal. A constraint (to quite Ton
Kyte) is a semantic thing, it tells people (and the database) things. To quote
Tom on this topic, he says
the following :
if you have a unique constraint, a data integrity constraint that says "thou
are unique" - it only
makes sense to use.....
a UNIQUE CONSTRAINT
a unique index is NOT a unique constraint, it is a unique index. the optimizer
will use
constraints as well as available indexes and so on when deciding on what to do.
Assert all constraints - if we choose to use a unique index to enforce it,
great - but don't create
a unique index and tell me you have a constraint, you don't. You missed out.
From:
http://asktom.oracle.com/pls/asktom/f?p0:11:0::::P11_QUESTION_ID:8743855576462
Robert G. Freeman
Master Principal Consultant, Oracle Corporation, Oracle ACE
Author of various books on RMAN, New Features and this shorter signature line.
Blog: http://robertgfreeman.blogspot.com
Note: THIS EMAIL IS NOT AN OFFICIAL ORACLE SUPPORT COMMUNICATION. It is just
the opinion of one Oracle employee. I can be wrong, have been wrong in the past
and will be wrong in the future. If your problem is a critical production
problem, you should always contact Oracle support for assistance. Statements in
this email in no way represent Oracle Corporation or any subsidiaries and
reflect only the opinion of the author of this email.
________________________________
From: Dba DBA <oracledbaquestions@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, October 21, 2011 9:12 AM
Subject: Re: primary keys and dictionary overhead
Thank you for the responses. I have been busy and unable to catch up the
last few days. First off I know to test it myself. I know I can run a
tkprof. However, I don't think that is sufficient. I can't recreate the
volumes that we get in production. So I was looking for a little more
information to see if anyone has seen anything. There is a limit to the
level of volume tests I can run. Has anyone written a paper or a
presentation on this?
As far as UK constraints. We use unique indexes. We don't define them as
constraints. Functionally its the same thing, but they don't show up in the
DBA_CONSTRAINTS views. I am not sure how much of a difference that makes in
data dictionary activity. I have not checked yet.
As far as the email about a few vendors who insisted doing this and then
didn't use bind variables. We use bind variables. James Morle was one of the
guys who worked on this database. One of the guys who is still here worked
with him and they both decided to pull the constraints and everything out.
No synonyms. Only grants to public. I think he wrote about some of his
experiences on another application back then in the Tales From the Oak Table
book. It might be his own book from the late 1990s. I can't remember. I was
still in college when they first built this database and had never even
heard of Oracle.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
