02 December 2010

Winners of Q3 Playoffs

On 29 November, we held the long-delayed championship playoff for Q3 2010. A total of 62 players participated. You can view the quizzes for the playoff from the Past Quizzes page.

First of all, congratulations to our top-three ranked players:

1st Place: Niels Hecker of Germany, wins US$1000.
2nd Place: Peter Schmidt of Germany, wins US$500.
3rd Place: Elic of Belarus, wins Complete set of O'Reilly Media Oracle eBooks.

Players ranked 4 through 10 each win their choice an O'Reilly ebook. All players will receive a certificate of participation.

All players rankings are shown below. The information shown in each line is:

Ranking (Weighted Score): Name (country) - # quizzes completed) in (Time) - % correct

You may notice that Niels has a higher weighted score than Peter, even though they have the same % correct and Peter finished in less time. That's because the weighted score is calculated on a per-quiz basis, and is affected by the difficulty of the question.

Again, congratulations to everyone who made it to the playoffs and who play the PL/SQL Challenge with diligence and seriousness.

Warm regards,
Steven Feuerstein

01 (5342): Niels Hecker (Germany) - 10 quizzes in 1091 secs - 81.4%
02 (5324): Peter Schmidt (Germany) - 10 quizzes in 783 secs - 81.4%
03 (5023): Elic (Belarus) - 10 quizzes in 1131 secs - 79.1%
04 (5016): Richard Meyer (United States) - 10 quizzes in 1179 secs - 76.7%
05 (5015): Sean Stuber (United States) - 10 quizzes in 1081 secs - 76.7%
06 (4923): Jeff Kemp (Australia) - 10 quizzes in 1117 secs - 76.7%
07 (4906): Gary Myers (Australia) - 10 quizzes in 732 secs - 74.4%
08 (4901): Randy Gettman (United States) - 10 quizzes in 1181 secs - 74.4%
09 (4879): William Robertson (United Kingdom) - 10 quizzes in 919 secs - 74.4%
10 (4817): Toine van Beckhoven (Netherlands) - 10 quizzes in 1102 secs - 74.4%
11 (4767): Eigminas Dagys (Lithuania) - 10 quizzes in 1180 secs - 72.1%
12 (4737): Frank Schrader (Germany) - 9 quizzes in 1192 secs - 84.2%
13 (4700): Radoslav Golian (Slovakia) - 10 quizzes in 1003 secs - 72.1%
14 (4651): Oleg  Gorskin (Russia) - 10 quizzes in 1121 secs - 74.4%
15 (4588): Rob van Wijk (Netherlands) - 10 quizzes in 938 secs - 72.1%
16 (4539): Dalibor Kovac (Croatia) - 10 quizzes in 1185 secs - 72.1%
17 (4509): Tony Winn (Australia) - 9 quizzes in 1144 secs - 81.6%
18 (4491): emha (Slovakia) - 10 quizzes in 1164 secs - 69.8%
19 (4480): João Barreto (Portugal) - 10 quizzes in 749 secs - 69.8%
20 (4464): Jeroen Rutte (Netherlands) - 9 quizzes in 1186 secs - 78.9%
21 (4458): Jen Croy (United States) - 9 quizzes in 1131 secs - 78.9%
22 (4399): Nopparat Vanichrudee (Thailand) - 9 quizzes in 1008 secs - 76.3%
23 (4375): Michal Cvan (Slovakia) - 9 quizzes in 1060 secs - 73.7%
24 (4329): Alexey Pirogov (Russia) - 10 quizzes in 1173 secs - 69.8%
25 (4324): Riccardo Buttice' (Italy) - 10 quizzes in 961 secs - 67.4%
26 (4316): Justin Cave (United States) - 10 quizzes in 1104 secs - 69.8%
27 (4300): Dennis Klemme (Germany) - 10 quizzes in 910 secs - 69.8%
28 (4266): Filipe Silva (Portugal) - 9 quizzes in 1154 secs - 76.3%
29 (4201): Filip Nikšic (Croatia) - 10 quizzes in 1182 secs - 69.8%
30 (4174): Markus Zuser (Austria) - 8 quizzes in 888 secs - 82.4%
31 (4159): Scott Wesley (Australia) - 9 quizzes in 1099 secs - 76.3%
32 (4117): Robert Marz (Germany) - 10 quizzes in 1151 secs - 67.4%
33 (4038): Xavier Descamps (French Republic) - 10 quizzes in 1160 secs - 65.1%
34 (3923): Kim Berg Hansen (Denmark) - 10 quizzes in 815 secs - 62.8%
35 (3907): Pavel Zeman (Czech Republic) - 10 quizzes in 1151 secs - 65.1%
36 (3905): Davide Gislon (Italy) - 10 quizzes in 1190 secs - 60.5%
37 (3895): pinkal soni (India) - 10 quizzes in 1168 secs - 65.1%
38 (3824): Soumyakanta Das (India) - 10 quizzes in 1128 secs - 62.8%
39 (3823): Hrvoje Torbašinovic (Croatia) - 10 quizzes in 1189 secs - 60.5%
40 (3805): glenm (Australia) - 8 quizzes in 1096 secs - 76.5%
41 (3779): Pietro Toniolo (Italy) - 10 quizzes in 1161 secs - 60.5%
42 (3758): Javid Sch (Azerbaijan) - 10 quizzes in 1083 secs - 62.8%
43 (3750): al0 (Germany) - 7 quizzes in 829 secs - 88.9%
44 (3735): Chris Roderick (Switzerland) - 9 quizzes in 1131 secs - 71.1%
45 (3728): Tim Scott (United Kingdom) - 10 quizzes in 1161 secs - 65.1%
46 (3721): Yuriy Pedan (Ukraine) - 10 quizzes in 1185 secs - 62.8%
47 (3718): Pavel Mitrofanov (Russia) - 9 quizzes in 1199 secs - 73.7%
48 (3664): Piet van Zon (Belgium) - 9 quizzes in 1185 secs - 65.8%
49 (3623): Paul Sharples (United Kingdom) - 10 quizzes in 846 secs - 58.1%
50 (3615): Michael Meyers (United Kingdom) - 8 quizzes in 1133 secs - 79.4%
51 (3612): Sergey Porokh (Australia) - 9 quizzes in 1175 secs - 65.8%
52 (3611): Johan Martensson (Sweden) - 10 quizzes in 594 secs - 60.5%
53 (3601): Michael Haynes (United States) - 10 quizzes in 554 secs - 58.1%
54 (3544): dannyg64 (United States) - 9 quizzes in 1066 secs - 65.8%
55 (3480): Gunjan (India) - 9 quizzes in 958 secs - 65.8%
56 (3371): V Vandana Patel (India) - 10 quizzes in 1167 secs - 58.1%
57 (3365): james su (Canada) - 9 quizzes in 883 secs - 63.2%
58 (3282): Tony Scholefield (Australia) - 9 quizzes in 1167 secs - 63.2%
59 (3269): Christopher Beck (United States) - 8 quizzes in 1130 secs - 67.6%
60 (2795): John Seaman (New Zealand) - 7 quizzes in 1143 secs - 70.4%
61 (2417): Jennifer Schiltz (United States) - 7 quizzes in 1153 secs - 63%
62 (1898): Justis Durkee (United States) - 6 quizzes in 1086 secs - 60.9%

