Posts by saxon:

    Database design resources: My reading list

    February 27th, 2015

    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 Database Design Quiz in Numbers: 2014 Review

    February 24th, 2015

    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;
    -- The same number of calories as a 
    -- Five Guys BLT
    -- How many people took every quiz?
    select count(*)
    from   players
    where  quizzes_taken = 52;
    -- The ascii character code for 
    -- the exclamation mark!
    -- What was the total of all scores 
    -- across all players?
    select sum(scores)
    from   players;
    -- 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;
    -- 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 ;)


    SQL Brainteasers: Guess the Superhero

    February 15th, 2015

    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';
      insert into boy (mouth)
      values ('Musa × paradisiaca');
      update boy 
      set    strength = '20 men',
             power = 'flight';

    As always, put your answers in the comments!


    Using Edition-Based Redefinition to Bypass Those Pesky Triggers

    February 12th, 2015

    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 = 
    drop trigger which_breaks_your_datafix;
    update the_table_with_the_trigger
    set    the_data = 
      'values prevented by your trigger';
    alter session set 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:

    Oracle-BASE article
    Tom Kyte’s Oracle Magazine article, Edition-Based Redefinition, Part 1
    Edition-Based Redefinition Whitepaper, Bryn Llewellyn July 2009


    SQL Brainteasers: Guess the Chocolate Bar

    January 17th, 2015

    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!


    Goodbye Flybe, Hello Oracle!

    January 5th, 2015
    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 :)


    SQL Brainteasers: Guess the Mythical Creature

    December 31st, 2014

    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';
      delete from creature
      where  appendage = 'head';
      insert into creature (appendage)
      values ('head');
      insert into creature (appendage)
      values ('head');
    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'


    SQL Brainteasers: Guess the Country part 2

    November 7th, 2014

    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!


    SQL Brainteasers: Guess the Country

    October 19th, 2014

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


    One Year of the Database Design Quiz

    October 5th, 2014

    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;
    -- 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;
    -- The mass (in kilos) of a typical dairy cow
    -- ~7 times the average adult body power 
    -- consumption (in watts)
    select count(*)
    from   answers_submitted;
    -- 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;
    -- 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, register for a free account and get playing!