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:

declare

  sess connection_pool.session_name%type;
  
begin
  
  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 );
  
end;
/

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 11.2.0.2 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, 
         row_number() 
           over (order by keyword) rn
  from   v$reserved_words
)
where rn in (1,45,476,766,1638,1654,1683,1836);
Oct 072013
 
begin
  update all_the_cols_in_one_fell_swoop
  set row = a_plsql_table;
end;
/
"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!