Certified at last!

Today saw me trundling off to dear old Birmingham for the second time in 2 weeks (the first time being for the UKOUG Tech17 conference) and doing a fair impression of a nervous wreck.

That’s because I was heading to take both the SQL (1z0-061) and PL/SQL (1z0-144) Oracle certification exams.

I don’t like doing exams, mainly because I’m rubbish at revising (seriously; the most revision I did throughout all my university career consisted of (more...)

Dropping constraint… what about the index?

In a recent release that I’ve been working on, we had to drop and recreate some of the unique/primary key constraints (due to columns being added into the constraint) on some of our tables.

Prior to working in this team, I had only ever come across explicitly creating the indexes for a table, similar to:

create table test_explicit_index (col1 number,
                                  col2 varchar2(30),
                                  col3 date);

create unique index col1_exp_pk on test_explicit_index (col1);

We can see the (more...)

Moving from Procedural to Set-Based Thinking

Most people who are new to working with databases tend to think procedurally. That is: I have a table that I need to update, so:

  • Find the first row
  • Update the relevant columns with the relevant values
  • Find the next row
  • Update the relevant columns with the relevant values
  • Find the last row
  • Update the relevant columns with the relevant values

People who are familiar with databases and set-based thinking would simply do:

My Agenda for UKOUG 2012 Conference

It’s a bit late arriving, but this year I couldn’t just do a copy and paste job that would put it into a similar table format as last years, so I’ve had to edit it by hand. *yawn*

Anyway, here it is:


OakTable registration opening times 11:00 – 12:00

12:15 – 13:00 8-bit Oak Roots
Bernie Dugggs
Imagine Software
13:05 – 13:50 DUDE, Where’s My Other Data?
Kurt Van Meerbeeck
13:50 – 14:35 Late Lunch
14:35 – 15:20 Guiding Practices for Gathering Optimiser Statistics (or Not)
Martin Widlake
15:25 – 16:10 Secret Talk About (more...)

The magic of bad design

The other day, I was investigating an issue in our process that takes a list of users and (effectively) merges them into the database. As part of that, I was trying to understand how the whole process worked.

This is the basic structure of the table:

Name          Null?    Type                        
------------- -------- ----------------------------
ID            NOT NULL NUMBER(12)                  
LOGIN_NAME             VARCHAR2(25)                
CREATED                DATE                        
LAST_UPDATED           DATE        

I could see data in the table:

----- ---------- ------------------- -------------------
 1451 user_1451  06/08/2008 00:10:54 10/10/2008 01:01:34
 1452 user_1452  07/08/2008 00:11:18 23/02/2009 15:23:53
 1453 user_1453  12/08/2008 00:09:51 04/07/2012 03:09:08
 1454 user_1454  14/08/2008 00:09:58 10/06/2009  (more...)

Dev or DBA? Where does the responsibility lie…

… which could be the title of a really great post about a big prod issue debriefing, but sadly, all you’re getting is this post instead!

At the UKOUG 2011 conference, I attended a presentation by Michael Salt about indexes. During the course of the presentation, he mentioned something along the lines of “DBA’s should be responsible for indexes (including creating them)” (that’s not a quote; I can’t remember exactly what he said, unfortunately!). I more or less recoiled, because as a database developer, I would expect to be the person worrying about whether I should be creating an (more...)

UKOUG 2011 – Social stuff

I go to the UKOUG conferences to learn stuff from the presentations, but I also go to meet people. If I didn’t really know anyone, I wouldn’t go! (As evidenced by the fact that I didn’t go until 2 years ago, which was not long after I’d met Doug Burns and let him talk me into going! Doug knows *everyone*, I swear!).

Here are the Social highlights of this year’s conference for me (I could detail every meeting, but even I would get bored of reading all that! Needless to say, everyone I met was brilliant, and the discussions (more...)

UKOUG2011 – Day 3 (Wednesday)

After a late night (um, < 4 hours sleep...) I was up bright and... well, just early now I come to think of it! I checked out, retrieved the coat (phew!) I hadn't realised I'd left in the hotel bar until just before I was about to leave the hotel room for the final time and had breakfast.

Then it was off to the first presentation of the day...

Statistics On Partitioned Objects – Doug Burns

I have a feeling that I’ve seen this presentation before – it’s possible that I haven’t seen the entire presentation, but I believe Doug did a couple of work presentations around this subject. Anyway, whether it was those or his blog entries on the subject, a lot of it felt familiar. Doug is an engaging speaker, and the subject is an interesting one. I’m not sure whether it’s applicable to the way our databases are designed, but (more...)

UKOUG2011 – Day 2 (Tuesday)

So, Tuesday dawned bright and … er, well I didn’t go in for the first session of the day (not that I’d found one I wanted to go in for anyway, so nothing lost there!) but I was definitely there for the second session!

Who’s Afraid Of Analytic Functions? – Alex Nuijten

Alex is someone I’ve known from the OTN SQL & PL/SQL forum for quite some time now, although I’ve only met him in person a couple of times now. He’s a massive proponent of analytic functions (as am I – seriously, they *rock*!) and I couldn’t (more...)

UKOUG2011 – Day 1 (Monday)

Opening Keynote – Mark Sunday

Before Mark came on, we had Debra Lilley and Lisa Dobson give a short talk about the changes in structure in the UKOUG organisation, which was interesting – I wasn’t aware that there’d been such big changes.

Mark came on and gave a talk about how fab Oracle was, and … well, that was my take-home message *{;-) I suspect I may not have been part of the ideal target audience!

SQL Tuning – Kyle Hailey

I’ve seen a couple of presentations in previous UKOUG conferences that were similar to what Kyle presented here – (more...)