23 December 2013

23 December Quiz Now Available

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

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:
  • 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.
We will continue to seek out opportunities to streamline processing on the site. Please feel free to post a comment on this blog with any other suggestions for places on the site to focus our efforts.

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

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.

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

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.

Rank Name Country Total Time % Correct Total Score
1Michal Cvan (10)Slovakia31 mins 08 secs100%3127
2mentzel.iudith (12)Israel26 mins 41 secs96%3066
3Janis Baiza (8)Latvia14 mins 28 secs88%3011
4Jeroen Rutte (6)Netherlands21 mins 05 secs88%2878
5Peter Schmidt (5)Germany21 mins 56 secs88%2861
6Ivan Blanarik (7)Slovakia24 mins 50 secs88%2803
7Ingimundur Gudmundsson (2)Norway31 mins 38 secs88%2667
8_tiki_4_ (5)Germany20 mins 13 secs80%2596
9Frank Schmitt (7)Germany20 mins 43 secs80%2586
10Andrey Zaytsev (1)Russia29 mins 03 secs84%2569
11Viacheslav Stepanov (11)Russia30 mins 55 secs84%2532
12Frank Puechl (3)Germany17 mins 37 secs76%2498
13Niels Hecker (13)Germany10 mins 47 secs72%2484
14Pavel Vorontsov (1)Russia16 mins 23 secs72%2372
15james su (7)Canada20 mins 07 secs72%2298
16Tony Winn (4)Australia23 mins 33 secs72%2229
17Siim Kask (12)Estonia24 mins 36 secs72%2208
18Stelios Vlasopoulos (9)Belgium30 mins 48 secs72%2084
19Denis Ivin (1)Russia31 mins 44 secs72%2065
20Tobias Stark (3)Germany32 mins 21 secs72%2053
21Zoltan Fulop (6)Hungary31 mins 49 secs68%1914
22Giedrius Deveikis (4)Lithuania11 mins 31 secs56%1870
23Hamid Talebian (1)Netherlands34 mins 19 secs68%1864
24Thierry Poels (6)Belgium25 mins 55 secs60%1732
25swart260 (6)Netherlands18 mins 49 secs52%1574
26Rakesh Dadhich (4)India16 mins 00 secs48%1480
27Oleksiy Varchyn (1)Norway30 mins 19 secs40%894
28Rich Barnhart (1)United States25 mins 12 secs36%846

Championship Performance History

After each name, the quarter in which he or she played, and the ranking in that championship.
Name History
Michal CvanQ3 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.iudithQ4 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 BaizaQ2 2010:3rd, Q4 2010:7th, Q3 2011:9th, Q4 2011:1st, Q3 2012:23rd, Q1 2013:4th, Q2 2013:4th, Q3 2013:3rd
Jeroen RutteQ3 2010:19th, Q3 2012:10th, Q4 2012:11th, Q1 2013:8th, Q2 2013:18th, Q3 2013:4th
Peter SchmidtQ3 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 GudmundssonQ1 2013:25th, Q3 2013:7th
_tiki_4_Q4 2011:26th, Q1 2012:16th, Q2 2012:20th, Q4 2012:8th, Q3 2013:8th
Frank SchmittQ4 2011:22nd, Q2 2012:2nd, Q3 2012:21st, Q4 2012:3rd, Q1 2013:13th, Q2 2013:1st, Q3 2013:9th
Andrey ZaytsevQ3 2013:10th
Viacheslav StepanovQ1 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 PuechlQ3 2012:26th, Q1 2013:15th, Q3 2013:12th
Niels HeckerQ2 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 VorontsovQ3 2013:14th
james suQ3 2010:56th, Q2 2011:7th, Q3 2011:11th, Q4 2011:14th, Q2 2012:15th, Q1 2013:7th, Q3 2013:15th
Tony WinnQ3 2010:16th, Q2 2013:17th, Q3 2013:16th
Siim KaskQ1 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 VlasopoulosQ4 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 IvinQ3 2013:19th
Tobias StarkQ1 2012:32nd, Q3 2012:3rd, Q3 2013:20th
Zoltan FulopQ1 2012:15th, Q2 2012:26th, Q3 2012:22nd, Q4 2012:17th, Q1 2013:22nd, Q3 2013:21st
Giedrius DeveikisQ2 2012:5th, Q3 2012:9th, Q3 2013:22nd
Hamid TalebianQ3 2013:23rd
Thierry PoelsQ3 2011:22nd, Q1 2012:24th, Q1 2013:26th, Q3 2013:24th
swart260Q2 2012:21st, Q3 2012:25th, Q4 2012:25th, Q1 2013:29th, Q2 2013:26th, Q3 2013:25th
Rakesh DadhichQ2 2012:10th, Q1 2013:23rd, Q2 2013:28th, Q3 2013:26th
Oleksiy VarchynQ3 2013:27th
Rich BarnhartQ3 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. 

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!


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)1Top 25Netherlands
Rakesh Dadhich (3)2Top 25India
Oleksiy Varchyn (0)3Top 25Norway
Stelios Vlasopoulos (8)4Top 25Belgium
Ajaykumar Gupta (2)5Top 25Singapore
Janis Baiza (7)6Top 25Latvia
mentzel.iudith (11)7Top 25Israel
Jerry Bull (9)8Top 25United States
Niels Hecker (12)9Top 25Germany
Frank Puechl (2)10Top 25Germany
Siim Kask (11)11Top 25Estonia
Viacheslav Stepanov (10)12Top 25Russia
Justin Cave (9)13Top 25United States
dannyg64 (2)14Top 25United States
Peter Schmidt (4)15Top 25Germany
Andrey Zaytsev (0)16Top 25Russia
Hamid Talebian (0)17Top 25Netherlands
Jeroen Rutte (5)18Top 25Netherlands
Michal Cvan (9)19Top 25Slovakia
Milibor Jovanovic (3)20Top 25Serbia
Zoltan Fulop (5)21Top 25Hungary
Pavel Vorontsov (0)22Top 25Russia
Frank Schmitt (6)23Top 25Germany
_tiki_4_ (4)24Top 25Germany
Ivan Blanarik (6)25Top 25Slovakia
Tony Winn (3)32WildcardAustralia
Giedrius Deveikis (3)38CorrectnessLithuania
Denis Ivin (0)42CorrectnessRussia
Tobias Stark (2)56CorrectnessGermany
Martin_Scholz_Berlin_Berlin (0)57CorrectnessGermany
james su (6)84WildcardCanada
Ingimundur Gudmundsson (1)87CorrectnessNorway
Thierry Poels (5)92CorrectnessBelgium
richb (0)133CorrectnessUnited States
Dan Kiser (4)223CorrectnessUnited States
Telmoc (0)224CorrectnessPortugal
Upendra Motamarri (0)316CorrectnessIndia


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!

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
Here's a picture of our quarterly trophy:

 
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.

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:
  • 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
