tag:blogger.com,1999:blog-15861274.post115600495671627037..comments2023-09-18T11:14:24.509+01:00Comments on Oracle WTF: CompetitionWilliam Robertsonhttp://www.blogger.com/profile/06976436975493102341noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-15861274.post-1170236372950314702007-01-31T09:39:00.000+00:002007-01-31T09:39:00.000+00:00If the requirement was to log the errors/exception...<I>If the requirement was to log the errors/exceptions without disrupting the main process, this makes sense.</I><BR/><BR/>Spoken like a man drafted on to the overnight roster.Adrianhttps://www.blogger.com/profile/00943497054353493755noreply@blogger.comtag:blogger.com,1999:blog-15861274.post-1170177646054055302007-01-30T17:20:00.000+00:002007-01-30T17:20:00.000+00:00> I disagree with> 5. WTF ignore all exceptions?He...<I>> I disagree with<BR/>> 5. WTF ignore all exceptions?</I><BR/><BR/>Here we go.<BR/><BR/>http://tkyte.blogspot.com/2006/08/ouch-that-hurts.htmlWilliam Robertsonhttps://www.blogger.com/profile/06976436975493102341noreply@blogger.comtag:blogger.com,1999:blog-15861274.post-1170176988218617222007-01-30T17:09:00.000+00:002007-01-30T17:09:00.000+00:00I disagree with 5. WTF ignore all exceptions?as we...I disagree with <BR/><BR/>5. WTF ignore all exceptions?<BR/><BR/>as we do not know the exact requirements for this proc. If the requirement was to log the errors/exceptions without disrupting the main process, this makes sense.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15861274.post-1156758126679027892006-08-28T10:42:00.000+01:002006-08-28T10:42:00.000+01:00I don't agree with"10. drop the surrogate key."Whe...I don't agree with<BR/><BR/>"10. drop the surrogate key."<BR/><BR/>When a lot of log entries are created within one second, the timestamp is not enough to tell the order of creation. And it is easier to tell a coworker "please have a look at log entries 124817..124922, looks odd" if you have such a key.Otto d.O.https://www.blogger.com/profile/08018027189719625909noreply@blogger.comtag:blogger.com,1999:blog-15861274.post-1156226791956899202006-08-22T07:06:00.000+01:002006-08-22T07:06:00.000+01:00Only perhaps without the NOLOGGING ;)Given that di...Only perhaps without the NOLOGGING ;)<BR/><BR/>Given that direct path INSERT uses blocks above the high water mark, I wonder what that would do to your space usage.William Robertsonhttps://www.blogger.com/profile/06976436975493102341noreply@blogger.comtag:blogger.com,1999:blog-15861274.post-1156200522721405832006-08-21T23:48:00.000+01:002006-08-21T23:48:00.000+01:00Yes I was counting APPEND and NOLOGGING as two sep...Yes I was counting APPEND and NOLOGGING as two separate WTFs, but not quite for the reason you mention. There is actually nothing in the INSERT statement to prevent logging. The developer seems to have misunderstood INSERT /*+ APPEND */ and the NOLOGGING attribute, and the database will just see a regular INSERT with a meaningless hint and table alias, which it will ignore.<BR/><BR/>I agree about using "p_" for things that aren't parameters though. I wonder whether the procedure once had an OUT parameter, and this was why it had the separate step to derive errorlogging_seq.NEXTVAL (they didn't know about RETURNING either).William Robertsonhttps://www.blogger.com/profile/06976436975493102341noreply@blogger.comtag:blogger.com,1999:blog-15861274.post-1156147027378635082006-08-21T08:57:00.000+01:002006-08-21T08:57:00.000+01:00Let's see:1. p_source and p_result could be %TYPEd...Let's see:<BR/>1. p_source and p_result could be %TYPEd on the underlying table columns.<BR/>2. <B>p_</B>primarykey should use some other prefix than p_ because it's not a parameter.<BR/>3. WTF select on sequence from dual? Move it to the insert statement.<BR/>4. WTF insert with append and nologging on a log table? So if the database crashes just after logging you've lost what you logged anyway. Besides for one record it's not going to yield the expected massive performance improvements :-)<BR/>5. WTF ignore all exceptions?<BR/>6. why is there a SUBSTR on p_result but not on p_source?<BR/>7. timestamp can be DEFAULT SYSDATE.<BR/>8. wherewasi, processed_count being filled with NULL: can drop these columns if they're never filled, or fill them; if they really are optional why include them in the insert statement? In general I use DEFAULTs and don't specify these columns and optional columns when inserting.<BR/>9. why not fill process_id with NULL if it's not specified, or is this some business logic that this is process "1"?<BR/>10. drop the surrogate key.<BR/><BR/>I got three WTF's... could be four if the use of APPEND and nologging are separate items.Colin 't Harthttps://www.blogger.com/profile/15623835285718803326noreply@blogger.com