Posts by saxon:
Following the database design championship playoff over on the PL/SQL Challenge, one player asked:
“Could you please advise me some resources (boooks, courses) so that I can improve?”
Of course I can! This is what I love to hear – people looking for resources they can use to increase their skills. This is information I’m sure others will benefit from, so here goes:
select materials from my_readings where topic = 'Database design';
I’ve found The Database Programmer blog to be immensely helpful for explaining normal forms and constraints. This provided the inspiration for the “shift patterns” question using the school class schema developed aross the “Table Design Patterns” series.
Another fun paper for explaining normalization is “Data Normalization, Denormalization and the Forces of Darkness”
Many people struggled with the orders “physical co-locating data” question. The inspiration for that question came from a number of places:
* Martin Widlake’s IOT series
* Iggy Fernandez’s “No to SQL and No to NoSQL” which has an example using table clusters at the bottom. His “The Twelve Days of NoSQL” series on the same blog also provides great ideas.
* The Data Warehouse Insider Blog article on attribute clustering
One key thing to remember is that physical data location can matter hugely for database performance. Unfortunately this is one area that people often overlook. The lack of index-organized tables in most Oracle databases is a great example of this.
To understand more about indexing – particularly across different database platforms – Use the Index, Luke! is a great site. If you really want to understand indexes in Oracle, take a look at Richard Foote’s blog (note – this does get very detailed!).
Of course, no list about designing for Oracle would be complete without Tom Kyte’s books. Reading these helped me understand how Oracle functions.
Finally I have to wrap up with a quick nod to the Oracle docs. Not exactly bedtime reading, but skimming through topics there has provided inspiration for a number of questions on the PL/SQL Challenge. :)
Of course this isn’t a complete list (that would span many pages!). These are some of the resources I’ve found most useful (and refer back to often).
So I’d like to hear from you. Which resources have you found most valuable when it comes to learning about designing (Oracle) databases? Please share your thoughts in the comments – I’d love to hear what other people have found useful.
The results of the first ever database design championship playoff were announced today. Huge congratulations to Sean Molloy, Pavel Zeman and Justin Cave for taking the top three spots. Also thanks to everyone else who’s taken part in the quiz, answering questions, adding to the discusions and pointing out my mistakes ;)
With this in mind here’s a some of the facts and figures from 2014’s database design quizzes, along with related trivia:
-- How many player were there in total? select count(*) from players; COUNT(*) -------- 533 -- The same number of calories as a -- Five Guys BLT -- How many people took every quiz? select count(*) from players where quizzes_taken = 52; COUNT(*) -------- 33 -- The ascii character code for -- the exclamation mark! -- What was the total of all scores -- across all players? select sum(scores) from players; SUM(SCORES) ----------- 2922577 -- About the same mass in kg as a -- Saturn V Rocket (2,970,000 kg) -- and how long did they take -- to place their answers? select sum(user_seconds) from player_answers; SUM(USER_SECONDS) ----------------- 3518872 -- 40 days, 17 hours, 27 minutes and 52 seconds -- About 0.46 years on the planet of Mercury
Want to know what all the fuss is about? Then just head over to PL/SQL Challenge and sign up for a free account. Who knows, it could be your name in lights this time next year ;)
Here’s another six SQL brainteasers. This time the SQL is a clue to the power(s) of a superhero. Who is each superhero?
insert into body (skeleton) values ('adamantium'); update person set strength = 'superhuman' where emotion = 'anger'; delete from powers where location = 'near Kryptonite'; update body set exoskeleton = 'ferrous material'; select * from women where equipment = 'noosed rope of veracity'; begin insert into boy (mouth) values ('Musa × paradisiaca'); update boy set strength = '20 men', power = 'flight'; end; /
As always, put your answers in the comments!
There’s a problem most people who’ve had to do production datafixes have encountered at some point: you need to update a dataset. Unfortunately there’s a trigger on the target table. The trigger either prevents the update outright or just has some unwanted side effects (firing off a business process, etc.). The trigger is necessary for the application to function correctly, so you can’t just drop it.
How do you apply the changes?
You can take an outage so you can drop/disable the trigger while applying the fix. Alternatively you can modify the application so that the trigger is no longer necessary. Scheduling the outage can be difficult (particularly if the fix is urgent). Changing the application may take a long time or even be infeasible if it’s supplied by a third party.
How can you get out of this dilemma?
Fortunately in Oracle 11gR2 and higher there is another way:
create edition to_do_your_dirty_work; alter session set edition = to_do_your_dirty_work; drop trigger which_breaks_your_datafix; update the_table_with_the_trigger set the_data = 'values prevented by your trigger'; alter session set edition = back_to_your_original_edition; drop edition to_do_your_dirty_work cascade;
Using edition-based redefinition you can create a new edition and drop the trigger just in that edition. The application will still function as desired because the trigger still exists in the old edition. All you need to worry about now is concurrency issues… ;)
Warning: you must first editions enable your application before you can do this. Only some object types are “editionable” (see the comments). If you have non-editioned objects that depend upon editioned objects you’ll need to make some changes to your application before you can do this! Doing this is also a “one-way” operation – to revert it you need to recreate your user/restore from backup!
For a complete script showing this in action, click here.
For some further reading on EBR, have a look at the following:
Some more SQL clues, this time to chocolate bars.
select iso_code from countries where capital_city = 'Taipei' and 9 = roman; select * from planets where position = 4; select characters from thundercats where age = 'prepubescent'; select * from ships where crew = 'mutinous'; select * from cities where that_is = 'Eboracum'; select min(volume) from speech;
When you’ve got them, put your answers in the comments!
update people set employer = 'Oracle', job_title = 'Database Evangelist' where name = 'Chris Saxon';
That’s right, as of last week I’m now an Oracle employee! I’m joining Steven Feuerstein’s database evangelist team, a hugely exciting opportunity I’m honoured to be a part of. I’ll be joining Natalka, Dan and Todd in helping people customers get the most out of their Oracle databases.
My focus will be SQL and Oracle’s differentiating features such as Edition Based Redefinition which help make, in my opinion, Oracle the best database available. I love sharing my knowledge and assisting people with improving their skills, so this job suits me well.
Farewell to all my colleagues at Flybe. I had a great time working with you all. This is an opportunity too good to pass up though.
2015 is set to be a great year :)
Another batch of SQL brainteasers. This time the clues are to the powers of mythical creatures – the task is to guess the creature!
There’s six in all, can you get them all? Put your answers in the comments!
update target_of_gaze set state = 'stone'; begin delete from creature where appendage = 'head'; insert into creature (appendage) values ('head'); insert into creature (appendage) values ('head'); end; / update bird set state = 'alive' where state = 'burning'; select * from women where singing = 'deadly'; update creature set body = 'furry' where moon = 'full'; select * from men where powers in ( 'conferring playthings', 'determining moral terpitude' );
Continuing the nation theme, here’s another six SQL clues to countries. Can you get them all?
select first_person from roman_eleven where business = 'abbreviated'; select aquatic_limb from places; select * from dinner where contents in ('beef', 'kidney beans'); select character from films where actor = 'Tom Green'; select unrepeatable_position from flightless_birds where allegiance = 'Confederate'; select fortification from wine where second_person = 'female';
As always, put your answers in the comments!
Same concept, different theme. All the SQL statements below are clues to the English spelling of countries.
Can you get them all?
As always, put your answers in the comments!
select * from german_affirmative join cooking_vessel; select * from nuts where anaphylaxis = 'sexually transmitted'; select * from olde_english_the join males; select * from elements where atomic_number = 47; select * from programming_languages where able_to = 'influence PL/SQL'; select * from places where H2O = 'frozen';
Today celebrates one year of the database design quiz on the PL/SQL Challenge!
Here’s a look back at some of the stats for the quiz to date with some trivia related to the numbers :)
select count(*) from quizzes; COUNT(*) -------- 52 -- The number of white keys on a piano -- the smallest number with 4 representations -- as the sum of 4 positive squares select count(*) from players; COUNT(*) -------- 691 -- The mass (in kilos) of a typical dairy cow -- ~7 times the average adult body power -- consumption (in watts) select count(*) from answers_submitted; COUNT(*) -------- 13723 -- Approximately the length of the Great Wall -- of China (in miles) and the mass of -- a large dinosaur (in stones) select name, score from (select name, score from players order by score desc ) where rownum <= 5; NAME SCORE ---------------- ----- mentzel.iudith 20618 Justin Cave 20038 Joaquin Gonzalez 19284 Krzysztof Helbin 19242 Elic 19127 -- Iudith and Justin are doing a great job, -- the only players to break 20,000 to date! select avg(rating) from player_surveys; AVG(RATING) ----------- 4.13 -- 50 times the half life of uranium 241 -- (in hours) -- Great to see the average rating above 4 :) -- I have to thank my reviewers, particularly -- Yuan Tschang, for helping keep the quality -- high!
Thanks to everyone who’s taken part, pointed out my mistakes and added to the discussions for the quiz over the past year.
If you’ve not played yet, today’s a great time to get started. Just head over to www.plsqlchallenge.com, register for a free account and get playing!