Whether you're an experienced data modeller or completely new to relational databases, the weekly Database Design Quiz offers you the opportunity to both learn new approaches and show off your expertise. It will teach techniques that you can use to improve the quality for your work and impress future employers with your achievements.

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.

Rank Name Country Total Time % Correct Total Score
1Frank Schmitt (6)Germany23 mins 28 secs92%2971
2Chris Saxon (6)United Kingdom27 mins 56 secs92%2881
3Niels Hecker (12)Germany29 mins 10 secs92%2857
4Janis Baiza (7)Latvia22 mins 49 secs88%2834
5Peter Schmidt (4)Germany30 mins 57 secs92%2821
6Ivan Blanarik (6)Slovakia33 mins 42 secs92%2741
7mentzel.iudith (11)Israel34 mins 38 secs92%2682
8Chad Lee (9)United States32 mins 35 secs88%2663
9Alexey Ponomarenko (1)Ukraine32 mins 45 secs88%2635
10Jason H (2)United States30 mins 31 secs84%2520
11Michal Cvan (9)Slovakia29 mins 01 secs80%2485
12Mike Pargeter (11)United Kingdom23 mins 51 secs76%2473
13Randy Gettman (11)United States32 mins 28 secs80%2451
14Vincent Malgrat (6)French Republic34 mins 42 secs84%2431
15kowido (10)Germany34 mins 27 secs84%2416
16Dieter Kowalski (5)Germany34 mins 13 secs80%2356
17Jerry Bull (9)United States31 mins 05 secs80%2353
18Siim Kask (11)Estonia25 mins 03 secs76%2289
19Tony Winn (3)Australia31 mins 34 secs80%2244
20Jeroen Rutte (5)Netherlands32 mins 26 secs76%2241
21Ajaykumar Gupta (2)Singapore33 mins 57 secs76%2211
22Frank Schrader (12)Germany32 mins 21 secs76%2143
23Veera Marimuthu (2)Singapore30 mins 45 secs76%2135
24Matthias Rogel (3)Germany18 mins 02 secs60%1869
25Viacheslav Stepanov (10)Russia31 mins 11 secs64%1751
26Stelios Vlasopoulos (8)Belgium23 mins 17 secs56%1674
27Leszek Grudzień (1)Poland25 mins 17 secs56%1599
28Pavel Noga (1)Czech Republic34 mins 52 secs60%1593
29swart260 (5)Netherlands33 mins 40 secs64%1582
30Vinu Garg (4)India24 mins 03 secs56%1569
31Rakesh Dadhich (3)India27 mins 45 secs56%1550
32Naresh Kumar (1)India32 mins 00 secs60%1540
33Yuan Tschang (7)United States34 mins 43 secs60%1531
34Livio (1)Luxembourg34 mins 51 secs44%878