Ambiguities in 1 December quiz on emulating indexes in collections? (1741)

The 1 December quiz tested your knowledge of using associative arrays to emulate multiple indexes into a single collection of data (at least, that was the intention. Several players felt that there were ambiguities in the question. I will provide some of their comments below, but leave it to them and others to elaborate more fully in their responses to the blog. 1. "I thought that the "perform faster than repeated SQL queries" phrase was ambiguous. I would assume I could run several (ie repeated) SQL queries for specific rows in less time than it would take for returning the same rows through the package if we included the initialisation time which is querying the whole table and performing the memory allocation. However, if we took SQL query 2, I would expect the package call 2 to be quicker." 2. "While the 1st answer is simply incorrect (AFAIK, there is no such thing as TYPE ... IS INDEX ON...) and the 2nd is obviously ineffective when doing lookups by partname, there's the 3rd answer I'm unsure about. Its execution time is roughly the same for any number of rows but as for me you cannot determine, if is it faster then plain SQL or not without some actual testing on some real data." 3. "The today quiz is somewhat ambiguous - the problem is with "roughly the same". I do not know implementation details of INDEX BY table but may relatively safely assume that they are map-based (either hash or tree). For this reason their access time should be n*log(n). Not sure if it covered by "roughly"." 4. "There are extra assumptions made, that are not real. In a normal situation I would say that none of the procedures are safe to do. There are indexes on the database. That is fast enough. Don't create code that makes it complex to survive changes to database. Maintenance on systems costs more then the development. Optimizing speed could be done most of time by creating good indexes or partitioning the data. " 5. "the question stated "that the execution times for both functions will be (a) faster than repeated SQL queries of the plch_parts table and (b) roughly the same, regardless of the number of rows in the table?" Your explanation just showed that your solution works but does not *prove* a) or b), is does not even explain it by stating that using a collection is faster than an unidexed query on a relatively big table, especially when (unlimited memory) the whole table might be cached." 6. "I've been enjoying the daily quiz since it began, and this is the first time I've felt the need to question one of the answers! In the 1 December quiz, one of the answers related to looking up a key from one associative array, then using that key into the second array. At the time I agonised over whether the performance would be "roughly the same" as the function that did just one lookup. On most systems the times we are talking about would be small, but for very large arrays, and if the function is called very often, then two lookups instead of one could be significant to response time. So I decided that "twice as long" is not "roughly the same" and did not tick this choice as correct; this was marked as incorrect. Therefore I question whether the specification of this question using "roughly the same" is sufficiently clear to allow us to choose the right answer." OK, let's leave it at that. I will make a few comments and then open it up for discussion. First, it is true that I did not include in the verification code the scripts needed to prove my claims. I will try to find some time to do that over the next couple of days. But if one of you would have the time to do so, and post your code and results, that would be fantastic (either proving my claim or disproving it). Second, yes, certainly using language like "roughly the same" leaves room for interpretation and some ambiguity. But I hope and plan to prove with my performance scripts that it is a reasonable statement to make. Finally, regarding the comment of my assumptions not being "safe," yes, I agree. You cannot assume for your production applications that you have unlimited memory, for example. And I am glad you pointed this out. When crafting quizzes that must by their nature involve the smallest amount of code and complexity as possible (and this question had LOTS of code in it - too much, I fear), I must make some not-real-world assumptions. Cheers, SF

30 November 2010

29 November quiz: interesting reactions to a "tricky" quiz (1706)

The 29 November quiz, the first authored by Ken Holmslykke, one of my invaluable quiz reviewers elicited several interesting reactions. It was a tricky quiz, no doubt about that. Involving implicit conversions and comparisons of string values, it was a hard one to sort out. I received the following two observations from players: 1. This is not an objection to the quiz at all (I did get i right , this is more a slight concern about "question styles..." Three cheers for Ken (nice guy taking some of the load off Stevens shoulders. But his "question style" is somewhat different. The thing is, that the "topic" of the question mostly is what happens with the implicit conversions of numbers to strings. But the use of all_objects.object_type%TYPE in my opinion mostly serves to confuse the reader so he won't recognize what the question is really about. Had the subtype index_t been declared as VARCHAR2, the question would have been clearer, that the subject was to test the readers knowledge of how a string associative array works if you use numbers working with that array. So just a slight warning (mostly to Ken) not to try to be too much "clever" when formulating questions. PL/SQL Challenge is mostly about learning, which to me means that it should be reasonably clear what topic the questions are trying to test our knowledge about. This tricky "obscuring" of the question topic is not (IMHO) consistent with the normal "question style" of Steven. That said, I do believe it will in the long run be a good thing to have questions from different authors, who might have a different angle than Steven Just my 2 bits. 2. It's a first time when I really want to nominate this question as a "Question of Year" (or at least a Quarter). I choose a wrong option, but I don't regret. It took a while for me to explain this right option. The only concern I have is about NLS_SORT / NLS_COMP parameters. People will definitely raise this point up, because you even may create your own character set definition and specify a different sort order using those parameters. I hope it's hard to achieve it and and question will be left unmodified. Regarding #1, ironically, though Ken wrote the original quiz, I did some editing and I am the one who introduced the use of the subtype. Ken originally declared the type as VARCHAR2. So I must take the "blame" for that obfuscation. Regarding #2, we document in the assumptions a default installation of an Oracle instance with a specific character set. Is that not enough to cover the issue raised regarding NLS_SORT and NLS_COMP? We'd love to hear what you thought of the quiz.