Posts by saxon:

    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!

    2 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 :)

    11 Comments "

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

    4 Comments "

    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!

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

    2 Comments "

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

    4 Comments "

    SQL Quiz: How do inserts create database objects?

    June 3rd, 2014

    I insert a row into a table, roll it back, yet somehow I’ve created a new object in my database!

    select count(*) from user_objects;
    
      COUNT(*)
    ----------
             2
    
    insert into a_table
    values ('that create', 1, 'new object');
    
    rollback;
    
    select count(*) from user_objects;
    
      COUNT(*)
    ----------
             3
    

    There’s no triggers or other “trickery” involved – the code above is a copy-paste in a database where I have the only session.

    How did this happen? Put your answers in the comments!

    If you’d like to get more posts on using and designing Oracle databases, enter your details the form below to receive future updates.

    9 Comments "

    Oracle can now use function-based indexes in queries without functions!

    May 5th, 2014

    There’s a neat optimization in Oracle I found while tinkering around (look closely at the predicate section):

    select oracle_can_now_use,
           a_function_based_index
    from   queries_with_no_functions
    where  a_date_col_with_an_fbi = :a_date
    and    oracle_version >= '11.2.0.2';
    
    -------------------------------------------------------------------------
    | Id  | Operation                   | Name                      | Rows  |
    -------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                           |     1 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| QUERIES_WITH_NO_FUNCTIONS |     1 |
    |*  2 |   INDEX RANGE SCAN          | QUWF_DATE_FBI             |     1 |
    -------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("ORACLE_VERSION">='11.2.0.2' AND "A_DATE_COL_WITH_AN_FBI"=:A_DATE)
       2 - access(TRUNC(INTERNAL_FUNCTION("A_DATE_COL_WITH_AN_FBI"))=TRUNC(:A_DATE))
    

    Line 2 of the predictates states that the INDEX RANGE SCAN was accessed via TRUNC(INTERNAL_FUNCTION()) – a function-based index was used when no function is present in the where clause!

    This optimization appeared in 11.2.0.2 (fix 9263333) – I have to thank the people of Oracle-L (particularly Velikikh Mikhail) identifying when this improvement came in.

    This optimization isn’t restricted to TRUNC() on date columns however.
    Read the rest of this entry “

    3 Comments "

    In Defense of Joins – Recap

    April 11th, 2014

    Over the past few articles we’ve looked at database joins. It started out with me noticing that joins appear to be getting bad press recently and wondering whether they really are the root of all evil that some people seem to think they are.

    We seen that denormalizing removes joins, giving performance benefits to primary key lookups. This benefit can come at the cost of non-primary key lookup queries though – often the extra work for these “search” queries is outweights the gains made for the primary key lookups.

    Query performance (particularly for “small” systems) is just part of the story though. The real cost of removing joins is the impact on data modification. Aside from (potentially) vastly increasing the number of records must update to keep data consistent, denormalization can introduce waiting issues for concurrent updates. In the worst case we may introduce application bugs in the form of deadlocks.

    We’ve seen there are some cases where joins do result in inefficient queries. These are usually the result of poor design decisions or limitations of the optimizer. Better design and use of Oracle features can overcome the worst of many of these however.

    I hope you’ve found this series (and blog as a whole!) useful and informative. If there’s anything else you’d like to see on the subject of joins get in touch or say in the comments.

    I’m going to take a break from regular blogging for a while to work on other projects. Thanks to those of you who’ve been following. If you’d like to receive posts when I start again, just enter your email address in the form below!

    No Comments "

    When Joins Go Bad

    April 7th, 2014

    So far in the joins series we’ve looked at the effect removing joins (via denormalization) has on performance. We’ve seen that joins can cause primary key looks to do more work. Lowering the normalization level to remove these can negatively impact “search” style queries though. More importantly, we’ve seen the real cost of denormalizing to remove joins is when updating records, potentially leading to concurrency waits and application bugs.

    So are joins always “good”?

    The fastest way to do anything is to not do it at all. If joins aren’t necessary to answer your queries, including them will add some overhead. Also, like any tool, there’s situations where adding a join may substantially slow your query down.

    Here’s some examples where joins may be “expensive” and strategies for coping with them.
    Read the rest of this entry “

    No Comments "