tag:blogger.com,1999:blog-8677649049588007585.post8332249946046452705..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Proposal for new weekly quiz on Database DesignSteven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger21125tag:blogger.com,1999:blog-8677649049588007585.post-3584649777123302632013-06-20T18:31:04.241+01:002013-06-20T18:31:04.241+01:00This is something I was waiting for since a long t...This is something I was waiting for since a long time! Thanks Steven and Chris.Anonymoushttps://www.blogger.com/profile/08768638927690522888noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-55574752380401313672013-06-20T14:36:01.400+01:002013-06-20T14:36:01.400+01:00Sounds good to me.Sounds good to me.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-1481719486300032262013-06-19T20:32:38.637+01:002013-06-19T20:32:38.637+01:00Siim Kask - I also take your point regarding corne...Siim Kask - I also take your point regarding corner cases. I think we'd need to be more rigorous in the quiz wording to ensure there's a definitive answer. I'd aim that the quizzes would be suitably clear-cut that the correct answers would be apparent even with some interpretation of the question. <br /><br />I agree with your conclusion re the difference a fully covering index makes. This does have to be traded off against the increased index maintenance for insert/update/deletion operations however!<br /><br />Kent - would love to get your input on this quiz; I've sent you a tweet to connect, if you could get in touch so we can discuss this that would be brilliant, thanks.Chris Saxonhttps://www.blogger.com/profile/09485645419920814104noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-83087655359394393712013-06-18T19:53:48.862+01:002013-06-18T19:53:48.862+01:00Hello All,
@SiimKask, just a short feedback to yo...Hello All,<br /><br />@SiimKask, just a short feedback to your post:<br /><br />Regarding optimization problems, you are right, there are not only "corner cases",<br />but even "classical" cases may become "corner cases" under some scenarios.<br /><br />The cost-based optimizer is becoming more and more clever along the versions,<br />but the many features added can sometimes conflict and produce unexpected results.<br /><br />However, if what is called "a small percentage" used to be entirely enough for an index range scan to be <br />always chosen by the rule optimizer, in the case of the cost-based optimizer things become different.<br /><br />If a table is really very big (much bigger than the 1M rows in the sample quiz), <br />then the optimizer may definitely happen to choose a full table scan even for a very small percentage of rows selected, to avoid performing an excessive number of single block IO-s.<br /><br />It all depends on the cost calculation results that the optimizer performs for that specific case (data),<br />and you cannot exactly "put the finger" and indicate at which percentage the "switch" between index and full table scan will happen, this may be very different from one table to another.<br /><br /><br />Thanks a lot & Best Regards,<br />Iudith<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-11602891954759243902013-06-18T16:16:30.904+01:002013-06-18T16:16:30.904+01:00Thanks, Kent. Yes, I think this quiz has lots of p...Thanks, Kent. Yes, I think this quiz has lots of potential....and once we get it going and you see the kinds of quizzes we are publishing, perhaps you will find it possible to do one or two. I will follow up with you (or Chris will) later...Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-13838357772098331402013-06-18T16:12:15.118+01:002013-06-18T16:12:15.118+01:00I love it! There is a huge need for this type of e...I love it! There is a huge need for this type of education. I have seen way too much bad design in recent years in the name of "agile" development. A good data model will actually make it easier to be agile and result in better data in the long run.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-1455695998520530052013-06-18T16:08:49.364+01:002013-06-18T16:08:49.364+01:00Guys I love this! There is definitely a need to en...Guys I love this! There is definitely a need to encourage developers (and some dbas) to learn more about normalization, what it is, and why a properly built data model can make your application more sustainable over the long term. <br /><br />I think the quiz is a great way to do this. Not sure I have time to build quiz questions (as someone suggested) but I should be able to review and provide some feedback.<br /><br />I have seen so much bad design in recent years it is hard to believe some of the systems ever went live. Hopefully this can change the trend.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-56409586631645784622013-06-18T11:20:43.906+01:002013-06-18T11:20:43.906+01:00I model based on my common sense. I would think an...I model based on my common sense. I would think and guess that it is reasonably "normal form", though I haven't much clue whether I'm doing 1st, 2nd or 3rd ;-)<br /><br />I would take this quiz as a method to start learning something about what true modellers talk about when they say "you should use 3rd normal form." Why is that good? What cases might there be for not stringently insisting on "always 3rd normal form" but sometimes let common sense say "nah, in this case there is a valid reason for not doing it by the book."<br /><br />So a quiz that shows us "practical" people when theory actually might help us - without the quiz becoming a university textbook class - that will be great ;-)Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-5319061701733781352013-06-18T08:20:06.077+01:002013-06-18T08:20:06.077+01:00Yes, I'd like to play such a quiz.
DaliborYes, I'd like to play such a quiz.<br />DaliborAnonymoushttps://www.blogger.com/profile/14632865570547252888noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-1322703375779649422013-06-18T07:43:38.853+01:002013-06-18T07:43:38.853+01:00Looking forward ... GREAT !!!Looking forward ... GREAT !!!puchtechttps://www.blogger.com/profile/03669738384487130501noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-83099485848900091622013-06-18T07:21:23.458+01:002013-06-18T07:21:23.458+01:00I vote for this quiz. However, we will be likely s...I vote for this quiz. However, we will be likely seeing some corner cases. For example the third case says "small percentage". I tried a case where 4 percent of the orders were NOT SHIPPED and the index was only on ORDER_STATUS column. Oracle still made the index range scan, but the logical IOs per query were:<br />11735 for full table scan<br />1035 for index range scan and table access<br /><br />Full table scan is or course multiblock reads, and index range scan is single block reads. This can make a difference, and also you have to consider that blocks can be buffered (and the query time reduces by a big margin), then the CPU time is always there for index scan + block fetching overhead.<br /><br />Q3 with index on (order_status, order_date, customer_id) made only 54 logical IOs.<br /><br />So, in conclusion, I am not a big fan of index scan + table access with big data.siimkaskhttps://www.blogger.com/profile/16786222546215308551noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-82206733015400971542013-06-18T06:09:18.240+01:002013-06-18T06:09:18.240+01:00Can't wait this quiz to start!Can't wait this quiz to start!Ravshan Abbasovhttps://www.blogger.com/profile/08461838801024354551noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-79869240902396672952013-06-18T05:04:58.350+01:002013-06-18T05:04:58.350+01:00I really like the idea. I'll ask ACED Kent Gra...I really like the idea. I'll ask ACED Kent Graziano if he would like to help with quiz creation/review.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-61273715656732054472013-06-18T03:19:35.499+01:002013-06-18T03:19:35.499+01:00Could be interesting subject to do on a regular ba...Could be interesting subject to do on a regular basis. Like most of people, I should have sleep during that course... but I use it anyway as this is common sense...<br /><br />So it will help to put some better definition on it!<br /><br />So I vote YES too!The clickmanhttps://www.blogger.com/profile/03657763922461066420noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-15759400370902948042013-06-17T20:20:05.364+01:002013-06-17T20:20:05.364+01:00Thanks everyone for the positive comments!
To add...Thanks everyone for the positive comments!<br /><br />To address some of the concerns:<br /><br />Kim: I take your point about having an available data modelling dictionary to make the terms clear. I'll have a look to see if I can find anything suitable - if anyone has suggestions please let me know. PS - your understanding is basically correct; it is a relationship between two (or more) columns that aren't part of the primary/unique(candidate) keys for a table.<br /><br />Iudith: I agree that the quiz should stay on actual modelling techniques and not be about the tool. I merely chose Oracle Data Modeler because it is a freely available tool which I'm familiar with.<br /><br />Personally I feel not enough people really understand what normalization is and why you should (or shouldn't) do this - many talk about (de)normalization when they really mean something else. This is part of why I want to include this topic in the quiz. I would aim to keep these in "real-world" contexts as above to avoid this becoming too theoretical though.<br /><br />If anyone has questions or comments they would like to make to me privately then please message me directly (using the challenge messaging or chris dot saxon at gmail dot com). If anything comes up that is applicable to a wider audience then I'll summarise points as comments on here.<br /><br />Thanks, ChrisChris Saxonhttps://www.blogger.com/profile/09485645419920814104noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-38122969997169080832013-06-17T19:16:58.415+01:002013-06-17T19:16:58.415+01:00I like the idea. I'll be looking forward to se...I like the idea. I'll be looking forward to seeing it come alive:-)Anonymoushttps://www.blogger.com/profile/06906092910454645333noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-62119418653417042082013-06-17T18:42:52.244+01:002013-06-17T18:42:52.244+01:00I agree 100% with what Kim said above ( right, a t...I agree 100% with what Kim said above ( right, a trivial kind of comment from my side ! ).<br /><br />Understanding data modeling is important, but, however, I think that we should be very careful<br />*NOT* to arrive at the point when a quiz will ask something like:<br />"Which button should be pressed (in a specific graphic Data Modeler tool) for accomplishing this or that ..."<br /><br /><br />If the quizzes are going to be about Data Modeling, aka focusing on the *final result*, which is<br />a correctly defined and optimally performing table, index, schema, application, then it is ok and most welcome.<br /><br />On the other hand, if answering a quiz would require close knowledge of some specific data modeling tool<br />(even of a single specific one out of -- I guess -- many of these ), then, for me, it would be less interesting.<br /><br />Also, following Kim's idea, I am not sure that knowing exactly how a design book defines "transitive dependency"<br />will make you a better designer ...<br /><br />I think that most developers (maybe excepting the truly beginners) are definitely able to design even entire applications <br />by applying a "common sense" regarding the tables structures and their relationships, without knowing anything at all about <br />the different Normal Forms ... this is how probably most of us have become Oracle developers.<br /><br />I remember a class organized many years ago in our company exactly about Data Design,<br />where the instructor spoke hours after hours exactly about this topic, and all the audience (composed of more or less<br />experienced developers) was almost falling asleep in the room ...<br /><br />And, then, everybody discovered that, since the very first day that he was designing tables,<br />these were always and automatically in the 3-rd normal form, without having known anything at all about such a concept :):)<br /><br /><br />In other words, facing a quiz like the 1st or the 2nd examples above, once you answer based on common sense only,<br />you will probably answer 100% correctly. <br />If you start to think about "book definitions", then you have more chances to be driven into the wrong direction.<br /><br />The 3-rd sample quiz (as you can probably easily guess :) ), is the kind that looks to me the most useful,<br />and this is the field where yes, indeed, there is a lot of room for mastering performance theory as well,<br />THE huge "daily butter and bread" issue that never ends ... <br /><br />Yes, indeed, when it comes to really understanding things also at a theoretical level, <br />I think that the "Design for Performance" is indeed THE topic which deserves the highest focus.<br /><br /><br />To summarize, as long as we will be able to avoid "slipping into Academism" or into "tools usage technicalities",<br />such a competition can indeed be very instructive.<br /><br /><br />All in all, a nice idea, chapeau to Chris for taking on himself this not small challenge :):)<br /><br /><br />And, for the exhausted old ones like me, maybe the good opportunity to finally also enjoy the <br />non-competitive playing variant.<br /><br /><br />Thanks a lot & Best Regards,<br />Iudith<br />( I am not Anonymous ... but for some reason I am not allowed to use my real profile :( :( )<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-38237159877913135302013-06-17T17:31:08.218+01:002013-06-17T17:31:08.218+01:00It would be nice to have such quiz. Only that quiz...It would be nice to have such quiz. Only that quizzes about ER, constraints and theory will not relate to Oracle, but quizzes about indexing strategy and physical design will be very Oracle dependent.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-89877969246108832192013-06-17T16:03:22.926+01:002013-06-17T16:03:22.926+01:00I will definitely take this quiz!I will definitely take this quiz!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-41937951831843304772013-06-17T14:35:49.321+01:002013-06-17T14:35:49.321+01:00I like this idea as it gives developers practice w...I like this idea as it gives developers practice with data modeling tools and design theory. I vote yes!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-38428985382263580802013-06-17T14:28:53.317+01:002013-06-17T14:28:53.317+01:00Looks good to me ;-)
One thing: I am not sure I k...Looks good to me ;-)<br /><br />One thing: I am not sure I know what "transitive dependency" is? (Probably because I haven't really got any experience with formal definitions of the different types of normal forms - I just model what I think is common sense ;-) I guess it is something like the dependency between DATE_OF_BIRTH and STAR_SIGN.<br /><br />I do not propose to add explanation of "transitive dependency" to the assumptions - then other people might like some of the other definitions more detailed explained, and suddenly the assumptions are a mile long.<br /><br />But perhaps assumptions or instructions page could contain a link to a "data modeling dictionary" or something to help new players to establish a little working knowledge of the terms and definitions used in data modeling?Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.com