Jan 052015
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 :)

Apr 042014

In the previous article in the joins series we compared query performance between a third normal form schema and the same schema denormalized to second normal form. We then extended it the example so our denormalized schema was in just first normal form.

The normalized approach performed better overall. The differences were small though – generally just a few consistent gets and all the queries executed in under a second. As Jeff Atwood points out, hardware is powerful enough that for most systems the performance between more normalized and less normalized schemas won’t make much difference to your queries. I disagree slightly with one of his conclusions though – that normalziation “doesn’t matter”.

To see why, let’s look at a different use-case: data modification. While this isn’t to do with joins directly, it is a very important consideration when deciding to denormalize to “remove joins”.

I’m not going to get into the details of the performance updates – there’s a much bigger problem waiting for us. Can you see what it is?
Continue reading »

Mar 242014

The following update forms the basis of a DIY connection pool. It is intended to allocate an unused session to whoever requests it:


  sess connection_pool.session_name%type;
  update connection_pool
  set    in_use = 'Y',
         last_used = systimestamp
  where  rowid in (
      select rowid 
      from   (
          select rowid
          from   connection_pool
          where  in_use = 'N'
          order  by last_used
      where  rownum = 1
  returning session_name 
  into      sess;
  dbms_output.put_line ( sess );

There’s a serious flaw with this approach however – can you spot it?

Answers in the comments!

Dec 202013
update sqlfail
set    next_post_date = date '2014-01-06';

I’m taking a bit of a break over Christmas while I spend time with my daughter and her grandparents. Thanks to everyone who follows this site, hope you have a great Christmas/festive time and 2014 is everything you hope for. :)

In the meantime, there’s a message hidden in the SQL below for you (note, you’ll need to run this on to guarantee it works):

select listagg(keyword, ' ') 
         within group (
           order by decode(rn, 1, 8,
                               45, 2,
                               476, 5, 
                               766, 6, 
                               1638, 4, 
                               1654, 3, 
                               1683, 1,
                               1836, 7)) 
from (
  select keyword, 
           over (order by keyword) rn
  from   v$reserved_words
where rn in (1,45,476,766,1638,1654,1683,1836);
Oct 072013
  update all_the_cols_in_one_fell_swoop
  set row = a_plsql_table;
"set row" is a handy way to update most/all the columns in the target table easily. 
This is also it's biggest drawback however, as it'll (probably unnecessarily) update 
the primary key too, potentially introducing some locking issues to your application.

For a discussion on some of the issues with using “set row” and how to overcome them, have a read of this oracle-developer.net article.

Feb 182013
update sqlfail_posts
set    weekly_posts = 2,
       monday = 'Y',
       tuesday = 'N',
       wednesday = 'N',
       thursday = 'N',
       friday = 'Y',
       saturday = 'N',
       sunday = 'N';
Recent changes in my life means I don't have as much time and energy to put into this blog as I did, 
so I'm reducing the number of posts slightly.
Jan 182013
update past_memories pm
set    pm.event_details = (
           select fi.event_details
           from   false_information fi
           where  fi.event = pm.event
);I find spotting changes in other people's memories easy to do; 
if only I could notice it so easily when doing so myself...

It’s all too easy to have your memories of an event altered; worryingly you’ll probably never realise this has happened to you!