Playoff Performance History

After each name, the quarter in which he or she played, and the ranking in that playoff.
Name History
Frank SchmittQ4 2011:24th, Q2 2012:4th, Q3 2012:23rd, Q4 2012:5th, Q1 2013:15th, Q2 2013:1st
Chris SaxonQ4 2010:16th, Q2 2011:2nd, Q4 2011:8th, Q3 2012:22nd, Q4 2012:3rd, Q2 2013:2nd
Niels HeckerQ2 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 BaizaQ2 2010:3rd, Q4 2010:7th, Q3 2011:10th, Q4 2011:2nd, Q3 2012:25th, Q1 2013:6th, Q2 2013:4th
Peter SchmidtQ3 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.iudithQ4 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 LeeQ2 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 PonomarenkoQ2 2013:9th
Jason HQ3 2012:19th, Q2 2013:10th
Michal CvanQ3 2010:23rd, Q4 2010:25th, Q3 2011:23rd, Q1 2012:12th, Q3 2012:16th, Q4 2012:17th, Q1 2013:16th, Q2 2013:11th
Mike PargeterQ4 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 GettmanQ3 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 MalgratQ4 2011:10th, Q1 2012:15th, Q2 2012:16th, Q4 2012:8th, Q1 2013:3rd, Q2 2013:14th
kowidoQ1 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 KowalskiQ1 2012:21st, Q2 2012:8th, Q4 2012:16th, Q1 2013:24th, Q2 2013:16th
Jerry BullQ2 2011:34th, Q3 2011:9th, Q1 2012:14th, Q2 2012:14th, Q3 2012:15th, Q4 2012:18th, Q1 2013:11th, Q2 2013:17th
Siim KaskQ1 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 WinnQ3 2010:17th, Q2 2013:19th
Jeroen RutteQ3 2010:20th, Q3 2012:12th, Q4 2012:13th, Q1 2013:10th, Q2 2013:20th
Ajaykumar GuptaQ1 2013:7th, Q2 2013:21st
Frank SchraderQ3 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 MarimuthuQ1 2013:21st, Q2 2013:23rd
Matthias RogelQ2 2013:24th
Viacheslav StepanovQ1 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 VlasopoulosQ4 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 NogaQ2 2013:28th
swart260Q2 2012:24th, Q3 2012:27th, Q4 2012:28th, Q1 2013:32nd, Q2 2013:29th
Vinu GargQ2 2012:32nd, Q1 2013:31st, Q2 2013:30th
Rakesh DadhichQ2 2012:13th, Q1 2013:26th, Q2 2013:31st
Naresh KumarQ2 2013:32nd
Yuan TschangQ2 2012:27th, Q3 2012:26th, Q4 2012:30th, Q2 2013:33rd
LivioQ2 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:

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.

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!

Name Rank Qualification Country
Ajaykumar Gupta (1)1Top 25Singapore
Rakesh Dadhich (2)2Top 25India
Janis Baiza (6)3Top 25Latvia
swart260 (4)4Top 25Netherlands
mentzel.iudith (10)5Top 25Israel
Stelios Vlasopoulos (7)6Top 25Belgium
Chris Saxon (5)7Top 25United Kingdom
Vinu Garg (3)8Top 25India
Milibor Jovanovic (2)9Top 25Serbia
Mike Pargeter (10)10Top 25United Kingdom
Ivan Blanarik (5)11Top 25Slovakia
Viacheslav Stepanov (9)12Top 25Russia
Niels Hecker (11)13Top 25Germany
Veera Marimuthu (1)14Top 25Singapore
Vincent Malgrat (5)15Top 25French Republic
Frank Schrader (11)16Top 25Germany
kowido (9)17Top 25Germany
Jeroen Rutte (4)18Top 25Netherlands
Dieter Kowalski (4)19Top 25Germany
Jerry Bull (8)20Top 25United States
Siim Kask (10)21Top 25Estonia
Jason H (1)22Top 25United States
Leszek Grudzień (0)23Top 25Poland
Tony Winn (2)24Top 25Australia
Frank Schmitt (5)25Top 25Germany
Peter Schmidt (3)31CorrectnessGermany
Joaquin Gonzalez (5)32WildcardSpain
Chad Lee (8)33CorrectnessUnited States
Matthias Rogel (2)34WildcardGermany
Naresh Kumar (0)35WildcardIndia
Jan Soubusta (0)48WildcardCzech Republic
Alexey Ponomarenko (0)77WildcardUkraine
Yuan Tschang (6)85CorrectnessUnited States
Randy Gettman (10)94CorrectnessUnited States
Pavel Noga (0)96CorrectnessCzech Republic
Michal Cvan (8)107CorrectnessSlovakia
Livio (0)332CorrectnessLuxembourg

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:


   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

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


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';