For those of you checking for news on the PL/SQL Challenge site via the blog (and not the plsqlchallenge twitter account, which we encourage you to follow):
The quiz for 23 December is now being displayed properly.
Our apologies for the invalid image error and display.
All answers submitted prior to 13:45 were removed, so you can take the quiz again without simply guessing.
If we end up with 475+ answers for the day, I probably will not void the results.
But I doubt highly that this will happen, given it is the start of Christmas "week," so I expect (sigh) to void another day's results.
Ah well, life can't always be a bowl of cherries, whatever that means.
My apologies for letting down our players,
Steven Feuerstein
23 December 2013
20 December 2013
Improving Website Performance: An Update
I announced earlier this week that I was suspending all work on new features until we improved the performance on the site.
Since then we have done the following:
Many thanks for your patience!
Steven Feuerstein
Since then we have done the following:
- Reduced by .5 seconds each and every page load time.
- Optimized an LOV on the home page, resulting in faster load time.
- Optimized the Library query so that you will see quizzes much faster.
- Changed the Switch Quiz LOV on the Quiz Details page to improve load time on that page.
Many thanks for your patience!
Steven Feuerstein
16 December 2013
PL/SQL Challenge Website Now Available
Just in time for Tuesday!
Hurray!
Well, you know how you just sometimes have one of those days?
This was one of mine.
I am still analyzing the full extent of my stupidity and incompetence (and I fear the results may be classified).
But I can say this:
First of all, thank you, John Scott, for being available and so, well, non-judgmental, as you once again bailed me out.
Second, we now do hope and expect that the website will stay up and you can take your quizzes. I will look into extending what is now yesterday's quiz so it can be played today as well (an excellent idea urged by Iudith Mentzel).
My apologies for inconvenience and any mental anguish,
Steven Feuerstein
Hurray!
Well, you know how you just sometimes have one of those days?
This was one of mine.
I am still analyzing the full extent of my stupidity and incompetence (and I fear the results may be classified).
But I can say this:
First of all, thank you, John Scott, for being available and so, well, non-judgmental, as you once again bailed me out.
Second, we now do hope and expect that the website will stay up and you can take your quizzes. I will look into extending what is now yesterday's quiz so it can be played today as well (an excellent idea urged by Iudith Mentzel).
My apologies for inconvenience and any mental anguish,
Steven Feuerstein
Poor, Sad Website: Voiding Today's Quiz
At 9:45 AM Chicago time, the PL/SQL Challenge website became unresponsive. It looks like we were and are being hammered by bots. We have been unable to stop the onslaught and so after a few hours of on again, off again access, I must announce with great sadness that we will void the results for today's quiz.
This means they will not be included in rankings for the quarter.
Now, having said that, I plan - when the site and backend become available again - to see how easily we can extend the end date for today's quiz....to tomorrow (make it a "two day quiz").
If that is possible (it should be, but you know how it is with unique indexes and constraints and code), then anyone who couldn't get to the quiz today, can do it tomorrow and then I will not void the results.
In the meantime, I will go take a walk in 12 degree weather (crisp but clear).
ALSO: make sure to follow PLSQLChallenge @ twitter.com so you can receive announcements about site status.
This means they will not be included in rankings for the quarter.
Now, having said that, I plan - when the site and backend become available again - to see how easily we can extend the end date for today's quiz....to tomorrow (make it a "two day quiz").
If that is possible (it should be, but you know how it is with unique indexes and constraints and code), then anyone who couldn't get to the quiz today, can do it tomorrow and then I will not void the results.
In the meantime, I will go take a walk in 12 degree weather (crisp but clear).
ALSO: make sure to follow PLSQLChallenge @ twitter.com so you can receive announcements about site status.
05 December 2013
Daily Quizzes Will Continue in 2014
I am very pleased to announce that I have decided to continue the daily PL/SQL quiz in 2014.
There are two reasons for this change from my earlier plan to terminate the daily quiz.
First, I realized that I hadn't made it as easy as I should have to write and submit quizzes. I never really gave you, my dear players, much advice about how best to write a quiz, and the Submit Quiz wizard requires too much effort on your part.
Second, even with the suboptimal wizard available today, many more players have started writing quizzes, and pledge to do so in 2014.
So it would be mean-spirited to say the least to stop the daily quizzes at this point.
Bottom Line: daily quizzes will continue at least through the end of Q1 2014.
And very soon we will announce significant improvements to our quiz submission features, including templates, the QuickQuiz smart script, and more.
Many thanks for your interest, enthusiasm and devotion to your craft!
Steven Feuerstein
There are two reasons for this change from my earlier plan to terminate the daily quiz.
First, I realized that I hadn't made it as easy as I should have to write and submit quizzes. I never really gave you, my dear players, much advice about how best to write a quiz, and the Submit Quiz wizard requires too much effort on your part.
Second, even with the suboptimal wizard available today, many more players have started writing quizzes, and pledge to do so in 2014.
So it would be mean-spirited to say the least to stop the daily quizzes at this point.
Bottom Line: daily quizzes will continue at least through the end of Q1 2014.
And very soon we will announce significant improvements to our quiz submission features, including templates, the QuickQuiz smart script, and more.
Many thanks for your interest, enthusiasm and devotion to your craft!
Steven Feuerstein
11 November 2013
Results from the Q3 2013 PL/SQL Championship now available!
You will find below the rankings for the 2013-3 championship; the number next to the player's name is the number of times that player has participated in a championship.
Congratulations first and foremost to our top-ranked players:
1st Place: Michal Cvan of Slovakia
2nd Place: mentzel.iudith of Israel
3rd Place: Janis Baiza of Latvia
We do not see 100% correct on the championship very often. Michal took his time, answered with care...and walked away with the top prize! Iudith was not far off Michal's flawless accuracy, with just one incorrect choice. Janis took a somewhat different approach, cruising through the quizzes in just 14-1/2 minutes and still scoring quite a high level of correctness.
Congratulations to everyone who played in the championship. I hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes next week through the Practice feature.
Steven Feuerstein
Note: Below the table of results for this championship, you will find another list showing the championship history of each of these players.
Congratulations first and foremost to our top-ranked players:
1st Place: Michal Cvan of Slovakia
2nd Place: mentzel.iudith of Israel
3rd Place: Janis Baiza of Latvia
We do not see 100% correct on the championship very often. Michal took his time, answered with care...and walked away with the top prize! Iudith was not far off Michal's flawless accuracy, with just one incorrect choice. Janis took a somewhat different approach, cruising through the quizzes in just 14-1/2 minutes and still scoring quite a high level of correctness.
Congratulations to everyone who played in the championship. I hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes next week through the Practice feature.
Steven Feuerstein
Note: Below the table of results for this championship, you will find another list showing the championship history of each of these players.
Rank | Name | Country | Total Time | % Correct | Total Score |
---|---|---|---|---|---|
1 | Michal Cvan (10) | Slovakia | 31 mins 08 secs | 100% | 3127 |
2 | mentzel.iudith (12) | Israel | 26 mins 41 secs | 96% | 3066 |
3 | Janis Baiza (8) | Latvia | 14 mins 28 secs | 88% | 3011 |
4 | Jeroen Rutte (6) | Netherlands | 21 mins 05 secs | 88% | 2878 |
5 | Peter Schmidt (5) | Germany | 21 mins 56 secs | 88% | 2861 |
6 | Ivan Blanarik (7) | Slovakia | 24 mins 50 secs | 88% | 2803 |
7 | Ingimundur Gudmundsson (2) | Norway | 31 mins 38 secs | 88% | 2667 |
8 | _tiki_4_ (5) | Germany | 20 mins 13 secs | 80% | 2596 |
9 | Frank Schmitt (7) | Germany | 20 mins 43 secs | 80% | 2586 |
10 | Andrey Zaytsev (1) | Russia | 29 mins 03 secs | 84% | 2569 |
11 | Viacheslav Stepanov (11) | Russia | 30 mins 55 secs | 84% | 2532 |
12 | Frank Puechl (3) | Germany | 17 mins 37 secs | 76% | 2498 |
13 | Niels Hecker (13) | Germany | 10 mins 47 secs | 72% | 2484 |
14 | Pavel Vorontsov (1) | Russia | 16 mins 23 secs | 72% | 2372 |
15 | james su (7) | Canada | 20 mins 07 secs | 72% | 2298 |
16 | Tony Winn (4) | Australia | 23 mins 33 secs | 72% | 2229 |
17 | Siim Kask (12) | Estonia | 24 mins 36 secs | 72% | 2208 |
18 | Stelios Vlasopoulos (9) | Belgium | 30 mins 48 secs | 72% | 2084 |
19 | Denis Ivin (1) | Russia | 31 mins 44 secs | 72% | 2065 |
20 | Tobias Stark (3) | Germany | 32 mins 21 secs | 72% | 2053 |
21 | Zoltan Fulop (6) | Hungary | 31 mins 49 secs | 68% | 1914 |
22 | Giedrius Deveikis (4) | Lithuania | 11 mins 31 secs | 56% | 1870 |
23 | Hamid Talebian (1) | Netherlands | 34 mins 19 secs | 68% | 1864 |
24 | Thierry Poels (6) | Belgium | 25 mins 55 secs | 60% | 1732 |
25 | swart260 (6) | Netherlands | 18 mins 49 secs | 52% | 1574 |
26 | Rakesh Dadhich (4) | India | 16 mins 00 secs | 48% | 1480 |
27 | Oleksiy Varchyn (1) | Norway | 30 mins 19 secs | 40% | 894 |
28 | Rich Barnhart (1) | United States | 25 mins 12 secs | 36% | 846 |
Championship Performance History
After each name, the quarter in which he or she played, and the ranking in that championship.Name | History |
---|---|
Michal Cvan | Q3 2010:22nd, Q4 2010:25th, Q3 2011:21st, Q1 2012:11th, Q3 2012:14th, Q4 2012:14th, Q1 2013:14th, Q2 2013:11th, Q3 2013:1st |
mentzel.iudith | Q4 2010:4th, Q1 2011:17th, Q2 2011:23rd, Q3 2011:5th, Q4 2011:4th, Q1 2012:7th, Q2 2012:16th, Q3 2012:33rd, Q4 2012:4th, Q1 2013:2nd, Q2 2013:7th, Q3 2013:2nd |
Janis Baiza | Q2 2010:3rd, Q4 2010:7th, Q3 2011:9th, Q4 2011:1st, Q3 2012:23rd, Q1 2013:4th, Q2 2013:4th, Q3 2013:3rd |
Jeroen Rutte | Q3 2010:19th, Q3 2012:10th, Q4 2012:11th, Q1 2013:8th, Q2 2013:18th, Q3 2013:4th |
Peter Schmidt | Q3 2010:2nd, Q4 2010:14th, Q3 2012:27th, Q2 2013:5th, Q3 2013:5th |
Ivan Blanarik | Q1 2012:3rd, Q2 2012:12th, Q3 2012:16th, Q4 2012:24th, Q2 2013:6th, Q3 2013:6th |
Ingimundur Gudmundsson | Q1 2013:25th, Q3 2013:7th |
_tiki_4_ | Q4 2011:26th, Q1 2012:16th, Q2 2012:20th, Q4 2012:8th, Q3 2013:8th |
Frank Schmitt | Q4 2011:22nd, Q2 2012:2nd, Q3 2012:21st, Q4 2012:3rd, Q1 2013:13th, Q2 2013:1st, Q3 2013:9th |
Andrey Zaytsev | Q3 2013:10th |
Viacheslav Stepanov | Q1 2011:8th, Q2 2011:4th, Q3 2011:13th, Q4 2011:19th, Q1 2012:17th, Q2 2012:9th, Q3 2012:6th, Q4 2012:21st, Q1 2013:18th, Q2 2013:22nd, Q3 2013:11th |
Frank Puechl | Q3 2012:26th, Q1 2013:15th, Q3 2013:12th |
Niels Hecker | Q2 2010:2nd, Q3 2010:1st, Q4 2010:15th, Q1 2011:6th, Q3 2011:7th, Q4 2011:10th, Q1 2012:2nd, Q2 2012:3rd, Q3 2012:2nd, Q4 2012:7th, Q1 2013:6th, Q2 2013:3rd, Q3 2013:13th |
Pavel Vorontsov | Q3 2013:14th |
james su | Q3 2010:56th, Q2 2011:7th, Q3 2011:11th, Q4 2011:14th, Q2 2012:15th, Q1 2013:7th, Q3 2013:15th |
Tony Winn | Q3 2010:16th, Q2 2013:17th, Q3 2013:16th |
Siim Kask | Q1 2011:28th, Q2 2011:6th, Q3 2011:10th, Q4 2011:3rd, Q1 2012:8th, Q2 2012:4th, Q3 2012:31st, Q4 2012:10th, Q1 2013:11th, Q2 2013:16th, Q3 2013:17th |
Stelios Vlasopoulos | Q4 2010:37th, Q4 2011:20th, Q1 2012:27th, Q2 2012:30th, Q3 2012:1st, Q4 2012:1st, Q1 2013:27th, Q2 2013:23rd, Q3 2013:18th |
Denis Ivin | Q3 2013:19th |
Tobias Stark | Q1 2012:32nd, Q3 2012:3rd, Q3 2013:20th |
Zoltan Fulop | Q1 2012:15th, Q2 2012:26th, Q3 2012:22nd, Q4 2012:17th, Q1 2013:22nd, Q3 2013:21st |
Giedrius Deveikis | Q2 2012:5th, Q3 2012:9th, Q3 2013:22nd |
Hamid Talebian | Q3 2013:23rd |
Thierry Poels | Q3 2011:22nd, Q1 2012:24th, Q1 2013:26th, Q3 2013:24th |
swart260 | Q2 2012:21st, Q3 2012:25th, Q4 2012:25th, Q1 2013:29th, Q2 2013:26th, Q3 2013:25th |
Rakesh Dadhich | Q2 2012:10th, Q1 2013:23rd, Q2 2013:28th, Q3 2013:26th |
Oleksiy Varchyn | Q3 2013:27th |
Rich Barnhart | Q3 2013:28th |
22 October 2013
Questions Arising from First Database Design Quiz
[This post is from Chris Saxon, manager of the weekly Database Design quiz.]
The database design quiz for the 12th-18th October covered implementation of a preferred address feature for customers. The first choice in the answers added a DEFAULT_ADDRESS_ID column to the customers table.
The database design quiz for the 12th-18th October covered implementation of a preferred address feature for customers. The first choice in the answers added a DEFAULT_ADDRESS_ID column to the customers table.
This answer was marked as correct,
however many players selected this choice as incorrect and a few objected on
the basis that no foreign key was defined (on the DEFAULT_ADDRESS_ID column).
The requirements in the question stated
that customers could select "at most one" of the addresses to be their
preferred address. By definition the PLCH_CUSTOMERS.DEFAULT_ADDRESS_ID meets
this, as it's only possible to store one default address per customer. In my
opinion, adding a FK to this column to an address table while a very desirable
action isn't strictly necessary to meet the "at most one"
requirement.
This also brings up a wider concern I
have with these quizzes regarding foreign keys. This week's quiz (18th-25th
October 2013) includes a CURRENCY_CD field. If I was designing "for
real", there would definitely be a FK on this column to a CURRENCIES
table. I excluded the FK and additional table from the question however to keep
it shorter and easier to read. There are a couple of other "missing"
FKs and tables from the question, which also aren't strictly necessary to
answer the question.
Based on the response to the default
address in the previous quiz, some players may feel these are necessary for
some of the choices to be correct.
So I would like to gather your thoughts
on how we should handle these issues.
The questions I have are:
- If a quiz includes a choice with fields that reference a key from another table, is it necessary to define a FK for the choice to be correct? (If the question states that we must do something, for example to use valid addresses, I certainly agree that a FK would be necessary)
- In general, should questions include as many FKs and tables as possible? Or should FKs only be listed where directly impact the correctness of some of the choices?
Please let us know your thoughts on this!
Also, remember that we will not publish
any comments that reference the details of this week's quiz! No spoiler alerts!
Feedback Needed: A new vision for the PL/SQL Challenge
We started the PL/SQL Challenge in April 2010 with a daily PL/SQL quiz. Over the years since, we have added weekly APEX, SQL and Logic quizzes, as well as monthly quizzes, quarterly championships and much, much more: over 1,200 to date!
Through it all we published five new quizzes on PL/SQL each week, come rain or shine, holiday or workday. And it should come as no big surprise, given that this whole site was my idea (Steven Feuerstein), that I have written more quizzes than everyone else combined: over 1,000. Wow.
I've gotten pretty good at it (productive, that is) and I actually do enjoy it.
Yet I now feel that the time has come to end the daily quizzes. I know that many of you may have strong feelings about this, so I would like to share (a) my reasons for feeling this way and (b) what I would like to do in place of the daily quiz.
I plan to put these changes into effect on 1 January 2014, so I very much want your feedback.
Here are my reasons:
1. It's very time consuming to write five quizzes per week, along with verification code and resources. It's also quite a job to review all these quizzes, and much of that effort has fallen at this point to a single, valiant player: Elic.
I have greatly enjoyed writing these quizzes. I've learned even more about PL/SQL in the process and, certainly, feel good about the impact of these quizzes on thousands of developers around the world.
But the bottom line is that by committing so much of my time to these quizzes, I have that much less time for other activities, both related to Oracle technologies and completely distinct from them.
And I must tell you that whether it is a mid-life crisis (I just celebrated my 55th birthday) or just one more human being coming to his or her senses, I feel more and more strongly with each passing day that I need to spend more time out in the natural world, away from computers and the Internet and plastic.
2. It's quite a burden on our reviewers to ensure a high quality for five quizzes each week. For a while, I had four-five PL/SQL developers checking my quizzes (well, usually 2-3 for any given quiz), but lately the main responsibility has fallen on our most excellent and diligent reviewer, Elic. He shows no signs of wear and tear, but it worries me....
3. Hmmm. I guess that's really about it. :-)
So what would I like to do instead? (with the PL/SQL Challenge, that is. Check out feuerthoughts.blogspot.com for occasional disclosures about what I plan to do with the rest of my life, overall. My views on that have been changing a lot in the past year.)
Here's what I am thinking about right now:
Offer THREE weekly quizzes on PL/SQL, as follows:
1. The PL/SQL Challenge: a weekly quiz authored by me that covers the gamut of PL/SQL features, as we have been going with the daily quiz - just once per week. See? I am not going to stop writing quizzes entirely!
2. The Community PL/SQL quiz: a weekly quiz that is authored by others (anyone but Steven Feuerstein). Currently we restrict how many questions authored by players can be used per quarter to just three. At least one player (Iudith Mentzel) has written lots of great questions that could be played and appreciated better with this new weekly quiz. And I hope that having a weekly quiz specifically showcasing the knowledge and creativity of others will encourage you to submit your own!
3. Deja Vu PL/SQL: a weekly quiz that features a question previously played in the daily PL/SQL quiz. Of course, if you spend lots of time practicing past quizzes, these will, over time, get ridiculously easy for you, but hey there's nothing wrong with that. You are internalizing programming patterns, leading to improved productivity and code quality in your own work. I am certain of it!
The PL/SQL Challenge quiz will be the primary quiz for rankings and qualifications for championships (more on that below). We would also rank the Community PL/SQL and Deja Vu PL/SQL quizzes, but they would not be used directly to qualify for championships. Instead, some players of these quizzes would qualify through a variety of special rules (play all quizzes in a quarter with N% correct or higher, that sort of thing).
Keep the Quarterly PL/SQL Championship
One could argue that there will no longer be enough quizzes in a quarter (12 weekly vs roughly 60 daily) to establish clearly who should participate in the championship. I was thinking about switching to a semi-annual (every 6 months) championship, but that complicates matters in our backend (support for a new frequency) and I don't really want to give players any fewer reasons to think about and visit the site.
We will probably adjust the rules around what it takes to qualify for the championship. And we will continue to play at a single time, to ensure the integrity of the competition.
Add a New Annual PL/SQL Championship
We are adding annual championships for SQL and Logic in 2014. We will do the same for PL/SQL, maybe starting in 2014 or wait until 2015.
Still sorting out the rules for participation. Maybe we will "loosen" things up and make it an "open" contest: anyone can play!Or have an open qualifier: take three quizzes (everyone at the same time), top 50 play in the annual championship.
Set Up More Tests
We recently rolled out the new Tests feature, which currently gives you the ability to take a past PL/SQL Quarterly Championship under the same conditions as the original players.
We will add more tests with specific feature area foci, such as a test on dynamic SQL or bulk processing. These will consist of multiple quizzes (not from those already played) that are designed to test how thoroughly you understand a particular area of functionality, and will include a mix of code-based and word quizzes.
Build the Community
I spend so much time writing quizzes, I don't have enough time to more fully develop the community side of the PL/SQL Challenge. I would like to start highlighting player accomplishments (Player of the Year, Reviewer of the Year, etc.), as well as provide better-organized and fully-realized utilities and reusable code.
And more generally I want to make the site less about/from me and more about featuring the knowledge and experience of PL/SQL experts all around the world!
Through it all we published five new quizzes on PL/SQL each week, come rain or shine, holiday or workday. And it should come as no big surprise, given that this whole site was my idea (Steven Feuerstein), that I have written more quizzes than everyone else combined: over 1,000. Wow.
I've gotten pretty good at it (productive, that is) and I actually do enjoy it.
Yet I now feel that the time has come to end the daily quizzes. I know that many of you may have strong feelings about this, so I would like to share (a) my reasons for feeling this way and (b) what I would like to do in place of the daily quiz.
I plan to put these changes into effect on 1 January 2014, so I very much want your feedback.
Here are my reasons:
1. It's very time consuming to write five quizzes per week, along with verification code and resources. It's also quite a job to review all these quizzes, and much of that effort has fallen at this point to a single, valiant player: Elic.
I have greatly enjoyed writing these quizzes. I've learned even more about PL/SQL in the process and, certainly, feel good about the impact of these quizzes on thousands of developers around the world.
But the bottom line is that by committing so much of my time to these quizzes, I have that much less time for other activities, both related to Oracle technologies and completely distinct from them.
And I must tell you that whether it is a mid-life crisis (I just celebrated my 55th birthday) or just one more human being coming to his or her senses, I feel more and more strongly with each passing day that I need to spend more time out in the natural world, away from computers and the Internet and plastic.
2. It's quite a burden on our reviewers to ensure a high quality for five quizzes each week. For a while, I had four-five PL/SQL developers checking my quizzes (well, usually 2-3 for any given quiz), but lately the main responsibility has fallen on our most excellent and diligent reviewer, Elic. He shows no signs of wear and tear, but it worries me....
3. Hmmm. I guess that's really about it. :-)
So what would I like to do instead? (with the PL/SQL Challenge, that is. Check out feuerthoughts.blogspot.com for occasional disclosures about what I plan to do with the rest of my life, overall. My views on that have been changing a lot in the past year.)
Here's what I am thinking about right now:
Offer THREE weekly quizzes on PL/SQL, as follows:
1. The PL/SQL Challenge: a weekly quiz authored by me that covers the gamut of PL/SQL features, as we have been going with the daily quiz - just once per week. See? I am not going to stop writing quizzes entirely!
2. The Community PL/SQL quiz: a weekly quiz that is authored by others (anyone but Steven Feuerstein). Currently we restrict how many questions authored by players can be used per quarter to just three. At least one player (Iudith Mentzel) has written lots of great questions that could be played and appreciated better with this new weekly quiz. And I hope that having a weekly quiz specifically showcasing the knowledge and creativity of others will encourage you to submit your own!
3. Deja Vu PL/SQL: a weekly quiz that features a question previously played in the daily PL/SQL quiz. Of course, if you spend lots of time practicing past quizzes, these will, over time, get ridiculously easy for you, but hey there's nothing wrong with that. You are internalizing programming patterns, leading to improved productivity and code quality in your own work. I am certain of it!
The PL/SQL Challenge quiz will be the primary quiz for rankings and qualifications for championships (more on that below). We would also rank the Community PL/SQL and Deja Vu PL/SQL quizzes, but they would not be used directly to qualify for championships. Instead, some players of these quizzes would qualify through a variety of special rules (play all quizzes in a quarter with N% correct or higher, that sort of thing).
Keep the Quarterly PL/SQL Championship
One could argue that there will no longer be enough quizzes in a quarter (12 weekly vs roughly 60 daily) to establish clearly who should participate in the championship. I was thinking about switching to a semi-annual (every 6 months) championship, but that complicates matters in our backend (support for a new frequency) and I don't really want to give players any fewer reasons to think about and visit the site.
We will probably adjust the rules around what it takes to qualify for the championship. And we will continue to play at a single time, to ensure the integrity of the competition.
Add a New Annual PL/SQL Championship
We are adding annual championships for SQL and Logic in 2014. We will do the same for PL/SQL, maybe starting in 2014 or wait until 2015.
Still sorting out the rules for participation. Maybe we will "loosen" things up and make it an "open" contest: anyone can play!Or have an open qualifier: take three quizzes (everyone at the same time), top 50 play in the annual championship.
Set Up More Tests
We recently rolled out the new Tests feature, which currently gives you the ability to take a past PL/SQL Quarterly Championship under the same conditions as the original players.
We will add more tests with specific feature area foci, such as a test on dynamic SQL or bulk processing. These will consist of multiple quizzes (not from those already played) that are designed to test how thoroughly you understand a particular area of functionality, and will include a mix of code-based and word quizzes.
Build the Community
I spend so much time writing quizzes, I don't have enough time to more fully develop the community side of the PL/SQL Challenge. I would like to start highlighting player accomplishments (Player of the Year, Reviewer of the Year, etc.), as well as provide better-organized and fully-realized utilities and reusable code.
And more generally I want to make the site less about/from me and more about featuring the knowledge and experience of PL/SQL experts all around the world!
09 October 2013
Participants in the Q3 2013 PL/SQL Championship
The following players will be invited to participate in the Q3 2013 championship playoff. The number in parentheses after their names are the number of playoffs in which they have already participated.
Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!
We currently plan to hold the championship on 7 November (again, at a single time world-wide), and are waiting for confirmation from players.
See the FAQ for an explanation of the three ways a player can qualify for the playoff.
Name | Rank | Qualification | Country |
---|---|---|---|
swart260 (5) | 1 | Top 25 | Netherlands |
Rakesh Dadhich (3) | 2 | Top 25 | India |
Oleksiy Varchyn (0) | 3 | Top 25 | Norway |
Stelios Vlasopoulos (8) | 4 | Top 25 | Belgium |
Ajaykumar Gupta (2) | 5 | Top 25 | Singapore |
Janis Baiza (7) | 6 | Top 25 | Latvia |
mentzel.iudith (11) | 7 | Top 25 | Israel |
Jerry Bull (9) | 8 | Top 25 | United States |
Niels Hecker (12) | 9 | Top 25 | Germany |
Frank Puechl (2) | 10 | Top 25 | Germany |
Siim Kask (11) | 11 | Top 25 | Estonia |
Viacheslav Stepanov (10) | 12 | Top 25 | Russia |
Justin Cave (9) | 13 | Top 25 | United States |
dannyg64 (2) | 14 | Top 25 | United States |
Peter Schmidt (4) | 15 | Top 25 | Germany |
Andrey Zaytsev (0) | 16 | Top 25 | Russia |
Hamid Talebian (0) | 17 | Top 25 | Netherlands |
Jeroen Rutte (5) | 18 | Top 25 | Netherlands |
Michal Cvan (9) | 19 | Top 25 | Slovakia |
Milibor Jovanovic (3) | 20 | Top 25 | Serbia |
Zoltan Fulop (5) | 21 | Top 25 | Hungary |
Pavel Vorontsov (0) | 22 | Top 25 | Russia |
Frank Schmitt (6) | 23 | Top 25 | Germany |
_tiki_4_ (4) | 24 | Top 25 | Germany |
Ivan Blanarik (6) | 25 | Top 25 | Slovakia |
Tony Winn (3) | 32 | Wildcard | Australia |
Giedrius Deveikis (3) | 38 | Correctness | Lithuania |
Denis Ivin (0) | 42 | Correctness | Russia |
Tobias Stark (2) | 56 | Correctness | Germany |
Martin_Scholz_Berlin_Berlin (0) | 57 | Correctness | Germany |
james su (6) | 84 | Wildcard | Canada |
Ingimundur Gudmundsson (1) | 87 | Correctness | Norway |
Thierry Poels (5) | 92 | Correctness | Belgium |
richb (0) | 133 | Correctness | United States |
Dan Kiser (4) | 223 | Correctness | United States |
Telmoc (0) | 224 | Correctness | Portugal |
Upendra Motamarri (0) | 316 | Correctness | India |
07 October 2013
How One Team Enjoys the PL/SQL Challenge
A very nice thing about my announcement over the weekend regarding cheaters is that it prompted several players to write to me about how much they have enjoyed the PL/SQL Challenge.
Here's the best story I got - it really made my day!
Here's the best story I got - it really made my day!
Thank you very much for this great project. I love the challenge and learn
(almost every day) new aspects of PL/SQL.
I thought you might like to hear how we "celebrate" the PL/SQL Challenge at our
company.
We are currently 6 developers
using PL/SQL and playing the PL/SQL Challenge.
We made our own trophies:
- Bronze for the leader in the previous week
- Silver for highest ranking player in the last month
- And gold for the best player in last quarter
It's always a fun and funny moment when the previous owner of the trophy presents it to the new winner.
How wonderful and entertaining! Thanks so much, Peter,for sharing this with me.
Do you have a fun story about the PL/SQL Challenge in your life and/or your job? Please let us know and we will post it on the blog!
Steven
05 October 2013
Sad Day for the PL/SQL Challenge: Cheaters Removed
Since the early days of the PL/SQL Challenge, we have been aware of the possibility of cheating by players. In response, we developed a set of tools to analyze player data to identify patterns of answer submissions that could reflect one or another form of cheating (such as a single person having multiple accounts, or multiple people colluding to achieve high scores).
We have in the past applied these tools to identify players who answer too quickly, and made adjustments to their scores accordingly. When we apply the adjustment, we notify the players that:
It is certainly quite possible that a person answering so quickly (and correctly) is cheating by taking the quiz under a different account, figuring out the correct answers, and then submitting the answer under their "real" account. It is also possible that they knew that subject very well or simply guessed. Rather than automatically reject such answers, we analyze player patterns over a longer period of time, usually no less than two weeks. We then make adjustments to the timings and ranks of those players who answer, cumulatively, in so short an amount of time that there is no way to distinguish their pattern of play from someone who would be cheating.
Today, it is my distinct non-privilege to announce that I have removed from the website three players who I believe have colluded over many quarters to take a whole bunch of the top prizes in the quarterly PL/SQL Championships. The players are (with their championship history under their names):
Frank Schrader
Q3 2010:12th, Q4 2010:30th, Q1 2011:1st, Q2 2011:5th, Q3 2011:1st, Q4 2011:1st, Q1 2012:13th, Q2 2012:3rd, Q3 2012:2nd, Q4 2012:4th, Q1 2013:2nd, Q2 2013:22nd
Dieter Kowalski
Q1 2012:21st, Q2 2012:8th, Q4 2012:16th, Q1 2013:24th, Q2 2013:16th
Konrad Schmidt (kowido)
Q1 2011:26th, Q2 2011:9th, Q3 2011:17th, Q4 2011:21st, Q1 2012:4th, Q2 2012:1st, Q3 2012:9th, Q4 2012:2nd, Q1 2013:1st, Q2 2013:15th
As you can see, two of our consistent champions dropped dramatically in ranking when in the Q2 2013 championship everyone played at the same time.
After I notified all three players of my determination and asked for a response, all three "people" immediately stopped playing. Only Frank even replied to my request and he protested his innocence. Unfortunately, it was not a convincing protest. The bottom line is that there is no way to distinguish their pattern of play from someone who would be cheating.
Frank and Konrad cumulatively won $2800 in prizes on the PL/SQL Challenge site. This is, of course, a source of extreme frustration for me....but what can you do?
Well, here's what we can do: all rankings have been recalculated after these players' removal. We therefore have in many cases new winners for the championships. I am sorry that we will not be able to issue prizes to these new winners, but at least you will be able to print off and display your certificate of achievement - and these new achievements become a part of your profile and history on the PL/SQL Challenge website.
And - do I really need to say this? - let this be a warning to other players who are cheating or contemplating such: we will continue to enhance and apply our tools to root our cheaters, remove them from the site, and announce their crimes to the world.
We may also make our tools available on the website itself, so that any players can run the analysis and see if they can uncover cases of potential cheating to report to us.
We have in the past applied these tools to identify players who answer too quickly, and made adjustments to their scores accordingly. When we apply the adjustment, we notify the players that:
It is certainly quite possible that a person answering so quickly (and correctly) is cheating by taking the quiz under a different account, figuring out the correct answers, and then submitting the answer under their "real" account. It is also possible that they knew that subject very well or simply guessed. Rather than automatically reject such answers, we analyze player patterns over a longer period of time, usually no less than two weeks. We then make adjustments to the timings and ranks of those players who answer, cumulatively, in so short an amount of time that there is no way to distinguish their pattern of play from someone who would be cheating.
Today, it is my distinct non-privilege to announce that I have removed from the website three players who I believe have colluded over many quarters to take a whole bunch of the top prizes in the quarterly PL/SQL Championships. The players are (with their championship history under their names):
Frank Schrader
Q3 2010:12th, Q4 2010:30th, Q1 2011:1st, Q2 2011:5th, Q3 2011:1st, Q4 2011:1st, Q1 2012:13th, Q2 2012:3rd, Q3 2012:2nd, Q4 2012:4th, Q1 2013:2nd, Q2 2013:22nd
Dieter Kowalski
Q1 2012:21st, Q2 2012:8th, Q4 2012:16th, Q1 2013:24th, Q2 2013:16th
Konrad Schmidt (kowido)
Q1 2011:26th, Q2 2011:9th, Q3 2011:17th, Q4 2011:21st, Q1 2012:4th, Q2 2012:1st, Q3 2012:9th, Q4 2012:2nd, Q1 2013:1st, Q2 2013:15th
As you can see, two of our consistent champions dropped dramatically in ranking when in the Q2 2013 championship everyone played at the same time.
After I notified all three players of my determination and asked for a response, all three "people" immediately stopped playing. Only Frank even replied to my request and he protested his innocence. Unfortunately, it was not a convincing protest. The bottom line is that there is no way to distinguish their pattern of play from someone who would be cheating.
Frank and Konrad cumulatively won $2800 in prizes on the PL/SQL Challenge site. This is, of course, a source of extreme frustration for me....but what can you do?
Well, here's what we can do: all rankings have been recalculated after these players' removal. We therefore have in many cases new winners for the championships. I am sorry that we will not be able to issue prizes to these new winners, but at least you will be able to print off and display your certificate of achievement - and these new achievements become a part of your profile and history on the PL/SQL Challenge website.
And - do I really need to say this? - let this be a warning to other players who are cheating or contemplating such: we will continue to enhance and apply our tools to root our cheaters, remove them from the site, and announce their crimes to the world.
We may also make our tools available on the website itself, so that any players can run the analysis and see if they can uncover cases of potential cheating to report to us.
New Weekly Quiz on Database Design
Since April 2010, the PL/SQL Challenge
has offered daily, weekly and monthly quizzes on Oracle PL/SQL, SQL,
deductive logic and Oracle Application Express. Oracle technologists
have submitted over 740,000 answers to these quizzes. Hundreds have
raved about how much they have learned, how much fun it is to compete
(or not, as you choose!)....
This website is one of my proudest achievements, but I sure would love to have more of my developer and DBA friends around the world using the site.
So I am excited to announce a new weekly quiz on Database Design, which will start on 5 October.
Relational databases form the backbone of thousands, if not millions, of applications around the globe. A key part of building these applications is designing and implementing the data structures they use. Proper table design can mean the difference between a scalable, high performing database that is a joy to query and an unscalable mess that makes your brain melt.
Given the importance of databases, understanding good data modelling techniques and physical implementation methods are essential skills for architects, database administrators and developers creating database applications.
Building on the SQL and PL/SQL quizzes already available at the PL/SQL Challenge, the weekly Database Design Quiz kicks off on 5 October to help you build these skills. The quiz will cover many areas of database design, from logical design all the way to physical database design, including topics such as:
The person who has made this quiz possible is Chris Saxon, a long-time player on the PL/SQL Challenge. Chris is a database technologist with 10 years experience designing and building Oracle database applications. He currently works as the Data Architect for the airline Flybe, a role which sees him creating the data structures for the flybe.com database and the company's enterprise data warehouse. He also runs the blog www.sqlfail.com, a project to explain database concepts and other topics of interest using just SQL and PL/SQL. While not thinking about data, his spare time is filled chasing after his daughter Isabelle, born in January this year.
I have, by the way, seen pictures of Isabelle. She is beautiful!
But Chris is so devoted to his craft and so interested in helping others that he is going to give up precious time with his daughter to create and manage the quizzes.
So play the quiz! Every week! Starting 5 October (this Saturday).
If you have not yet visited the PL/SQL Challenge, it's easy to register and entirely free. Check it out!
This website is one of my proudest achievements, but I sure would love to have more of my developer and DBA friends around the world using the site.
So I am excited to announce a new weekly quiz on Database Design, which will start on 5 October.
Relational databases form the backbone of thousands, if not millions, of applications around the globe. A key part of building these applications is designing and implementing the data structures they use. Proper table design can mean the difference between a scalable, high performing database that is a joy to query and an unscalable mess that makes your brain melt.
Given the importance of databases, understanding good data modelling techniques and physical implementation methods are essential skills for architects, database administrators and developers creating database applications.
Building on the SQL and PL/SQL quizzes already available at the PL/SQL Challenge, the weekly Database Design Quiz kicks off on 5 October to help you build these skills. The quiz will cover many areas of database design, from logical design all the way to physical database design, including topics such as:
- Normalization - ensuring you have high quality data
- Referential integrity - saving you the time and effort of writing your own constraints
- Indexing - enabling you to write fast and efficient queries
The person who has made this quiz possible is Chris Saxon, a long-time player on the PL/SQL Challenge. Chris is a database technologist with 10 years experience designing and building Oracle database applications. He currently works as the Data Architect for the airline Flybe, a role which sees him creating the data structures for the flybe.com database and the company's enterprise data warehouse. He also runs the blog www.sqlfail.com, a project to explain database concepts and other topics of interest using just SQL and PL/SQL. While not thinking about data, his spare time is filled chasing after his daughter Isabelle, born in January this year.
I have, by the way, seen pictures of Isabelle. She is beautiful!
But Chris is so devoted to his craft and so interested in helping others that he is going to give up precious time with his daughter to create and manage the quizzes.
So play the quiz! Every week! Starting 5 October (this Saturday).
If you have not yet visited the PL/SQL Challenge, it's easy to register and entirely free. Check it out!
06 September 2013
Q2 2013 PL/SQL Championship Results!
You will find below the rankings for the 2013-2 playoff; the number next to the player's name is the number of times that player has participated in a playoff.
Congratulations first and foremost to our top-ranked players:
1st Place: Frank Schmitt of Germany wins: Amazon.com US$250 Gift Card .
2nd Place: Chris Saxon of United Kingdom wins: Amazon.com US$175 Gift Card.
3rd Place: Niels Hecker of Germany wins: Amazon.com US$100 Gift Card.
Congratulations to everyone who played in the playoff. I hope you found it entertaining, challenging and educational. And for those who were not able to participate in the playoff, you can take the quizzes next week through the Practice feature.
Warm regards,
Steven Feuerstein
Note: Below the table of results for this playoff, you will find another list showing the playoff history of each of these players.
Congratulations first and foremost to our top-ranked players:
1st Place: Frank Schmitt of Germany wins: Amazon.com US$250 Gift Card .
2nd Place: Chris Saxon of United Kingdom wins: Amazon.com US$175 Gift Card.
3rd Place: Niels Hecker of Germany wins: Amazon.com US$100 Gift Card.
Congratulations to everyone who played in the playoff. I hope you found it entertaining, challenging and educational. And for those who were not able to participate in the playoff, you can take the quizzes next week through the Practice feature.
Warm regards,
Steven Feuerstein
Note: Below the table of results for this playoff, you will find another list showing the playoff history of each of these players.
Rank | Name | Country | Total Time | % Correct | Total Score |
---|---|---|---|---|---|
1 | Frank Schmitt (6) | Germany | 23 mins 28 secs | 92% | 2971 |
2 | Chris Saxon (6) | United Kingdom | 27 mins 56 secs | 92% | 2881 |
3 | Niels Hecker (12) | Germany | 29 mins 10 secs | 92% | 2857 |
4 | Janis Baiza (7) | Latvia | 22 mins 49 secs | 88% | 2834 |
5 | Peter Schmidt (4) | Germany | 30 mins 57 secs | 92% | 2821 |
6 | Ivan Blanarik (6) | Slovakia | 33 mins 42 secs | 92% | 2741 |
7 | mentzel.iudith (11) | Israel | 34 mins 38 secs | 92% | 2682 |
8 | Chad Lee (9) | United States | 32 mins 35 secs | 88% | 2663 |
9 | Alexey Ponomarenko (1) | Ukraine | 32 mins 45 secs | 88% | 2635 |
10 | Jason H (2) | United States | 30 mins 31 secs | 84% | 2520 |
11 | Michal Cvan (9) | Slovakia | 29 mins 01 secs | 80% | 2485 |
12 | Mike Pargeter (11) | United Kingdom | 23 mins 51 secs | 76% | 2473 |
13 | Randy Gettman (11) | United States | 32 mins 28 secs | 80% | 2451 |
14 | Vincent Malgrat (6) | French Republic | 34 mins 42 secs | 84% | 2431 |
15 | kowido (10) | Germany | 34 mins 27 secs | 84% | 2416 |
16 | Dieter Kowalski (5) | Germany | 34 mins 13 secs | 80% | 2356 |
17 | Jerry Bull (9) | United States | 31 mins 05 secs | 80% | 2353 |
18 | Siim Kask (11) | Estonia | 25 mins 03 secs | 76% | 2289 |
19 | Tony Winn (3) | Australia | 31 mins 34 secs | 80% | 2244 |
20 | Jeroen Rutte (5) | Netherlands | 32 mins 26 secs | 76% | 2241 |
21 | Ajaykumar Gupta (2) | Singapore | 33 mins 57 secs | 76% | 2211 |
22 | Frank Schrader (12) | Germany | 32 mins 21 secs | 76% | 2143 |
23 | Veera Marimuthu (2) | Singapore | 30 mins 45 secs | 76% | 2135 |
24 | Matthias Rogel (3) | Germany | 18 mins 02 secs | 60% | 1869 |
25 | Viacheslav Stepanov (10) | Russia | 31 mins 11 secs | 64% | 1751 |
26 | Stelios Vlasopoulos (8) | Belgium | 23 mins 17 secs | 56% | 1674 |
27 | Leszek Grudzień (1) | Poland | 25 mins 17 secs | 56% | 1599 |
28 | Pavel Noga (1) | Czech Republic | 34 mins 52 secs | 60% | 1593 |
29 | swart260 (5) | Netherlands | 33 mins 40 secs | 64% | 1582 |
30 | Vinu Garg (4) | India | 24 mins 03 secs | 56% | 1569 |
31 | Rakesh Dadhich (3) | India | 27 mins 45 secs | 56% | 1550 |
32 | Naresh Kumar (1) | India | 32 mins 00 secs | 60% | 1540 |
33 | Yuan Tschang (7) | United States | 34 mins 43 secs | 60% | 1531 |
34 | Livio (1) | Luxembourg | 34 mins 51 secs | 44% | 878 |
Playoff Performance History
After each name, the quarter in which he or she played, and the ranking in that playoff.Name | History |
---|---|
Frank Schmitt | Q4 2011:24th, Q2 2012:4th, Q3 2012:23rd, Q4 2012:5th, Q1 2013:15th, Q2 2013:1st |
Chris Saxon | Q4 2010:16th, Q2 2011:2nd, Q4 2011:8th, Q3 2012:22nd, Q4 2012:3rd, Q2 2013:2nd |
Niels Hecker | Q2 2010:2nd, Q3 2010:1st, Q4 2010:15th, Q1 2011:7th, Q3 2011:8th, Q4 2011:11th, Q1 2012:2nd, Q2 2012:5th, Q3 2012:3rd, Q4 2012:9th, Q1 2013:8th, Q2 2013:3rd |
Janis Baiza | Q2 2010:3rd, Q4 2010:7th, Q3 2011:10th, Q4 2011:2nd, Q3 2012:25th, Q1 2013:6th, Q2 2013:4th |
Peter Schmidt | Q3 2010:2nd, Q4 2010:14th, Q3 2012:29th, Q2 2013:5th |
Ivan Blanarik | Q1 2012:3rd, Q2 2012:15th, Q3 2012:18th, Q4 2012:27th, Q2 2013:6th |
mentzel.iudith | Q4 2010:4th, Q1 2011:18th, Q2 2011:25th, Q3 2011:6th, Q4 2011:5th, Q1 2012:8th, Q2 2012:19th, Q3 2012:35th, Q4 2012:6th, Q1 2013:4th, Q2 2013:7th |
Chad Lee | Q2 2011:28th, Q3 2011:19th, Q4 2011:13th, Q1 2012:1st, Q2 2012:26th, Q3 2012:30th, Q4 2012:25th, Q1 2013:22nd, Q2 2013:8th |
Alexey Ponomarenko | Q2 2013:9th |
Jason H | Q3 2012:19th, Q2 2013:10th |
Michal Cvan | Q3 2010:23rd, Q4 2010:25th, Q3 2011:23rd, Q1 2012:12th, Q3 2012:16th, Q4 2012:17th, Q1 2013:16th, Q2 2013:11th |
Mike Pargeter | Q4 2010:22nd, Q1 2011:16th, Q2 2011:10th, Q4 2011:6th, Q1 2012:6th, Q2 2012:20th, Q3 2012:6th, Q4 2012:23rd, Q2 2013:12th |
Randy Gettman | Q3 2010:8th, Q1 2011:27th, Q2 2011:12th, Q3 2011:4th, Q4 2011:12th, Q1 2012:20th, Q2 2012:22nd, Q3 2012:32nd, Q4 2012:14th, Q1 2013:5th, Q2 2013:13th |
Vincent Malgrat | Q4 2011:10th, Q1 2012:15th, Q2 2012:16th, Q4 2012:8th, Q1 2013:3rd, Q2 2013:14th |
kowido | Q1 2011:26th, Q2 2011:9th, Q3 2011:17th, Q4 2011:21st, Q1 2012:4th, Q2 2012:1st, Q3 2012:9th, Q4 2012:2nd, Q1 2013:1st, Q2 2013:15th |
Dieter Kowalski | Q1 2012:21st, Q2 2012:8th, Q4 2012:16th, Q1 2013:24th, Q2 2013:16th |
Jerry Bull | Q2 2011:34th, Q3 2011:9th, Q1 2012:14th, Q2 2012:14th, Q3 2012:15th, Q4 2012:18th, Q1 2013:11th, Q2 2013:17th |
Siim Kask | Q1 2011:30th, Q2 2011:7th, Q3 2011:11th, Q4 2011:4th, Q1 2012:9th, Q2 2012:6th, Q3 2012:33rd, Q4 2012:12th, Q1 2013:13th, Q2 2013:18th |
Tony Winn | Q3 2010:17th, Q2 2013:19th |
Jeroen Rutte | Q3 2010:20th, Q3 2012:12th, Q4 2012:13th, Q1 2013:10th, Q2 2013:20th |
Ajaykumar Gupta | Q1 2013:7th, Q2 2013:21st |
Frank Schrader | Q3 2010:12th, Q4 2010:30th, Q1 2011:1st, Q2 2011:5th, Q3 2011:1st, Q4 2011:1st, Q1 2012:13th, Q2 2012:3rd, Q3 2012:2nd, Q4 2012:4th, Q1 2013:2nd, Q2 2013:22nd |
Veera Marimuthu | Q1 2013:21st, Q2 2013:23rd |
Matthias Rogel | Q2 2013:24th |
Viacheslav Stepanov | Q1 2011:9th, Q2 2011:4th, Q3 2011:14th, Q4 2011:20th, Q1 2012:19th, Q2 2012:12th, Q3 2012:7th, Q4 2012:24th, Q1 2013:20th, Q2 2013:25th |
Stelios Vlasopoulos | Q4 2010:38th, Q4 2011:22nd, Q1 2012:30th, Q2 2012:33rd, Q3 2012:1st, Q4 2012:1st, Q1 2013:30th, Q2 2013:26th |
Leszek Grudzień | Q2 2013:27th |
Pavel Noga | Q2 2013:28th |
swart260 | Q2 2012:24th, Q3 2012:27th, Q4 2012:28th, Q1 2013:32nd, Q2 2013:29th |
Vinu Garg | Q2 2012:32nd, Q1 2013:31st, Q2 2013:30th |
Rakesh Dadhich | Q2 2012:13th, Q1 2013:26th, Q2 2013:31st |
Naresh Kumar | Q2 2013:32nd |
Yuan Tschang | Q2 2012:27th, Q3 2012:26th, Q4 2012:30th, Q2 2013:33rd |
Livio | Q2 2013:34th |
03 August 2013
On the Importance of Being Emailed
I don't know about you, but my In box functions as my to-do list. Not my only to-do list, mind you. I still scratch out lists on paper with an ancient technology known as a pen. But my In box plays a critical role in reminding me of upcoming items of interest.
I am not the only one, as I was reminded rather forcefully this week.
The PL/SQL Challenge reminds players (who have asked for reminders) about their daily and weekly quizzes. We also send out emails with results, once the quiz has closed.
Unfortunately, the email queue for our site (and perhaps others?) got backed up this week. Emails did not go out. And I did not pay sufficient attention to notice.
As a result, we received some painful emails like:
I got reminder for 2013-07-30 quiz at early morning of 2013-08-03. Looking at the email headers it is not my local SMTP server problem. All other emails from plsql challenge also is very late, so I missed Fridays quiz :(
I didn't get "Quiz Results"-mails on 31 July and 01 August and I also didn't get a "Reminder"-mail yesterday (or already some days before). So I forgot to play the quiz yesterday.
and worst of all:
I have been playing PL/SQL quizzes without having missed a single quiz since last 2 years. Since I am working, I rely on reminder emails, But I missed out the 1st August 2013 quiz. If you can please resolve the issue with reminder emails and consider my waiver for 1st August Daily Quiz, which I could not take.
Sadly, we cannot issue waivers or give you credit for a missed quiz. Hundreds of people did play on Thursday and Friday (though I wondered at the time why the count was lower than usual - I just figured it was due to summer holidays).
We will do our best to ensure that the emails keep on flowing, but remember:
I am not the only one, as I was reminded rather forcefully this week.
The PL/SQL Challenge reminds players (who have asked for reminders) about their daily and weekly quizzes. We also send out emails with results, once the quiz has closed.
Unfortunately, the email queue for our site (and perhaps others?) got backed up this week. Emails did not go out. And I did not pay sufficient attention to notice.
As a result, we received some painful emails like:
I got reminder for 2013-07-30 quiz at early morning of 2013-08-03. Looking at the email headers it is not my local SMTP server problem. All other emails from plsql challenge also is very late, so I missed Fridays quiz :(
I didn't get "Quiz Results"-mails on 31 July and 01 August and I also didn't get a "Reminder"-mail yesterday (or already some days before). So I forgot to play the quiz yesterday.
and worst of all:
I have been playing PL/SQL quizzes without having missed a single quiz since last 2 years. Since I am working, I rely on reminder emails, But I missed out the 1st August 2013 quiz. If you can please resolve the issue with reminder emails and consider my waiver for 1st August Daily Quiz, which I could not take.
Sadly, we cannot issue waivers or give you credit for a missed quiz. Hundreds of people did play on Thursday and Friday (though I wondered at the time why the count was lower than usual - I just figured it was due to summer holidays).
We will do our best to ensure that the emails keep on flowing, but remember:
If it is a weekday, there is a PL/SQL quiz for you to take!
22 July 2013
Q2 2013 Championship Rescheduled for 3 September
Due to conflicts on 3 August and then the joy of summer vacations and holidays, we will hold the Q2 2013 Championship on 3 September, at 15:00 UTC (still a single time, but now on Tuesday, not Saturday).
Players will be invited via email to sign up for the championship, confirming their participation.
Players will be invited via email to sign up for the championship, confirming their participation.
17 July 2013
Q2 2013 Championship will NOT be held on 3 August
It is tough finding a time in the summer to play the Championship! Enough players said "no" to 3 August to trigger the rule that says: "Find a new date!" So the championship will not be held on 3 August. We will poll our players and find a better date - and for this second attempt, we will shift back to a weekday, not the weekend.
15 July 2013
Q2 2013 PL/SQL Championship to be held on 3 August
The second quarter of 2013 is now history. And that means....it's time
for the next championship competition!
The following players will be invited to participate in the Q2 2013 championship. The number in parentheses after their names are the number of playoffs in which they have already participated.
See the FAQ for an explanation of the three ways a player can qualify for the playoff.
And congratulations to all listed below on their accomplishment and best of luck in the upcoming competition! We have six first-time championship players and many veterans of this fine competition.
This championship will be different from past events in two important ways:
Everyone will play at the same time: 3 August 16:00 UTC. Which means we are also holding the championship on a Saturday, the first time ever that we play on the weekend. Our three players from the Asia-Pacific region (who will be playing in the very early morning) have graciously accepted this additional challenge. Everyone else is in Europe and the United States, so it shouldn't be too painful. :-) We shall see how it goes!
The following players will be invited to participate in the Q2 2013 championship. The number in parentheses after their names are the number of playoffs in which they have already participated.
See the FAQ for an explanation of the three ways a player can qualify for the playoff.
And congratulations to all listed below on their accomplishment and best of luck in the upcoming competition! We have six first-time championship players and many veterans of this fine competition.
This championship will be different from past events in two important ways:
Everyone will play at the same time: 3 August 16:00 UTC. Which means we are also holding the championship on a Saturday, the first time ever that we play on the weekend. Our three players from the Asia-Pacific region (who will be playing in the very early morning) have graciously accepted this additional challenge. Everyone else is in Europe and the United States, so it shouldn't be too painful. :-) We shall see how it goes!
Name | Rank | Qualification | Country |
---|---|---|---|
Ajaykumar Gupta (1) | 1 | Top 25 | Singapore |
Rakesh Dadhich (2) | 2 | Top 25 | India |
Janis Baiza (6) | 3 | Top 25 | Latvia |
swart260 (4) | 4 | Top 25 | Netherlands |
mentzel.iudith (10) | 5 | Top 25 | Israel |
Stelios Vlasopoulos (7) | 6 | Top 25 | Belgium |
Chris Saxon (5) | 7 | Top 25 | United Kingdom |
Vinu Garg (3) | 8 | Top 25 | India |
Milibor Jovanovic (2) | 9 | Top 25 | Serbia |
Mike Pargeter (10) | 10 | Top 25 | United Kingdom |
Ivan Blanarik (5) | 11 | Top 25 | Slovakia |
Viacheslav Stepanov (9) | 12 | Top 25 | Russia |
Niels Hecker (11) | 13 | Top 25 | Germany |
Veera Marimuthu (1) | 14 | Top 25 | Singapore |
Vincent Malgrat (5) | 15 | Top 25 | French Republic |
Frank Schrader (11) | 16 | Top 25 | Germany |
kowido (9) | 17 | Top 25 | Germany |
Jeroen Rutte (4) | 18 | Top 25 | Netherlands |
Dieter Kowalski (4) | 19 | Top 25 | Germany |
Jerry Bull (8) | 20 | Top 25 | United States |
Siim Kask (10) | 21 | Top 25 | Estonia |
Jason H (1) | 22 | Top 25 | United States |
Leszek Grudzień (0) | 23 | Top 25 | Poland |
Tony Winn (2) | 24 | Top 25 | Australia |
Frank Schmitt (5) | 25 | Top 25 | Germany |
Peter Schmidt (3) | 31 | Correctness | Germany |
Joaquin Gonzalez (5) | 32 | Wildcard | Spain |
Chad Lee (8) | 33 | Correctness | United States |
Matthias Rogel (2) | 34 | Wildcard | Germany |
Naresh Kumar (0) | 35 | Wildcard | India |
Jan Soubusta (0) | 48 | Wildcard | Czech Republic |
Alexey Ponomarenko (0) | 77 | Wildcard | Ukraine |
Yuan Tschang (6) | 85 | Correctness | United States |
Randy Gettman (10) | 94 | Correctness | United States |
Pavel Noga (0) | 96 | Correctness | Czech Republic |
Michal Cvan (8) | 107 | Correctness | Slovakia |
Livio (0) | 332 | Correctness | Luxembourg |
30 June 2013
Are "abandoned" quizzes included in rankings analysis?
JasonC asks this question:
Occasionally, I'll start a quiz, and it's about a subject I have no
knowledge of at all - I can't even make an educated guess. So I just
close the window and do something else.
So then I wondered if my non-start gets logged somewhere as null points, and a VERY long time ?
The reason for this is that I notice I nearly always complete the quiz quicker than the AVERAGE time, but slower than the MEAN time, which suggests to me that a few excessively long times are skewing the average - maybe these could be people failing to complete the quiz? None of this really matters: I've no complaints about my scores (well, they're lower than I would like, but that's another story!) - I'm just curious.
Excellent question! I thought I knew the answer but decided to look at the code, anyway. The code always tells the truth. :-)
Here's what the code tells me:
Look, a comment! I proudly proclaim in my trainings that my code is self-documenting, requiring no comments. But I am glad I broke my pledge here.
So then I wondered if my non-start gets logged somewhere as null points, and a VERY long time ?
The reason for this is that I notice I nearly always complete the quiz quicker than the AVERAGE time, but slower than the MEAN time, which suggests to me that a few excessively long times are skewing the average - maybe these could be people failing to complete the quiz? None of this really matters: I've no complaints about my scores (well, they're lower than I would like, but that's another story!) - I'm just curious.
Excellent question! I thought I knew the answer but decided to look at the code, anyway. The code always tells the truth. :-)
Here's what the code tells me:
PROCEDURE submit_saved_answers (comp_event_id_in IN INTEGER)
IS
l_comp_event qdb_comp_events%ROWTYPE
:= one_comp_event (comp_event_id_in);
l_competition qdb_competitions%ROWTYPE;
l_answer_closed BOOLEAN DEFAULT FALSE;
BEGIN
/* Assign an end date to all answers for which there is at
least one saved answer. */
FOR rec
IN (SELECT DISTINCT eva.compev_answer_id, eva.user_id
FROM qdb_compev_answers eva, qdb_quiz_results qr
WHERE eva.compev_answer_id =
qr.compev_answer_id
AND eva.comp_event_id = comp_event_id_in
AND eva.ended_on IS NULL)
LOOP
UPDATE qdb_compev_answers eva
SET ended_on =
qdb_player_mgr.user_end_time (
comp_event_id_in,
rec.user_id)
WHERE eva.compev_answer_id = rec.compev_answer_id;
END LOOP;
END submit_saved_answers;
Look, a comment! I proudly proclaim in my trainings that my code is self-documenting, requiring no comments. But I am glad I broke my pledge here.
Bottom line: in the current scheme of things at the PL/SQL Challenge, we will not automatically set an end time for your answer when the quiz consists of a single question. For multiple-question competitions like the playoff, we will automatically set an end time if you answered at least one of the questions.
There can still, however, be some very long answer times that will skew the average. I have tried to isolate those in at least some of our calculations, but may not have caught them all.
25 June 2013
Some Changes in Championship Rules (and more)
We have decided to institute a few changes in the rules and format for the quarterly championship, as well as rules for winners of other prizes.
First, regarding winners of prizes (weekly, monthly, etc.): while you can choose to remain anonymous on your public profile, you will not be eligible to receive a prize unless you have completed the following parts of your profile (which you can keep private):
On your Account-Personal page, provide your real and full name, as well as the country in which you reside. Then complete at least one of the three "My Website" fields with your LinkedIn account, professional website and/or other webpages that identify you and your profession. Your company website, combined with an email address in the same domain, is acceptable.
On the Account-Professional page, tell us the name of the company for which you work, the university you attend, or whatever is appropriate in your case.
Bottom line: we want to make sure that the players who win prizes are "real people" and not duplicate accounts, team efforts, or anything else. Of course, we can't stop you from putting in "phony" data, but we remain confident in the honesty and integrity of our players.
Second, regarding the quarterly championship:
1. The above rule applies to everyone who wishes to participate in the championship. In other words, even if you qualify by ranking, you will not be able to play in the championship without completing the minimal elements of your profile listed above. Only "real people" can compete!
2. Everyone will play in the championship at the same time. We will no longer offer multiple times at which it can be taken. We realize that this could cause hardship for some players (I'm thinking about the Pacific nations mostly), but we figure that if you are sufficiently honored and excited to be in the championship, you'll make it work.
In all cases, if a player does not provide the necessary information their status will be set to "Not Ranked" for the appropriate quiz.
We haven't finalized the time for the championship yet, but since most players are in the US and in Europe, we expect to aim for the end of the work day in Europe, late morning in the US.
Thanks once again for your dedicated play on the PL/SQL Challenge site. We will be unveiling new features in the coming months that will make it an even better at helping you become (more of) an expert in Oracle technologies.
Warm regards,
Steven Feuerstein
First, regarding winners of prizes (weekly, monthly, etc.): while you can choose to remain anonymous on your public profile, you will not be eligible to receive a prize unless you have completed the following parts of your profile (which you can keep private):
On your Account-Personal page, provide your real and full name, as well as the country in which you reside. Then complete at least one of the three "My Website" fields with your LinkedIn account, professional website and/or other webpages that identify you and your profession. Your company website, combined with an email address in the same domain, is acceptable.
On the Account-Professional page, tell us the name of the company for which you work, the university you attend, or whatever is appropriate in your case.
Bottom line: we want to make sure that the players who win prizes are "real people" and not duplicate accounts, team efforts, or anything else. Of course, we can't stop you from putting in "phony" data, but we remain confident in the honesty and integrity of our players.
Second, regarding the quarterly championship:
1. The above rule applies to everyone who wishes to participate in the championship. In other words, even if you qualify by ranking, you will not be able to play in the championship without completing the minimal elements of your profile listed above. Only "real people" can compete!
2. Everyone will play in the championship at the same time. We will no longer offer multiple times at which it can be taken. We realize that this could cause hardship for some players (I'm thinking about the Pacific nations mostly), but we figure that if you are sufficiently honored and excited to be in the championship, you'll make it work.
In all cases, if a player does not provide the necessary information their status will be set to "Not Ranked" for the appropriate quiz.
We haven't finalized the time for the championship yet, but since most players are in the US and in Europe, we expect to aim for the end of the work day in Europe, late morning in the US.
Thanks once again for your dedicated play on the PL/SQL Challenge site. We will be unveiling new features in the coming months that will make it an even better at helping you become (more of) an expert in Oracle technologies.
Warm regards,
Steven Feuerstein
17 June 2013
Proposal for new weekly quiz on Database Design
Introduction
In
addition to writing PL/SQL code and constructing SQL queries, database
developers are often called on to design table structures. Wouldn’t it
be great if there was a weekly quiz to test and improve your knowledge
of data modelling to help you with this? Well now there can be!
To
complement the existing PL/SQL and SQL quizzes, we (Steven Feuerstein and Chris Saxon, a longtime PL/SQL Challenge player who is offering to administer this quiz) propose the creation of a new weekly "Database Design" quiz as part of the PL/SQL
Challenge. The intended scope of this quiz is:
- Reading and understanding database schema diagrams
- Determining how to apply database constraints (e.g. foreign keys, unique constraints, etc. ) to enforce business rules
- Understanding and application of database theory (normal forms, star schemas, etc.)
- Appropriate indexing strategies and physical design considerations (e.g. partitioning, index organised tables, etc.)
Below
is a starting set of assumptions and three example quizzes. Please have
a read of these and send us your thoughts on whether or not you would
be interested in a database design quiz and the nature of the questions.
Thanks!
Steven and Chris
Thanks!
Steven and Chris
Assumptions
All schema diagrams shown will be drawn using Oracle Data Modeler, using the following settings:
* The Logical Model Notation Type of Barker
* Relational Model Foreign Key Arrow Direction set to “From Foreign Key to Primary Key”
To indicate constraints on columns, the following will appear next to them:
- An asterisk indicates that the column is mandatory (i.e. not null)
- P means this forms part of the primary key for the table
- F means this forms part of a foreign key to another table
- U means this forms part of a unique constraint on the table
For normal forms, the following definitions are used:
First Normal Form (1NF):
- Table must be two-dimensional, with rows and columns.
- Each row contains data that pertains to one thing or one portion of a thing.
- Each column contains data for a single attribute of the thing being described.
- Each cell (intersection of row and column) of the table must be single-valued.
- All entries in a column must be of the same kind.
- Each column must have a unique name.
- No two rows may be identical.
- The order of the columns and of the rows does not matter.
- There is (at least) one column or set of columns that uniquely identify a row
- Date’s definition that all columns must be mandatory for a table to be in 1NF will not be included for the purposes of this quiz.
Second Normal Form (2NF):
- Table must be in first normal form (1NF).
- All nonkey attributes (columns) must be dependent on the entire key.
Third Normal Form (3NF):
- Table must be in second normal form (2NF).
- Table has no transitive dependencies.
Boyce-Codd Normal Form (BCNF)
- Table must be in third normal form
- Table has no overlapping keys (that is two or more candidate keys that have one or more columns in common)
*
The only database objects that exist are those shown in the quiz or are
available in a default installation of Oracle Enterprise Edition
*
There is no relationship between different answers within a quiz. The
correctness of one choice has no impact on the correctness of any other
choice.
* All code (PL/SQL blocks, DDL statements, SQL statements, etc.) is run in Oracle's SQL*Plus.
* The
edition of the database instance is Enterprise Edition; the database
character set is ASCII-based with single-byte encoding; and a dedicated
server connection is used. The version of the Oracle client software
stack matches that of the database instance.
* All
code in the question and in the multiple choice options run in the same
session (and concurrent sessions do not play a role in the quiz unless
specified). The schema in which the code runs has sufficient system and
object privileges to perform the specified activities.
* When
analyzing or testing choices, you should assume that for each choice,
you are starting "fresh" - no code has been run previously except that
required to install the Oracle Database and then any code in the
question text itself.
* The
session and the environment in which the quiz code executes has enabled
output from DBMS_OUTPUT with an unlimited buffer size, using the
equivalent of the SQL*Plus SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT
WRAPPED command to do the enabling. The SET DEFINE OFF command has been
executed so that embedded "&" characters will not result in prompts
for input. Unless otherwise specified, assume that the schema in which
the code executes is not SYS nor SYSTEM, nor does it have SYSDBA
privileges.
*
Index performance will be measured in terms of the “constistent gets”
metric, as reported when enabling autotrace. This will be done using the
command: “set autotrace trace” in SQL*Plus.
Q1: Topic: Understanding entity relationship diagrams
Difficulty: Beginner
Below is a schema diagram showing a table structure for storing order and product details in a database:
Given this structure, which of the following statements are true:
Questions
Q1. All orders must be linked to a product.
Q2. An order may be linked to more than one product.
Q3. A product may be associated with multiple orders.
Q4. It is not possible to have products with no linked orders.
Answers
A1.
Correct. The ORDERS.PRODUCT_ID column is a mandatory foreign key to the
PRODUCTS table, as denoted by the F (foreign key) and asterisk
(mandatory) next to this column.
A2. Wrong. Only one value can be stored in the ORDERS.PRODUCT_ID column for a given ORDERS row.
A3.
Correct. There is no unique constraint on the ORDERS.PRODUCT_ID column,
therefore the same product can be listed on more than one row in the
ORDERS table.
A4.
Wrong. As the ORDERS.PRODUCT_ID column is mandatory, an entry must
exist in the PRODUCTS table before an order can be linked to it.
Therefore it must be possible to have a product with no associated
orders.
Q3 Topic: 3rd normal form
Difficulty: Intermediate
A
new social networking site is under development and work is taking
place on the user profile page. The current requirements are a profile
page should be able to display:
* A user’s email address
* Their name
* Their date of birth
* Their astrological star sign (determined from the day and month of their birth)
You’ve been asked to design the table to store this information and have come up with the following:
create table plch_user_profile (
user_id integer primary key,
email varchar2(320) not null unique,
display_name varchar2(200),
date_of_birth date not null,
star_sign varchar2(10)
);
Unfortunately,
the data architect isn’t happy with this, saying the table isn’t in
third normal form! How can this be changed so it complies with third
normal form while still meeting the requirements above?
Questions
Q1. Remove the unique constraint on the EMAIL column.
Q2. Remove the STAR_SIGN column from the table.
Q3. Change the DATE_OF_BIRTH column to accept null values.
Q4. Remove the DATE_OF_BIRTH column from the table.
Answers
A1.
Wrong. Removing this constraint means we still have a dependency
between DATE_OF_BIRTH and STAR_SIGN, which can lead to "update
anomalies" if only one of these columns is changed. To meet third normal
form, we must remove one of these columns.
A2.
Correct. We can calculate a person's star sign from the day and month
of their birth, so we can still display a person's star sign on their
profile if we just store their date of birth.
A3.
Wrong. There’s still a (non-prime) dependency between DATE_OF_BIRTH and
STAR_SIGN. This means it is possible to enter a (date_of_birth,
star_sign) pair that isn't valid in the real world (e.g. 1st Jan 1980,
Libra)
A4.
Wrong. Removing DATE_OF_BIRTH from the table does remove the non-prime
dependency DATE_OF_BIRTH->STAR_SIGN so the table is now in third
normal form. However, we can't determine the DATE_OF_BIRTH just from a
peron's star sign, meaning we no longer meet the business requirement to
display this!
Q3 Topic: Indexing strategies
Difficulty: Intermediate
We have the following table that stores details of customer orders:
create table plch_orders (
order_id integer primary key,
customer_id integer not null,
order_date date not null,
order_status varchar2(20) check (
order_status in ('UNPAID', 'NOT SHIPPED', 'COMPLETE', 'RETURNED', 'REFUNDED')),
notes varchar2(50)
);
This
stores over a million orders that have been placed over the past ten
years. The vast majority (>95%) of the orders have the status of
COMPLETE. There are a few thousand different customers that place
approximately one order per month.
Recently
there’s been complaints from customers that their orders are taking a
long time to arrive, so your boss wants a daily report displaying the
PLCH_ORDERS.CUSTOMER_ID for orders that have been placed within the past
month that have not yet been shipped. You put together the following
query:
select customer_id
from plch_orders
where order_date > add_months(sysdate, -1)
and order_status = 'NOT SHIPPED';
Unfortunately,
there's currently no indexes on the columns used in this query so it's
taking a long time to run! Which of the following indexes can we create
that can benefit the performane of this query:
Questions
Q1. create index plch_unshipped on plch_orders (order_status);
Q2. create index plch_unshipped on plch_orders (order_status, order_date);
Q3. create index plch_unshipped on plch_orders (order_status, order_date, customer_id);
Q4. create index plch_unshipped on plch_orders (customer_id);
Q5. create index plch_unshipped on plch_orders ( customer_id, order_date, order_status);
Q6. create index plch_unshipped on plch_orders ( customer_id, order_status);
Answers
A1. Correct. Only a small percentage of orders will have the status "NOT SHIPPED" so an index on this will be beneficial.
A2.
Correct. Adding the date to the index enables Oracle to filter the data
even further, requiring fewer rows to be inspected in the table.
A3. Correct. This is a "fully covering" index, meaning the query can be answered without having to access the table at all.
A4.
Wrong. CUSTOMER_ID doesn’t appear in the where clause of the query,
therefore it is not available to Oracle to improve the execution of this
query.
A5.
Correct. This also a "fully covering" index, so the Oracle can answer
the query by just inspecting the index without accessing the table.
However, this will result in an “INDEX FAST FULL SCAN” rather than an
“INDEX RANGE SCAN” as in answer three which is less efficient. This is
because CUSTOMER_ID is the first column in the index, so Oracle is not
able to access the NOT SHIPPED rows directly. Instead it must scan
through the whole index
A6.
Wrong. For an index to be considered by the optimizer then the first
column(s) in the index must be in the where clause. If the leading
columns have a low distinct cardinality, then Oracle can choose to
perform an "index skip scan" operation. However, we have a large number
of different customers in this case, so a skip scan is not possible.
Verification Code
create table plch_orders (
order_id integer primary key,
customer_id integer not null,
order_date date not null,
order_status varchar2(20) check (
order_status in ('UNPAID', 'NOT SHIPPED', 'COMPLETE', 'RETURNED', 'REFUNDED')),
notes varchar2(50));
insert into plch_orders
select rownum, mod(rownum, 3179),
sysdate - ((1000000-rownum)/1000000)*3650,
case when rownum <= 950000 then 'COMPLETE'
else decode(mod(rownum, 5),
0, 'COMPLETE',
1, 'UNPAID',
2, 'NOT SHIPPED',
3, 'RETURNED',
4, 'REFUNDED')
end,
dbms_random.string('x', 50)
from dual
connect by level <= 1000000;
commit;
exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);
set autotrace trace
PRO default query results in full table scan
select customer_id
from plch_orders
where order_date > add_months(sysdate, -1)
and order_status = 'NOT SHIPPED';
create index plch_unshipped on plch_orders (order_status);
exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);
PRO Index range scan with table access by rowid
select customer_id
from plch_orders
where order_date > add_months(sysdate, -1)
and order_status = 'NOT SHIPPED';
drop index plch_unshipped;
create index plch_unshipped on plch_orders (order_status, order_date);
exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);
PRO Index range scan with table accesss by rowid.
PRO Because order_date is included, this is index is more selective so results in fewer consistent gets
select customer_id
from plch_orders
where order_date > add_months(sysdate, -1)
and order_status = 'NOT SHIPPED';
drop index plch_unshipped;
create index plch_unshipped on plch_orders (order_status, order_date, customer_id);
exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);
PRO Index range scan with no table access. The most efficient option
select customer_id
from plch_orders
where order_date > add_months(sysdate, -1)
and order_status = 'NOT SHIPPED';
drop index plch_unshipped;
create index plch_unshipped on plch_orders (customer_id);
exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);
PRO Causes a full table scan as in the un-indexed example
select customer_id
from plch_orders
where order_date > add_months(sysdate, -1)
and order_status = 'NOT SHIPPED';
drop index plch_unshipped;
create index plch_unshipped on plch_orders (customer_id, order_date, order_status);
exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);
PRO Results in an index fast full scan. This is less efficient than the other correct examples,
PRO but is still more efficient than a FTS, resulting in ~1/3 fewer consistent gets in my example
select customer_id
from plch_orders
where order_date > add_months(sysdate, -1)
and order_status = 'NOT SHIPPED';
drop index plch_unshipped;
create index plch_unshipped on plch_orders (customer_id, order_status);
exec dbms_stats.gather_table_stats(user, 'plch_orders', cascade => true);
PRO Results in a full table scan as in the unindexed example
select customer_id
from plch_orders
where order_date > add_months(sysdate, -1)
and order_status = 'NOT SHIPPED';
Subscribe to:
Posts (Atom)