Agree on both counts. The simple solution, which I believe is stated in the Oracle Concepts manuals is that all foreign keyed columns should also be indexed. A step sadly not taken in this particular case. Love it when you can make a simple suggestion & fix the problem!!
Dick Goulet ____________________Reply Separator____________________ Author: orantdba <[EMAIL PROTECTED]> Date: 1/22/2002 8:07 AM Hi Dennis, Agreed this was not the developers fault, it was the DBA's! I don't blame this on RI being handled by constraints, but on a DBA that doesn't understand the consequences, the resulting table level lock could have also been a problem :-). BTW, if RI had been handled via the application they would have had the same problem. John [EMAIL PROTECTED] wrote: >John, > > I have only seen one situation where referential integrity has caused a >problem that the developer could not prevent. That case involved a foreign key >with the 'on delete cascade' option turned on and the key column in the child >table was NOT indexed. OH, BTW the child table was well lets just say VERY >large (2 billion rows). > >Dick Goulet > >____________________Reply Separator____________________ >Subject: Re: Limits on referential integrity >Author: orantdba <[EMAIL PROTECTED]> >Date: 1/22/2002 5:40 AM > > >--------------020102080806060304030001 >Content-Type: text/plain; charset=us-ascii; format=flowed >Content-Transfer-Encoding: 7bit > >Hi Dennis, > >Just my opinion but I tried to follow these rules as a DBA. > >1. If the business rule can be implemented with pk, fk, unique or check >constraints I do it as such >2. If the business rule can be implemented as a trigger I code it as a >trigger >3. If none of the above, I implement as a stored procedure and try to >insure that every developer uses this procedure. > >Occasionally I have heard the "performance discussion" in regards to >constraints. In 5 years of consulting I have never had constraints be >THE problem. But if I was a developer that had written some of the >awful SQL I have seen, I might have tried [:-)] . Constraints do put a >premium on error checking by the application on inserts/updates. > >Hope this helps, >John > > > >[EMAIL PROTECTED] wrote: > >>Jared - I wasn't clear, but then again it is Monday. I have a team of >>inexperienced developers starting a big, new Java application. They have a >>good, experienced data model consultant helping them create the data model. >>They are eager to include referential integrity. So eager it has me a little >>worried. My question: "Is there too much of a good thing?". In Oracle 7, >>sometimes sites would remove RI to ensure good performance (we are starting >>this project on Oracle9i). Has anyone encountered problems with too many >>constraints? Any guidelines you use with developers? Thanks. >>Dennis Williams >>DBA >>Lifetouch, Inc. >>[EMAIL PROTECTED] >> >> >>-----Original Message----- >>Sent: Monday, January 21, 2002 4:16 PM >>To: Multiple recipients of list ORACLE-L >> >> >>I would be you lunch that what they are implementing in their >>code is not actually RI. They may be implementing code to >>ensure things get inserted in the right order, and that child rows >>have a parent. >> >>This is a very weak form of RI. Oracle is very good at implementing >>RI, and it is not dependent on an application. RI in the database >>is the route to choose unless there is some good reason not to. >> >>RI in the database will prevent orphaned data created through >>updates, deletes or even ( gasp! ) bugs in the app. >> >>Programmers tend to dislike RI in the database because it >>forces them to maintain data integrity in a transaction. This is >>not a bad thing, it just forces them to have a good understanding >>of their transactions. >> >>Point out to them that it is less code to write as well. :) >> >>Jared >> >> >> >> >> >> >> >>DENNIS WILLIAMS <[EMAIL PROTECTED]> >>Sent by: [EMAIL PROTECTED] >>01/21/02 01:35 PM >>Please respond to ORACLE-L >> >> >> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >> cc: >> Subject: Limits on referential integrity >> >> >>How much referential integrity should be implemented in Oracle? We are >>starting a large new Java project. Our current applications keep their >>referential integrity inside their own dictionary, so I haven't had to >>deal >>much with referential integrity recently. Can there be too much of a good >>thing? What guidelines do you tend to use? At this point the developers >>are >>designing the data model so they are busily linking all the little boxes. >>My >>attitude at this point is "implement what you've got and if there are >>performance problems we'll deal with them when they arise". Can anyone >>give >>me a better motto? >>Thanks. >>Dennis Williams >>DBA >>Lifetouch, Inc. >>[EMAIL PROTECTED] >> > > >--------------020102080806060304030001 >Content-Type: multipart/related; > boundary="------------InterScan_NT_MIME_Boundary" > > > >--------------InterScan_NT_MIME_Boundary >Content-Type: multipart/alternative; >boundary="------------020102080806060304030001" > boundary="------------070606010707000609020708" > >--------------070606010707000609020708 >Content-Type: text/html; charset=us-ascii >Content-Transfer-Encoding: 7bit > ><html> ><head> ></head> ><body> ><pre wrap="">Hi Dennis, > >Just my opinion but I tried to follow these rules as a DBA. > >1. If the business rule can be implemented with pk, fk, unique or check >constraints I do it as such >2. If the business rule can be implemented as a trigger I code it as a >trigger >3. If none of the above, I implement as a stored procedure and try to >insure that every developer uses this procedure. > >Occasionally I have heard the "performance discussion" in regards to >constraints. In 5 years of consulting I have never had constraints be >THE problem. But if I was a developer that had written some of the >awful SQL I have seen, I might have tried <img >src="chrome://editor/content/images/smile_n.gif" alt=":-)" class="moz-txt-smily" >height="19" width="19" align="Center">. Constraints do put a >premium on error checking by the application on inserts/updates. > >Hope this helps, >John</pre> ><br> ><br> ><a class="moz-txt-link-abbreviated" >href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a> wrote:<br> ><blockquote type="cite" cite="mid:md5%3A64756D6D79206D657373616765206964";> > <pre wrap="">Jared - I wasn't clear, but then again it is Monday. I have a >team of<br>inexperienced developers starting a big, new Java application. They >have a<br>good, experienced data model consultant helping them create the data >model.<br>They are eager to include referential integrity. So eager it has me a >little<br>worried. My question: "Is there too much of a good thing?". In Oracle >7,<br>sometimes sites would remove RI to ensure good performance (we are >starting<br>this project on Oracle9i). Has anyone encountered problems with too >many<br>constraints? Any guidelines you use with developers? Thanks.<br>Dennis >Williams<br>DBA<br>Lifetouch, Inc.<br><a class="moz-txt-link-abbreviated" >href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a><br><br><br>-- - >--Original Message-----<br>Sent: Monday, January 21, 2002 4:16 PM<br>To: >Multiple recipients of list ORACLE-L<br><br><br>I would be you lunch that what >they are implementing in their<br>code is not actually R >I. They may be implementing code to <br>ensure things get inserted in the right >order, and that child rows<br>have a parent.<br><br>This is a very weak form of >RI. Oracle is very good at implementing<br>RI, and it is not dependent on an >application. RI in the database<br>is the route to choose unless there is some >good reason not to.<br><br>RI in the database will prevent orphaned data created >through <br>updates, deletes or even ( gasp! ) bugs in the >app.<br><br>Programmers tend to dislike RI in the database because it<br>forces >them to maintain data integrity in a transaction. This is<br>not a bad thing, >it just forces them to have a good understanding<br>of their >transactions.<br><br>Point out to them that it is less code to write as well. >:)<br><br>Jared<br><br><br><br><br><br><br><br>DENNIS WILLIAMS <a >class="moz-txt-link-rfc2396E" >href="mailto:[EMAIL PROTECTED]";><[EMAIL PROTECTED]></a><br>Sen >t by: <a class="moz-txt-link-abbreviated" href="mailto:r! >o ! >! >[EMAIL PROTECTED]">[EMAIL PROTECTED]</a><br>01/21/02 01:35 PM<br>Please respond to >ORACLE-L<br><br> <br> To: Multiple recipients of list ORACLE-L <a >class="moz-txt-link-rfc2396E" >href="mailto:[EMAIL PROTECTED]";><[EMAIL PROTECTED]></a><br> >cc: <br> Subject: Limits on referential integrity<br><br><br>How >much referential integrity should be implemented in Oracle? We are<br>starting a >large new Java project. Our current applications keep their<br>referential >integrity inside their own dictionary, so I haven't had to <br>deal<br>much with >referential integrity recently. Can there be too much of a good<br>thing? What >guidelines do you tend to use? At this point the developers <br>are<br>designing >the data model so they are busily linking all the little boxes. ><br>My<br>attitude at this point is "implement what you've got and if there >are<br>performance problems we'll deal with them when they arise". Can anyone ><br>give<br>me a better motto? ><br>Thanks.<br>Dennis Williams<br>DBA<br>Lifetouch, Inc.<br><a >class="moz-txt-link-abbreviated" >href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a><br><br></pre> > </blockquote> > <br> > </body> > </html> > >--------------070606010707000609020708-- > >--------------020102080806060304030001-- > <html> <head> </head> <body> Hi Dennis,<br> <br> Agreed this was not the developers fault, it was the DBA's! I don't blame <br> this on RI being handled by constraints, but on a DBA that doesn't understand <br> the consequences, the resulting table level lock could have also been a problem :-). <br> BTW, if RI had been handled via the application they would have had the same <br> problem.<br> <br> John<br> <br> <a class="moz-txt-link-abbreviated" href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a> wrote:<br> <blockquote type="cite" cite="mid:md5%3A64756D6D79206D657373616765206964";> <pre wrap="">John,<br><br> I have only seen one situation where referential integrity has caused a<br>problem that the developer could not prevent. That case involved a foreign key<br>with the 'on delete cascade' option turned on and the key column in the child<br>table was NOT indexed. OH, BTW the child table was well lets just say VERY<br>large (2 billion rows).<br><br>Dick Goulet<br><br>____________________Reply Separator____________________<br>Subject: Re: Limits on referential integrity<br>Author: orantdba <a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";><[EMAIL PROTECTED]></a><br>Date: 1/22/2002 5:40 AM<br><br><br>--------------020102080806060304030001<br>Content-Type: text/plain; charset=us-ascii; format=flowed<br>Content-Transfer-Encoding: 7bit<br><br>Hi Dennis,<br><br>Just my opinion but I tried to follow these rules as a DBA. <br><br>1. If the business rule can be implemented with pk, fk, unique or check <br>constraint s I do it as such<br>2. If the business rule can be implemented as a trigger I code it as a <br>trigger<br>3. If none of the above, I implement as a stored procedure and try to <br>insure that every developer uses this procedure.<br><br>Occasionally I have heard the "performance discussion" in regards to <br>constraints. In 5 years of consulting I have never had constraints be <br>THE problem. But if I was a developer that had written some of the <br>awful SQL I have seen, I might have tried [:-)] . Constraints do put a <br>premium on error checking by the application on inserts/updates.<br><br>Hope this helps,<br>John<br><br><br><br><a class="moz-txt-link-abbreviated" href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a> wrote:<br><br></pre> <blockquote type="cite"> <pre wrap="">Jared - I wasn't clear, but then again it is Monday. I have a team of<br>inexperienced developers starting a big, new Java application. They have a<br>good, experienced data model consultant helping them create the data model.<br>They are eager to include referential integrity. So eager it has me a little<br>worried. My question: "Is there too much of a good thing?". In Oracle 7,<br>sometimes sites would remove RI to ensure good performance (we are starting<br>this project on Oracle9i). Has anyone encountered problems with too many<br>constraints? Any guidelines you use with developers? Thanks.<br>Dennis Williams<br>DBA<br>Lifetouch, Inc.<br><a class="moz-txt-link-abbreviated" href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a><br><br><br>--- --Original Message-----<br>Sent: Monday, January 21, 2002 4:16 PM<br>To: Multiple recipients of list ORACLE-L<br><br><br>I would be you lunch that what they are implementing in their<br>code is not actually RI. They may be implementing code to <br>ensure things get inserted in the right order, and that child rows<br>have a parent.<br><br>This is a very weak form of RI. Oracle is very good at implementing<br>RI, and it is not dependent on an application. RI in the database<br>is the route to choose unless there is some good reason not to.<br><br>RI in the database will prevent orphaned data created through <br>updates, deletes or even ( gasp! ) bugs in the app.<br><br>Programmers tend to dislike RI in the database because it<br>forces them to maintain data integrity in a transaction. This is<br>not a bad thing, it just forces them to have a good understanding<br>of their transactions.<br><br>Point out to them that it is less code to write as well. :)<br><br>Jared<br><br><br><br><br><br><br><br>DENNIS WILLIAMS <a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";><[EMAIL PROTECTED]></a><br>Sen t by: <a class="moz-txt-link-abbreviated" href="mailto! : [EMAIL PROTECTED]">[EMAIL PROTECTED]</a><br>01/21/02 01:35 PM<br>Please respond to ORACLE-L<br><br><br> To: Multiple recipients of list ORACLE-L <a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";><[EMAIL PROTECTED]></a><br> cc: <br> Subject: Limits on referential integrity<br><br><br>How much referential integrity should be implemented in Oracle? We are<br>starting a large new Java project. Our current applications keep their<br>referential integrity inside their own dictionary, so I haven't had to <br>deal<br>much with referential integrity recently. Can there be too much of a good<br>thing? What guidelines do you tend to use? At this point the developers <br>are<br>designing the data model so they are busily linking all the little boxes. <br>My<br>attitude at this point is "implement what you've got and if there are<br>performance problems we'll deal with them when they arise". Can anyone <br>give<br>me a better motto?! <b r>Thanks.<br>Dennis Williams<br>DBA<br>Lifetouch, Inc.<br><a class="moz-txt-link-abbreviated" href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a><br><br></pre> </blockquote> <pre wrap=""><!----><br><br>--------------020102080806060304030001<br>Content-Type: multipart/related;<br> boundary="------------InterScan_NT_MIME_Boundary"<br><br><br><br>--------------I nterScan_NT_MIME_Boundary<br>Content-Type: multipart/alternative;<br>boundary="------------020102080806060304030001"<br> boundary="------------070606010707000609020708"<br><br>--------------07060601070 7000609020708<br>Content-Type: text/html; charset=us-ascii<br>Content-Transfer-Encoding: 7bit<br><br><html><br><head><br></head><br><body><br>< ;pre wrap="">Hi Dennis,<br><br>Just my opinion but I tried to follow these rules as a DBA. <br><br>1. If the business rule can be implemented with pk, fk, unique or check <br>constraints I do it as such<br>2. If the business rule can be implemented as a trigger I code it as a <br>trigger<br>3. If none of the above, I implement as a stored procedure and try to <br>insure that every developer uses this procedure.<br! ><br>Occasionally I have heard the "performance discussion" in regards to <br>constraints. In 5 years of consulting I have never had constraints be <br>THE problem. But if I was a developer that had written some of the <br>awful SQL I have seen, I might have tried <img<br>src=<a class="moz-txt-link-rfc2396E" href="chrome://editor/content/images/smile_n.gif">"chrome://editor/content/image s/smile_n.gif"</a> alt=":-)" class="moz-txt-smily"<br>height="19" width="19" align="Center">. Constraints do put a <br>premium on error checking by the application on inserts/updates.<br><br>Hope this helps,<br>John</pre><br><br><br><br><br><a class="moz-txt-link-abbreviated"<br>href=<a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";>"mailto:[EMAIL PROTECTED]";</a>><a class="moz-txt-link-abbreviated" href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a></a> wrote:<br><br><blockquote type="cite" cite="mid:md5! %3 A64756D6D79206D657373616765206964"><br> <pre wrap="">Jared - I wasn't clear, but then again it is Monday. I have a<br>team of<br>inexperienced developers starting a big, new Java application. They<br>have a<br>good, experienced data model consultant helping them create the data<br>model.<br>They are eager to include referential integrity. So eager it has me a<br>little<br>worried. My question: "Is there too much of a good thing?". In Oracle<br>7,<br>sometimes sites would remove RI to ensure good performance (we are<br>starting<br>this project on Oracle9i). Has anyone encountered problems with too<br>many<br>constraints? Any guidelines you use with developers? Thanks.<br>Dennis<br>Williams<br>DBA<br>Lifetouch, Inc.<br><a class="moz-txt-link-abbreviated"<br>href=<a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";>"mailto:[EMAIL PROTECTED]";</a>><a class="moz-txt-link-abbre! v iated" href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a></a><b r><br><br>---<br>--Original Message-----<br>Sent: Monday, January 21, 2002 4:16 PM<br>To:<br>Multiple recipients of list ORACLE-L<br><br><br>I would be you lunch that what<br>they are implementing in their<br>code is not actually R<br>I. They may be implementing code to <br>ensure things get inserted in the right<br>order, and that child rows<br>have a parent.<br><br>This is a very weak form of<br>RI. Oracle is very good at implementing<br>RI, and it is not dependent on an<br>application. RI in the database<br>is the route to choose unless there is some<br>good reason not to.<br><br>RI in the database will prevent orphaned data created<br>through <br>updates, deletes or even ( gasp! ) bugs in the<br>app.<br><br>Programmers tend to dislike RI in the database because it&l! t;b r>forces<br>them to maintain data integrity in a transaction. This is<br>not a bad thing,<br>it just forces them to have a good understanding<br>of their<br>transactions.<br><br>Point out to them that it is less code to write as well.<br>:)<br><br>Jared<br><br><br><br>< br><br><br><br>DENNIS WILLIAMS <a<br>class="moz-txt-link-rfc2396E"<br>href=<a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";>"mailto:[EMAIL PROTECTED]";</a>><a
class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";><[EMAIL PROTECTED]></a></a& gt;<br>Sen<br>t by: <a class="moz-txt-link-abbreviated" href="<a class="moz-txt-link-freetext" href="mailto:r";>mailto:r</a>!<br>o !<br>!<br><a class="moz-txt-link-abbreviated" href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a>"><a class="moz-txt-link-abbreviated" href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a></a ><br>01/21/02 01:35 PM<br>Please respond to<br>ORACLE-L<br><br> <br> To: Multiple recipients of list ORACLE-L <a<br>class="moz-txt-link-rfc2396E"<br>href=<a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";>"mailto:[EMAIL PROTECTED]";</a>><a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";><[EMAIL PROTECTED]></a></a>< ;br> <br>cc: <br> Subject: Limits on referential integrity<br><br><br>How<br>much referential integrity should be implemented in Oracle? We are<br>starting a<br>large new Java project. Our current applications keep their<br>referential<br>integrity inside their own dictionary, so I haven't had to <br>deal<br>much with<br>referential integrity recently. Can there be too much of a good<br>thing? What<br>guidelines do you tend to use? At this point the developers <br>are<br>design ing<br>the data model so they are busily linking all the little boxes.<br><br>My<br>attitude at this point is "implement what you've got and if there<br>are<br>performance problems we'll deal with them when they arise". Can anyone<br><br>give<br>me a better motto? <br><br>Thanks.<br>Dennis Williams<br>DBA<br>Lifetouch, Inc.<br><a<br>class="moz-txt-link-abbreviated"<br>href=<a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]";>"mailto:[EMAIL PROTECTED]";</a>><a class="moz-txt-link-abbreviated" href="mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a></a><b r><br></pre><br> </blockquote><br> <br><br> </body><br> </html><br><br>--------------070606010707000609020708--<br><br>----------- ---020102080806060304030001--<br></pre> </blockquote> <br> </body> </html>
