Re: primary keys and dictionary overhead
Date: Sun, 23 Oct 2011 11:50:58 -0700 (PDT)
Message-ID: <1319395858.84762.YahooMailNeo_at_web113208.mail.gq1.yahoo.com>
>> 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_at_gmail.com> To: ORACLE-L <oracle-l_at_freelists.org> 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.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sun Oct 23 2011 - 13:50:58 CDT