Home › Category Archives › Uncategorized

UKOUG TEBS Conference 2010 – Monster update post!

boneist April 13, 2011 Uncategorized 1

It’s been a while since I wrote in my blog, sorry! (My hobby of cross-stitching took over when I was stitching a sampler for the birth of my first nephew and hasn’t really stopped! I know, excuses excuses…)

Anyway, I went along to the UKOUG TEBS Conference 2010 and I really enjoyed it. Here is a brief summary of what I remember (which in itself will be a good test of how well a) I listened and b) the quality of the presentations!)

Day 0

I turned up in Birmingham on Sunday evening, which was later than I had planned, due to having to work on the Sunday (big migration project; I think I’ll blog more on this later!).

I eventually managed to finish working just by the deadline I set myself, and hopped on the train. Thankfully there was no snow where I live, so all the trains were still running! This was sadly not the case for lots of other attendees, who had to battle severe transport issues in order to make the journey.

Once I’d sorted myself out at the hotel, Lisa Dobson and Niall Litchfield came and collected me and took me to a pub somewhere in the Food/drink court area just over the canal from the ICC. I’d never properly met either Lisa or Niall before, but Twitter has a lot to answer for *{;-)

I was fairly shy and didn’t mingle at all, but fortunately I knew several people who came over to chat, so that was nice! What I like most about these sorts of events is that people are really friendly and happy to chat to anybody, even though they might not know them. Very inclusive, even though I was sort of gatecrashing! *{;-)

Day 1

I started off by attending Tom Kyte’s “What’s New in Oracle Database Application Development” session, which was interesting as always. Judging from the lack of notes I took, I must have known about most of the things he mentioned – thankfully, he’s an engaging speaker, so this was certainly not a problem!

He even seemed to have taken on board one of my complaints from last year – there was a lot less glaring red in his presentations (lots of bright red areas in last year’s slides did unpleasant things to my eyes!). Since I didn’t complain directly to him, I can only assume that I wasn’t the only one who had a problem, or he decided on a style change (probably the latter!).

Next came Marco Gralike’s “XML Indexing Strategies – Choosing the right index for the right job” session. I was interested in this one because we use XMLTYPE columns in one of the databases I’ve taken over the maintenance of in the past year, and I’m not too au fait with XML!

I didn’t really understand that much of the presentation, as a lot was geared towards 11g XMLTYPE options, plus my general ignorance of XML, but I did scribble down a lot of notes, and at least I’m aware that there are various options out there when we finally move to 11g (we only just moved from 9i to 10g on some of our databases!).

I have a feeling that this is the kind of thing that will slot into place the more I learn of the subject, and I may well have “Ahhhh, so *that’s* what Marco meant!” moments in time to come!

I had a choice to make with the next session – Graham Wood’s “Oracle ‘Hidden’ Features: Features You Probably Didn’t Know About and are FREE” or Jonathan Lewis’s “The beginners’ guide to becoming an expert”. Tough decision, but eventually I went with Jonathan.

Again, I didn’t take many notes, which, if I recall correctly, was mainly because Jonathan had SQL heavy slides and was talking about various ways of improving performance.

At the end of Jonathan’s session (at least, I think it was this session!), Andrew Clarke (aka APC) somehow recognised me and introduced himself to me (he was behind me – I recall Rob van Wijk doing the same thing to me the previous year, coincidentally in the same hall! I’m not sure what that says about me…! *{;-) ). Andrew is someone I know from the OTN Forums, so it was a pleasure to be able to put a name to the face (although sadly, I’m not sure I’d recognise him if I met him again, stupid memory! *slaps side of head*).

After lunch, I went to see Alex Nuijten’s “Analytic Functions Revisited”, which I thought was an excellent presentation, especially if you weren’t already aware of them. Seeing as Alex is another OTN Forum regular and I was feeling brave, I decided to hang around afterwards to introduce myself. I was glad I did; he’s a thoroughly decent chap *{:-D

My last session of the day was Cary Millsap’s “Oracle Extended SQL Trace Data for Developers and Performance Analysts”. I managed to make copious notes for this one, which I found interesting and informative. I meant to go back to work and immediately make use of some of the tools that were mentioned, but, um, I still haven’t got round to doing that yet!

After hanging around for a while, I decided that I had to go back to my hotel as I wasn’t feeling well (bah, why must this be part of my conference experiences?!) so I sadly missed the UKOUG Pantomime, and missed seeing Doug Burn’s Movember shave off. I also had to log into work and catch up / fix some bugs.

Day 2

I woke up early on the Tuesday, and thankfully felt much better. I headed down to see Martin Widlake’s presentation, which had a start time of 8.45am. Sadly, Martin was late, and consequently was not at his best. I thought the presentation content was excellent, so it was a shame that Martin wasn’t able to present it at his best.

After Martin’s session (and a bit of commiseration), I went to see Tom Kyte’s “What do I really need to know when upgrading?” session. Seeing as I was right in the middle of a project to migrate from 9i Linux to 10g Solaris, I thought this would be useful. Unfortunately, Tom was discussing 11g, so it wasn’t ultimately useful for my project, but I did glean a lot of information that will be useful when we do eventually move to 11g.

After lunch, I headed to Jonathan Lewis’s “Co-operating with the database” presentation, where I was a little early. Whilst I was waiting around, I got a tap on the shoulder, which turned out to be the wife of an ex-colleague of mine. How on earth she recognised me, I have *no* idea! I couldn’t have done that if you paid me, but apparently she’d seen my photo on my facebook page, as her husband is a friend of mine there. Mucho bizarro!

Jonathan’s session was interesting and covered Bind variables, histograms, partitioned tables and adaptive cursor sharing, as well as examples. Most of which, I already knew, but he has a knack of explaining things very clearly.

After this, I headed off to see Robyn Sands’ presentation on Instrumentation. This was very clearly explained, and had information about some tools which, again, I meant to research but…

It was at the end of this session that Niall Litchfield spotted me – I was sat on my own a couple of rows back from the front of the stage so that I could see the slides properly – and he convinced me to join him and others at the front of the stage. I felt like I was surrounded by royalty, as I was now sat with what I would term “famous” (at least in the Oracle community) people. I know Niall will probably pshaw this, but that’s how I felt (and would still feel!).

The next session was Graham Wood’s “How to build and unscalable system”, which took us through some of the common problems that cause unscalable systems. He was very clear and easy to listen to.

Next was Randalf Geist’s live troubleshooting session (part 1), which took us through some problems and what you would do to investigate/fix them. Sadly, I found this to be quite a “dry” presentation, and by the end of it, my brain was somewhat fried – conference overload!

Afterwards, Niall, Doug Burns, Martin Widlake and I headed down into Birmingham to a pub (quelle surpise, huh?!) where I think we bumped into someone whose name I can’t recall (Rob?). An entertaining evening was had, but sadly I had to leave early so that I could log into work (boo!!).

Day 3

My first session of the day was Mark McGill’s “Common Causes of SQL Performance Regression and their Remedies”. Once again, this was something that I already knew quite a bit about, but I found it useful as it consolidated my knowledge and gave me a list of things to think about when I next come across a problem SQL statement

After that, I went to a very entertaining debate between Jonathan Lewis and Tony Hasler on “Does Oracle Ignore Hints?”. Jonathan was arguing that no, Oracle doesn’t ignore hints and Tony was arguing the opposite.

I have to say that I was firmly on the side of Jonathan throughout the entire debate, but Tony did come up with some interesting situations for Jonathan to debunk. I thoroughly enjoyed the light hearted banter that took place throughout!

At lunchtime, I met up (finally!) with Ben “Munky” Burrell, another OTN forum regular. At some point during the proceedings, Alex N and Rob vW also came and joined us – I definitely think that we could set up our own “OTN SQL & PL/SQL forum” table at the next UKOUG Tebs conference and take on SQL and PL/SQL questions that people might have!

My final session of the day was Jason Arneil’s “Taking .uk all the way to 11: A migration war story”. I think this was one of my favourite sessions of the conference – he took us through the steps taken to upgrade Nominet’s databases to 11g (testing, testing and yet more testing!), and told it in an entertaining style. I thought this was the perfect wind-down to the conference.

To summarise my experience of the 2010 UKOUG Tebs Conference:

  • I’d sorted out my glasses beforehand – I was better able to see the slides without straining my eyes this time!
  • I knew more people this time, which helped me feel more confident and enabled me to introduce myself to yet more people.
  • I made sure I didn’t overload myself with presentations – saw quite a few the first day, but less on the other two days.
  • I came prepared to learn a *lot*!
  • I discovered that I’m really recognisable to people who’ve never met me before.
  • I had fun!

Replacing a string within a column

boneist April 22, 2010 Uncategorized 1

@rnm1978 asked an interesting question over Twitter, along the lines of:

“My column has “YYYY / MM” in it – how can I amend the column so that the date is in “PMM-YY” format? eg. ‘Here is some text 2010 / 04 and more text’ becomes ‘Here is some text P04-10 and more text’.”

REGEXP_REPLACE to the rescue!

SQL> with mt as (select 'Thisismy string 2010 / 04 and there''s more over here' col1 from dual union all
  2              select 'Just plain vanilla string' col1 from dual union all
  3              select 'Have some 2009 / 02 more text here' col1 from dual)
  4  select col1, regexp_replace(col1, '([[:digit:]]{2})([[:digit:]]{2}) / ([[:digit:]]{2})',
  5                                    'P\3-\2') col1a
  6  from   mt;

COL1                                                    COL1A
------------------------------------------------------- --------------------------------------------------
Thisismy string 2010 / 04 and there's more over here    Thisismy string P04-10 and there's more over here
Just plain vanilla string                               Just plain vanilla string
Have some 2009 / 02 more text here                      Have some P02-09 more text here

A dedicated follower of Tom?

boneist March 26, 2010 Uncategorized 2

Back when I first became an Oracle developer some 5 years ago (that long? Wow, and I still feel like a newbie at times!), I read AskTom daily. It was pretty much my only online source of information (apart from Google and the documentation, of course!).

When I moved to my current job 3 years ago, I discovered people were writing blogs about Oracle. I started reading those, as well as starting to help out on the OTN Forums. My reading of AskTom declined sharply over the first couple of weeks or so I was here (sorry Tom!). Don’t get me wrong, it’s still a fabulous site and I refer to it often, I just don’t sit and wait for the nuggets of information to appear as obsessively as I used to.

Of course, over that time I have absorbed a lot of Tom’s teaching, not only through his site and blog, but also because a lot of my fellow forum contributers and regular bloggers are advocates of Tom!

Today, Doug said to me (in the midst of some Friday afternoon bantering), “By the way, you might think I’m joking, but you *reek* follower-of-Tom”. I know he didn’t mean it as a compliment (probably something about lack of originality or summat!), but I took it as one anyway.

I have my own thoughts and opinions, and – gasp – I don’t always agree with Tom but, contrary to some people’s opinion, the reason I seem to be so pro-Tom is because my experience has shown him to be correct time and time again. Mind you, Tom isn’t the only one to have helped mould my views on Oracle, SQL and PL/SQL (eg. Jonathan Lewis, Doug, Billy Verreynne and BluShadow to name but a few) but it could perhaps be argued that he has the most catch-phrases *{;-)

Oracle Tips and Tricks – Part 2: Analytic Functions

boneist February 25, 2010 Uncategorized 2

As Tom Kyte has often said, Analytic functions Rock and Roll!

Back when I first came across them just over 3 years ago, I thought they were the most confusing things ever to have been invented – I just could *not* get my head around the syntax. It wasn’t until I started answering questions at the OTN SQL and PL/SQL forum based on people’s real-life sample data and requirements that I started to make sense of it all.

So, firstly, what is an analytic function and what makes it different to an aggregate function? An aggregate function, such as min() or count(), produces a summary of the rows being worked on. An analytic fuction allows you to access information from other rows within the current row. It does not produce a summary – if you had 5 rows in your table, using an analytic function to find the count of all the rows would give you 5 rows back, whereas the aggregate version of sum would give you just 1.

Eg: With analytics:

SQL> with my_tab as (select 1 col1 from dual union all
  2                  select 2 col1 from dual union all
  3                  select 3 col1 from dual union all
  4                  select 4 col1 from dual union all
  5                  select 5 col1 from dual)
  6  select col1,
  7         sum(col1) over () sum_col1
  8  from   my_tab
  9  /

      COL1   SUM_COL1
---------- ----------
         1         15
         2         15
         3         15
         4         15
         5         15

With aggregate:

SQL> with my_tab as (select 1 col1 from dual union all
  2                  select 2 col1 from dual union all
  3                  select 3 col1 from dual union all
  4                  select 4 col1 from dual union all
  5                  select 5 col1 from dual)
  6  select sum(col1)
  7  from   my_tab;

 SUM(COL1)
----------
        15

You can see there that both both functions correctly gave the sum of 1 to 5 as 15, but with the analtyic function, you could access each row in the table. This allows you to do such things as “What is the difference between each col1 and the sum of the total?” without having to query the table again:

With analytics:

SQL> with my_tab as (select 1 col1 from dual union all
  2                  select 2 col1 from dual union all
  3                  select 3 col1 from dual union all
  4                  select 4 col1 from dual union all
  5                  select 5 col1 from dual)
  6  select col1,
  7         sum(col1) over () sum_col1,
  8         sum(col1) over () - col1 diff
  9  from   my_tab;

      COL1   SUM_COL1       DIFF
---------- ---------- ----------
         1         15         14
         2         15         13
         3         15         12
         4         15         11
         5         15         10

With aggregate:

SQL> with my_tab as (select 1 col1 from dual union all
  2                  select 2 col1 from dual union all
  3                  select 3 col1 from dual union all
  4                  select 4 col1 from dual union all
  5                  select 5 col1 from dual),
  6       sum_mt as (select sum(col1) sum_col1
  7                  from   my_tab)
  8  select mt.col1,
  9         smt.sum_col1,
 10         smt.sum_col1 - mt.col1 diff
 11  from   my_tab mt,
 12         sum_mt smt;

      COL1   SUM_COL1       DIFF
---------- ---------- ----------
         1         15         14
         2         15         13
         3         15         12
         4         15         11
         5         15         10

I won’t be going over each of the analytic functions – you could always visit the documentation to see what functions there are, but I did want to go through the syntax to try and make it a little easier to understand.

To do this, I am assuming that you, the reader, is familiar with the syntax of aggregate functions.

The basic structure of an analytic function is:

function_name(arguments) over (partition by columns order by columns)

The partition by clause is similar to the group by clause of an aggregate function. It simply says “I want the function to be done on rows partitioned (or grouped) by these columns”.
The order by clause simply tells the analytic function the order it is meant to process the rows within the groups mentioned in the partition by clause (if one is specified). The order of the data makes a great deal of difference to the results of the function, for example, if you wanted a running total. More on this later.

You can have an analytic function without a partition by or an order by clause – this simply says “apply across all the rows in the table at once”.
You can just have the partition by clause – “apply across all the rows in each group at once”.
You can just have the order by clause – “apply across all the rows in the table in this order”.
You can have both partition and order by clause – “apply across the rows in each group in this order”.

Eg.

with my_tab as (select 1 id, 10 value from dual union all
                select 1 id, 20 value from dual union all
                select 1 id, 30 value from dual union all
                select 2 id, 5 value from dual union all
                select 2 id, 15 value from dual union all
                select 3 id, 2 value from dual)
select id,
       value,
       sum(value) over (order by id, value) running_tot_id_asc,
       sum(value) over (order by id desc, value) running_tot_id_desc,
       sum(value) over (partition by id) tot_by_id,
       sum(value) over (partition by id order by value) running_tot_by_id1,
       sum(value) over (partition by id order by value desc) running_tot_by_id2,
       sum(value) over () total_value
from   my_tab;

        ID      VALUE RUNNING_TOT_ID_ASC RUNNING_TOT_ID_DESC  TOT_BY_ID RUNNING_TOT_BY_ID1 RUNNING_TOT_BY_ID2 TOTAL_VALUE
---------- ---------- ------------------ ------------------- ---------- ------------------ ------------------ -----------
         1         10                 10                  32         60                 10                 60          82
         1         20                 30                  52         60                 30                 50          82
         1         30                 60                  82         60                 60                 30          82
         2          5                 65                   7         20                  5                 20          82
         2         15                 80                  22         20                 20                 15          82
         3          2                 82                   2          2                  2                  2          82

6 rows selected.

You can hopefully see the difference that a) the inclusion/exclusion of the partition by / order by clauses affects the results and b) the final ordering of the results has on how the running totals are displayed. In reality, you wouldn’t calculate running totals with different ordering to how the final output would be output, I’ve just done that here for demonstration purposes.

I have used them to answer questions such as:

  • What is the difference between the value in the previous row and in the current row?
  • How can I retrieve rows from table2 that have the exact set of rows in table1 per some join conditions?
  • How can I generate a running sequence over each group of rows in my resultset?

    • etc…

In short, if you’re not familiar with analytic functions yet, you would be wise to research and play around with them. If aggregate functions are the ratchet screwdrivers of the SQL toolbox, then analytic functions are the cordless power screwdrivers!

Oracle Tips and Tricks – Part 1 in a series (I hope!)

boneist February 3, 2010 Uncategorized 0

Over the past couple of years or so, I’ve been hanging round the OTN SQL and PL/SQL forum, trying to help out where I can. Originally, I started doing this as a way of gaining experience of different “real world” examples, so that I could consolidate my knowledge (such as how to work with analytic functions!) and also expose myself to a lot of different issues. And also, where possible, to help people!

Since then, I’ve learnt much more than I could ever have hoped to, from various tips and tricks that I use often, to things that I haven’t yet needed to use in my day-to-day job. Of this latter group, there are things that I haven’t got the set up or access to try out, but even though I haven’t used them, I *know* that they exist and can refer back to them if I ever do come across a situation that requires them.

I cannot recommend enough to people that if you want to consolidate your SQL and PL/SQL knowledge and improve your skills, start helping out on the OTN SQL and PL/SQL forum!

Anyway, I thought that I would start writing about some of the things that I have picked up from my fellow forum helpers (they know who they are! *waves*) in the hopes that more people will come across them.

Subquery Factoring

Subquery factoring has been around since Oracle 9i, and is an incredibly useful tool. This is one thing that I picked up from the forums that I use at least once every day whilst at work!

In essence, it’s a way of giving each subquery you use within a SELECT statement a name and giving the scope of that subquery to the whole query. Whilst you can name inline views, you have also restricted the scope of that subquery to the bit of the query that it’s in.

The format looks like:

with subq1 as (select ....),
     subq2 as (select ....),
     ...
     subqn as (select ....)
select ...
from   subq1,
       subq2,
       ...
       subqn
where  ....;

You can have one or multiple subqueries defined, and you can also refer to previous subqueries within later subqueries (eg. subq2 could select from subq1, etc). All subqueries must be selected from at least one somewhere in the overall query.

The beauty of subquery factoring is that if you have a subquery repeated throughout a query, like:

select 'first_query' name_col, col1, col3
from   (select col1, col2, sum(col3) col3
        from   a_table)
where  col2 != 'hello'
union all
select 'second_query' name_col, col1, col3
from   (select col1, col2, sum(col3) col3
        from   a_table)
where  col2 = 'hello';

where the subquery gets run twice, when you factor it out, it only gets run once and then accessed twice:

with my_sub_query as (select col1, col2, sum(col3) col3
                      from   a_table)
select 'first_query' name_col, col1, col3
from   my_sub_query
union all
select 'second_query' name_col, col1, col3
from   my_sub_query;

Oracle will quite happily create a Global Temporary Table behind the scenes to hold the data from the subquery if it thinks it is beneficial to do so.

As well as performance benefits, it helps to make the query easier to read by modularising the SQL statement. And as we all know, modularising your code and making it resuable is Good Practice (TM)!

As well as all those benefits, subquery factoring has become very popular as a means of simulating a table with data, like so:

with my_tab as (select 1 col1, 'A' col2, 'hi' col3 from dual union all
                select 2 col1, 'B' col2, 'bye' col3 from dual)
select *
from   my_tab;

This comes in very, very handy when needing to set up test cases that other people can run, as there’s no longer a need for them to create a test table – the data’s already there!

UKOUG Tech and EBS Conference – what have I learnt for next time?

boneist December 6, 2009 Uncategorized 0

So, UKOUG Tech and EBS 2009 Conference was the first conference I’ve ever been to. I headed out to it full of nerves over whether I’d get lost, have the right materials to take notes with, etc, so I thought I’d write a list of things I’d do differently next year. It may come in handy for next year’s first-timers, who knows?!

So, for my next UKOUG conference, I will:

  • make plans as to when to meet up with people I know, if they’re also going to be attending, especially for lunch and in the evenings
  • compare agendas with said people
  • talk to my optician re. what I can do to make it easier to see the slides; different glasses just for the long distance viewing, maybe?
  • make sure I have plenty of ibuprofen and paracetamol at hand, just in case!
  • pick up the conference goody-bag prior to my first presentation – do this by making sure you go round the entire exhibition hall, as this time, they were handed out at the very back!
  • not worry about having to buy a separate notebook; one is included in the conference goody-bag
  • try not to fill every available slot with a presentation; it will be information overload!
  • try to get enough sleep!
  • be prepared to be overwhelmed and try to take it in step when that happens
  • make much more effort to overcome my shyness and *talk* to more people, and especially the people I have talked to online that I really want to say hi to!

Things that I already did which turned out to be really good ideas:

  • wear comfortable shoes
  • read (and comment on!) lots of blogs throughout the year from different people (to a) gain a wider perspective and b) get familiar with the wider Oracle community)
  • become friends with someone who has been to previous conferences and knows a few of the regular attendees
  • come prepared to learn
  • follow the signs to the halls and pay attention to the most excellent and helpful ICC staff!
  • explore the area before the conference starts
  • Twitter like mad and use the appropriate hash tags! This is a useful means of introducing yourself to other conference attendees and also the wider Oracle community *{;-)
  • work out what sessions you’re going to attend prior to the conference start; having a plan of action for the day in advance helps a lot!
  • be prepared to enjoy yourself!

All in all, I had a great time and I learnt a lot. I also met lots of people that I’d never dreamed I would actually meet, and hopefully have begun broadening my network of contacts!

I would wholly recommend attending the UKOUG Tech and EBS Conference, and would happily go again next year, I get the opportunity to go again!

UKOUG Tech and EBS Conference – the writeup, part 3! (Wednesday)

boneist December 6, 2009 Uncategorized 2

Wednesday – #ukoug_tebs Day 3
So, what with Tuesday evening’s shennanigans, I decided to give the first presentation of Wednesday (Joze Senegacnik’s “Execution Plan Stability in Oracle 11g”) a miss, and get more of a lie in. As I had packed before bed, there wasn’t much to do after I’d got up (there was method to my madness after all!), so more time in bed! *{;-)

Randolf Geist – Everything you always wanted to knw about FIRST_ROWS_N but were afraid to ask
Randolf is another person that I know and respect from the OTN SQL and PL/SQL forum, and I had been looking forward to attending this presentation ever since I’d booked to come to the conference.

I’ve never really used the ALL/FIRST_ROWS hint before, although obviously I knew about their existence beforehand; I’ve never needed to do Top-N or pagination queries before, and ALL_ROWS (the default) has always been sufficient for me, especially since I’ve usually needed… all the rows!

Randolf stated right at the beginning that he’d intended to reveal everything about how the ALL_ROWS_N hint/optimizer mode worked, but that he’d had problems with the results, which caused problems with his theories. I thought this was a fairly odd way to start the presentation, but after a while, it became clear that this was still clearly an area that Randolf was still experimenting with, to fine tune his theories.

I found it a fascinating insight into how one might go about working out what the database internals are actually doing, as well as learning more about the ALL_ROWS_N hint/mode. I did want to go over to introduce myself afterwards but unfortunately someone else had already had a similar idea, and I didn’t like to barge in. I should’ve gone up and stood waiting (hind-sight, oh how you’re 20-20!); how much does it cost to just say “Hi, I’m Boneist from the forums, just wanted to say ‘hi’ and great presentation!”?! D’oh. Next time, definitely.

Rob van Wijk – All about grouping
Poor Rob had developed a cold by the time he came to present *{:-( However, he managed not to sniffle at all (impressive!) despite sounding more and more full of cold as the presentation went on! My heart went out to him and I kept wanting to hand him a tissue, just in case he needed one!

The presentation was informative, and I’ve picked up a lot about how the grouping sets, cubes and rollups work as well as how they interact. Sounds like the optimizer still has a way to go on working out ways to identify cubes, given a mix of grouping sets, rollups etc. This was one of the presentations I was really looking forwards to, as I had always been confused about the extended grouping stuff. However, work wanted me to see if it was possible to do stuff in “my” database that currently gets done in another one further downstream in the lifecycle of our data, so I’d had to work out myself what the grouping sets, rollups etc is about. So, I knew a bit more about them than when I had originally booked to come to the conference.

The presentation clarified things for me, and I now feel confident in knowing how to use and manipulate grouping sets/rollups/cubes etc to try and get best performance out of them. Thanks Rob!

Again, Rob had people talking to him at the end of his presentation, so I left without talking to him *{:-( Still, at least we had met, so that wasn’t quite as bad as it could have been, but even so…

Wolfgang Breitling – Anatomy of a SQL Tuning session
I actually met Wolfgang at the Fire party on the Tuesday night, and he seemed like a really nice, down-to-earth kinda guy, although perhaps a little insulted that I’d only managed to skim read the paper of his that Doug had sent me a link to! (I didn’t have time to read it fully, but skim read it so I at least know to go should I need to use that information!) I’m not sure I managed to dig myself out of that particular hole, though. *{;-) I’d had this session in my agenda for a long while, so I wasn’t attending it just to make it up for my boo-boo of the previous night!

It took me a little while to click that we were taking a SQL statement that he had tuned and were going to run through what steps he had done (um, I blame my stupid headache for that!), but once it had, I sat back and enjoyed his demonstration. Feedback by cardinality; which sort of ties back to what Jonathan Lewis did in his presentation on Monday (“Writing Optimal SQL”) – I think a combination of using pictures to diagram the query plus this feedback by cardinality is a good place to start when trying to tune SQL statements; I shall certainly be using both methods to try and do that from now on.

I really enjoyed the session, even though the last step Wolfgang did was to use quite a specific set of hints to force the behaviour he wanted! Whilst I know that some hints are good if you know more about the data than the optimizer does (eg all_rows), I disagree with trying to force production code down one particular path, based on information that’s “correct” as of today – I think all that does is postpone the problem until tomorrow/next month/next year (delete as applicable).

However, I do understand the limits we all have to work under (upset users, irate managers, no time for investiaging proper fixes, etc, etc) and I guess hints really are the quick fix, albeit likely temporary!

The End
And that – as they say – is all, folks! The end of the conference!

I headed off back home, sad that it was all over, but glad to be going home and meeting up with my partner. Less happy to be heading back to work the following day, it has to be said! My 6am alarm was rather more of a shock than normal!

UKOUG Tech and EBS Conference – the writeup, part 2! (Tuesday)

boneist December 5, 2009 Uncategorized 6

Tuesday – #ukoug_tebs Day 2
Despite having promised myself an early night on the Monday night, it was quite late when I finally went to sleep, so I decided that I would give Bob Mycroft’s “Getting connected!” a miss and take advantage of a bit of a lie in, although in the end, I didn’t lie-in for long!

Tom Kyte – All about metadata; why telling the database about your schema matters
I found myself nodding a lot to what Tom was saying, as well as despairing at the 3rd party database we have <whisper>that doesn’t have any foreign keys</whisper>. Most of this presentation was reinforcing things that I’d hope most if not all database designers/developers/dba should already know, but I know from experience on the OTN SQL and PL/SQL forum that people consistently use the wrong datatypes (particularly dates; people seem to hate using the DATE datatype for some reason!).

One thing that was mostly new was the Dimensions thing. Whilst I do vaguely touch a snowflake schema in one of my databases, it really isn’t a datawarehouse, so I haven’t really needed to dig all that deeply into tuning it. But I now understand the Dimensions constraint thing better than I did, and it’s now there in my mental toolbox *{:-)

Connor McDonald – 11g features for Developers
Wow! This was incredibly faced paced and packed with humour! I’ve no idea how he managed it, given that he was also jetlagged! I really enjoyed this presentation, even though Connor thought it wasn’t all that great (?!?!), and I had trouble writing everything down in my notes, there was so much stuff!

Lots of things that I wasn’t aware of in 11g that sound like they’ll come in useful (eg. error logging in scripts; now that does sound useful… plus he mentioned 10g there, so I shall investigate when I get a moment!) and some that I already knew about (eg. virtual columns). Good stuff!

I’d definitely go watch Connor present again, even if it was in a subject area I had no interest in; he was amazing!

Afterwards, when I came out of the presentation, Rob van Wijk introduced himself to me, so I can at least say that I did meet one of the people I had really, really wanted to meet at UKOUG, albeit that I was a bit shellshocked from both presentations of the morning so wasn’t exactly at my best *{:-( I didn’t talk to him for as long as I would have liked, but I now have a face to go along with the name! (Rob is one of the people on the OTN SQL and PL/SQL forum that I have huge respect for and he always gives well thought-out answers and explanations. I am kicking myself that I didn’t chat longer with him *{:-( )

another pause in the proceedings
I was meant to be going to Jonathan Lewis’ “Introducing Partitions” but I gave this a miss, due to feeling information overloaded and also my eyes needed a break. So I headed to the lounge and pootled around for a while, having lunch etc, until the next presentation.

Julian Dyke – Vital Statistics
I have to say, I was disappointed by this presentation. I’d heard that Julian was a pretty good presenter, and I went along, expecting to learn a lot more about statistics than I already knew.

Unfortunately, what I got was something that more or less walked through the documentation, something that I have already done for myself. I was hoping for something that extended what the documents said and gave it context and meaning, but that was sadly lacking. Perhaps my fault for not having read the abstract clearly enough.

I did learn one thing, though, so not a total waste of time – copy stats. How on earth I’ve missed this procedure is anyone’s guess, but it will make copying stats from one partition to another easier than what I do at the moment (export the stats, faff around changing things in the stats table, reimport the stats to the intended partition)!

Chris Dunscombe – RAC – What’s the difference?
This was my last presentation of the day, and I was in two minds as to whether to stay for it or not, as I knew it was oriented towards DBAs, which I am most assuredly not *{;-) There was nothing else I wanted to see, and I had picked this one because it sounded like it might give me a basic idea of what’s involved in setting up RAC, should I ever become involved with working with RAC databases. In the end, I decided to stay (and no, the offer of chocolate that Chris gave us was not really an incentive for me to stay *{;-) ).

All I can say is, wow! I’m *so* glad I’m not a DBA; there’s a lot of server and storage considerations etc, and really, my brain is not geared for that territory! Still, I was glad that I stayed for this, as it did give me an overview of the kinds of things that would need to be thought about, and will allow me to help give the DBAs the right kinds of information if I’m ever involved in setting one up (from a development point of view, obviously!)

It was a good presentation – Chris managed to get people interacting, providing information etc which is not bad for that time of the day! I liked that Chris was able to take new ideas on board and run with that right in the middle of his presentation!

The Fire and Ice parties
So, after briefly (and I do mean that!) heading in to the meet the speakers bit and not seeing anyone I could talk to (only recognised Jonathan Lewis, and I really didn’t feel like I could just barge up and say “Hi! I think you’re fab!” when he was already in the middle of talking to someone else…) I went and hid for a while. I popped my head round the doors for the ice party, didn’t see anyone (again other than JL) that I recognised, felt sorry for the ladies stuck in the middle of tables, dressed up like loo roll covers and headed off to the fire party.

This had scalextric, pool tables, table football and random jugglers (who were *amazing*… I can just about manage 3 balls in a basic pattern for about 30 seconds, but they were making the balls do incredible things and just kept on going!), and after having a drink and wandering round watching people playing, I gave up and headed out into the foyer bit for the internet suite.

They had notebooks or whatever they’re called, and my goodness the writing was tiny! I ended up hunched over and peering at the screen (must have looked really odd!) just to be able to read the screen properly! Anyway, just before I was going to call it a day (it was cold in the foyer, brrr!), Doug showed up after having had a snooze. So I stuck like a limpet to his side for the rest of the evening and managed to meet and chat to loooooads of people! At one point, I was in a pub surrounded by all these big names of the Oracle community! Little ol’ *me*?!?!

Highlights were doing a spot of not-being-able-to-find-one-good-thing-to-say about a certain someone with a whole crowd of people, talking to Chris Dunscombe re. his presentation and how I got loads out of it (along with other things), meeting Alex Gorbachev after having bantered with him over Twitter and oooh, just about everything, actually!!

I bowed out of the pub early (at 1.15am?!) and headed back to my hotel, where I realised I needed to pack. Ended up procrastinating and didn’t go to sleep until about 2.30am! Whoops…

UKOUG Tech and EBS Conference – the writeup, part 1!

boneist December 3, 2009 Uncategorized 0

Sunday – arrival in Birmingham
After visiting the not-quite-in-laws for the weekend (and helping out – in the kitchen – at this year’s Shrewsbury Model Railway exhibition that my Great-Uncle Jeff started over 25 years ago. Sadly he died earlier this year, and so this year’s event was held in his memory), I arrived in Birmingham.

Driving in Birmingham is as bad as driving in Leeds! Thankfully, my other half was driving, but even so, the exits to take weren’t clear on the sat nav until, typically, we’d gone past! Thankfully, we only missed one junction, but there were some hairy moments as we tried to work out which lane we should be in, etc! T’other ‘alf deserves a medal! Anyway, once I’d been dropped off at the hotel, I decided to go exploing.

The hotel I stayed at (Copthorne; would recommend – very handy for food, German market and the ICC!) was very central; I discovered a cut through and ended up just outside the Paradise Forum. Intending to find
the ICC first, I decided to go through the, um, “forum” and when I got to the other side, the German market … distracted me somewhat! So, with some Chrstmas shopping done, I decided to give up my quest for things, and decided I’d check my iPhone to see if I could navigate myself to the ICC. I eventually got there, but the long way round (typical!) – it turned out that if I’d headed away from the “forum” towards the giant lit-up Ferris wheel, I’d have soon found the ICC!

Once I was happy with knowing where to go in the ICC, I headed back to the hotel and, along the way, found yet more German market stalls (including one with puzzles… I ended up buying 4!).

Monday – #ukoug_tebs Day 1

I made sure I got to the ICC in plenty of time to register before the First Timer’s Briefing, and whilst waiting for that to start, I went round the exhibition. I was too shy to actually stop and talk to people, plus I hate being pestered by random sales guys when I’m just browsing, so having not found anything that I felt I could legitimately talk about with the vendors, I just wandered past each stall quickly! *{;-)

First Timer’s Briefing
The first timer’s briefing was ok; telling us about the ICC, the staff, wear comfortable shoes, etc, etc and mentioned the free bag with agenda guide and other goodies that I’d somehow missed from the exhibition hall! So, when the session finished early, I headed straight back to pick up my bag before heading off to my first ever conference session – the keynote by David Callaghan.

David Callaghan Keynote
This was in hall 1 (biiiiiig lecture theatre stylee) and was quite full. We had a talk by the UKOUG chairman, Ronan, and someone whose name I forget (sorry, sorry) – Deborah, I think?. This was quite entertaining and light-hearted and went on for about 20 mins or so, before David was introduced.

David talked for a while about how the recession was affecting things, etc, and (should I really admit this?) I found it a bit dry and boring, and despite trying to listen, kept finding myself drifting off onto other thoughts (what’ll I have for dinner? when’s Tom going to appear? etc etc). I never have been too good at wading through all the marketing spiel *{;-) Aaaanyway, Ronan came back on after David had finished and there was a bit more banter, before Tom Kyte was introduced.

Tom Kyte Keynote – What are we still doing wrong?
Unfortunately, there was no break between the end of the keynote and Tom’s session, so people were forced to just get up and leave whilst Tom was standing at the podium. A bit distracting for him, I’m sure, although I know that Ronan has already taken this on board and I’m sure it’ll be done differently next time! Tom’s session was about things that we as developers are still doing wrong – it was thoroughly entertaining and useful (and yes, I had a few guilty winces…).

I could have sat and listened to Tom talk for longer, and indeed, it seemed like he had more to say, but unfortunately ran out of time. The only negative thing I would say about Tom’s presentation is that there was too much RED in the slides; it made my eyes hurt! (And, to be honest, my eyes were struggling with seeing the slides, even though I was fairly close to the front!).

pause in the procedings
I’d had a bit of a headache (which I don’t normally get!) on the Sunday, and by this point in the proceedings, it had returned and I wasn’t feeling brilliant. My eyes had struggled with the slides, and I don’t think it was a problem with the projecters or the screens, either. I’m long-sighted, amongst other things, but I struggle to focus at distances. My prescription could be improved for long distance vision, but this would reduce my near distance vision. Given that I need to be close to the things that I’m looking at when I read, sew, write, etc, and that I do more of this than I do staring at presentations on large screens, it’s a compromise I’ve had to make. Didn’t help my headfail though!

I was going to go to Carl Dudley’s “Constraints – for complex business rules and improved performance” presentation, but decided that I really, really needed to give my eyes a break, so I ended up wandering through the exhibition again, before flumping in the lounge area.

Graham Wood – The ASHes of time?
I went to this primarily because Doug has previously mentioned about ASH both as part of his “How I learned to love pictures” presentation, and separately when we were discussing a performance issue I was having, and it sounded like it would be a good session to learn more about it from an introductory level. And it was!

I picked up some useful tips and information from this, that when I finally persuade my team’s DBA’s to reset our Grid Control password, will come in very handy! And, of course, if I query the table myself! *{:-D

Jonathan Lewis – Writing Optimal SQL
By now, I was really struggling, as my head really was becoming fuzzy and painful, so I took some ibuprofen (which didn’t do a thing to help!). Still, managed to stick with it, and I was glad I did! This session by Jonathan took us through the decisions we need to think about when we write SQL, in order to come out with better plans.

Most of this was familiar to me, but not in such an organised fashion. The one thing I did pick out of this session that I will be applying at work is (and this is probably sooooo obvious, only I’ve never considered it!): to draw a diagram of the tables and how they relate to the number of rows generated, etc. You can then see pictorially which might be the best table to start with, and where to go next. Simple, but brilliant!

Tom Kyte – Top 10, no 11, new features of Oracle database 11gR2
Aaah, Mr Kyte (or “Uncle Tom” as I used to refer to him back when I first started learning PL/SQL etc, and I followed AskTom religiously. Actually, for the first year or two, that was the only thing I really knew about; and I found it invaluable as a learning aid – Tom is one of the best teachers around, IMHO!) again. This was a list of features that he wanted to bring to our attention that are new in 11gR2. Some of them I already knew about, but some were completely unfamiliar to me!

I’ve already had some ideas on how to improve my db app once the databases have been upgraded to 11gR2, whenever that might be! I may well be pestering our DBAs for that to be sooner rather than later! *{;-)

Still too much red in this presentation, which unfortunately did not help my poor achey head and eyes any!

Doug Burns – More Parallel Fun
I know that Doug was less than happy with this presentation, and to be fair, he did suffer from a touch of BluePeter-itis, as the demos that had been running happily for him when he ran them earlier in the day went veeeeeery sloooooooooow during the presentation.

I thought that the presentation was good, though, as I was able to keep up with the content and understand what Doug was saying (although perhaps the graphs could maybe have done with being animated, to highlight the fact that we were looking at slices of time, rather than the whole thing at once – my confusion was soon put to rest when he explained what it was representing, though!). Having attended this and an earlier presentation on parallel that he gave at work, I now feel much more confident in working with parallel. I may even try making some of our big queries run in parallel – something that I’d never really considered seriously!

Meet the Speaker
I headed briefly to this, but didn’t really have anything intelligent to say or ask, plus I really was feeling unwell by this point, so I decided the best thing to do would be to go back to the hotel, and have some rest. I wasn’t really thinking coherently by now, because I passed one of my friends that I’ve known for years now from a bulletin board system that I am a member of – he was eating alone in a restaurant *{:-(

If I’d been thinking properly, I would have arranged to have met up and gone to dinner or dragged him along with me when I would have tagged along with Doug and met the big hitters that were apparently about on Monday evening at the exhibition party/technical pubs. But it was not to be – sorry @Farkough *{:-(

I ended up walking round the city centre trying to find somewhere that was open so I could buy some paracetamol, once I’d worked up the motivation to leave the hotel room. Thankfully, Boots were doing late night opening, so I got some, and on the way back to the hotel, I passed a Pizza Hut and couldn’t resist the comfort-food factor!

Since everyone else seems to be doing it…. My Agenda for UKOUG TechEBS ’09:

boneist November 29, 2009 Uncategorized 0

Monday

Registration opens: 8:30 – 18:00

Exhibition opens: 9:00 – 20:00
09:35 – 09:55 First Timers Briefing
10:00 – 10:45 David Callaghan Keynote
10:45 – 11:45 Server Technology Keynote: What are we still doing wrong?
Mr Thomas Kyte
Oracle
Hall: Hall 1
11:50 – 12:35 Constraints – for complex business rules and improved performance
Mr Carl Dudley
University of Wolverhampton
Hall: Hall 7A
12:35 – 13:40 Lunch
13:40 – 14:40 The ASHes of time?
Mr Graham Wood
Oracle
Hall: Hall 5
14:50 – 15:35 Writing Optimal SQL
Mr Jonathan Lewis
JL Computer Consultancy
Hall: Hall 1
16:00 – 17:00 Top 10, no 11, new features of Oracle database 11g Release 2
Mr Thomas Kyte
Oracle
Hall: Hall 1
17:10 – 17:55 More Parallel Fun
Mr Doug Burns
Bayvale
Hall: Hall 5
18:00 – 18:30 Meet the Speaker

Exhibition Party: 18:30 – 20:00

Focus Pubs: 20:00 – 21:30

Tuesday

Registration opens: 8:30 – 18:00

Exhibition open: 10:30 – 17:30

09:45 – 10:30 Getting Connected!
Mr Bob Mycroft
Fortissimo Solutions Ltd
Hall: Hall 10B
10:30 – 10:55 Tea and Coffee break
10:55 – 11:55 All about metadata; why telling the database about your schema matters
Mr Thomas Kyte
Oracle
Hall: Hall 1
12:05 – 13:05 11g features for Developers
Mr Connor McDonald
Independent
Hall: Hall 5
13:15 – 14:00 Introducing Partitions
Mr Jonathan Lewis
JL Computer Consultancy
Hall: Hall 1
14:00 – 14:45 Lunch
14:45 – 15:45 Vital Statistics
Mr Julian Dyke
JulianDyke.com
Hall: Hall 1
15:55 – 16:40 RAC – What’s the difference?
Mr Chris Dunscombe
Christallize
Hall: Hall 1
16:40 – 17:05 Tea and Coffee break

Fire and Ice Party – 18:30 – 21:00 (Sponsor by Oracle)

Wednesday

Registration Opens: 9:00 – 15:00

Exhibition opens: 10:00 – 15:15

Installfest – 11:00 – 13:00 (Exhibition gallery)
09:50 – 10:50 Execution Plan Stability in Oracle 11g
Mr Joze Senegacnik
Private Researcher
Hall: Hall 9
11:05 – 12:05 Everything you always wanted to know about FIRST_ROWS_N but were afraid to ask
Mr Randolf Geist
Freelance Consultant
Hall: Hall 8A
12:05 – 12:50 Lunch and Meet the Speaker
13:50 – 14:50 All about grouping
Mr Rob van Wijk
CIBER
Hall: Hall 8A
14:50 – 15:15 Tea and Coffee break
15:15 – 16:00 Anatomy of a SQL Tuning session
Mr Wolfgang Breitling
Centrex Consulting
Hall: Hall 5

Watch out….

boneist October 27, 2009 Uncategorized 0

… not only do teddy bears have picnics in the wood, but they also can take over the office!

George in the boss’s chair:

George preparing to go to work:

George might not have quite “got” the concept of a laptop:

Oh, and he nabbed the consolation schwag I got for not making it to OOW ’09… sheesh!

An Explanation for the previous post

boneist September 19, 2009 Uncategorized 1

At work, I like to help out other people, and one of the areas I know a fair amount about is Toad – where to download it from the servers, how to register to get a licence key, basic installation problems, etc, etc. I’ve worked out how to get myself access to the Quest Support site (veeeeery useful if you haven’t and you do use Toad – you can search for solutions, a bit like Metalink only without the pwetty flash…).

I’ve been using Toad for at least 10 years now, and until I worked in my current job, I worked with the really buggy versions that had memory leaks all over the place. (v8.6 and earlier, from memory) Upgrading to 9.0.1 didn’t really seem to make much difference – multiple crashes, address access violations all over the place, etc per day. However, since I upgraded to v9.6+, Toad has been an awful lot more stable…. I can have it open for days if not weeks with multiple databases connected and it’s fine!

Therefore, I have become used to asking people what their Toad version is, whenever they say they have a problem with Toad. If it’s pre-v9.6, I tell them to install the latest available version.

Of course, a certain Oracle Ace has picked up on this, and now precedes to tease me mercilessly about my propensity to say that phrase (of course, he teases me mercilessly about all sorts of other things too, but that’s par for the course… and of course, I give as good as I get! *{;-) ). Just a shame that LJ doesn’t allow anonymous users to put in their name – I suggest to anyone wanting to comment without creating an LJ sign-in that you use openid to verify who you are; it’ll then pick up the details from whatever account you’ve been verified under.

(eg. if you have a Google account, if you put the identity URL as: https://www.google.com/accounts/o8/id it’ll either already recognise you’re signed into your Google account or it’ll ask you to sign in. If you have an account anywhere that supports OpenId, you can use this method if you don’t want to create yet another account – but you will have to find out what the url is to verify your identity!)

So… what’s *your* version of Toad?! *{;-)

Do you use Toad?

boneist September 17, 2009 Uncategorized 11

If so, what version do you use?

UKOUG Tech / E-business Conference 2009 – aka My First Oracle Related Conference, Evah!

boneist September 7, 2009 Uncategorized 0

I’ve just had a look through the agenda for the UKOUG Conference agenda for 30th Nov – 2nd Dec, and I have to say, it all looks pretty interesting! Lots of familiar names – Tom Kyte, Jonathan Lewis, Steven Feuerstein, etc, as well as a couple of names I recognise from the OTN SQL and PL/SQL forum: Randalph Geist and Rob van Wijk!

I’ve already seen one of the presentations that Doug has given, so probably won’t go see it again (and no, I *still* haven’t managed to get the password so I can log in to Grid Control and playlearn about it myself!), but the other one looks to be a followup to another presentation of his that I’ve seen. Tom Kyte’s are a must, of course, and I do fancy Rob vW’s presentation on grouping – I’ve never really used those features, so it’d be useful to go!

I could go on, but perhaps I should wait until I’ve signed (and paid!) up.

If anyone else is going that I haven’t yet met, I will be wearing a clue as to who I am *{;-) (And no, it won’t be a bobbly hat, although it will be winter… hmm!)

Learning things

boneist July 30, 2009 Uncategorized 5

So today, I attended Doug Burns’ “10g/11g Performance Analysis – How I learned to love pictures” presentation at work today. Very interesting; I want to get my hands on Grid Control now, and play around with the pretty graphs! It’s a shame that our area’s DBAs are both absent (one’s on holiday I think, the other is unfortunately unwell), so it’ll be a while before I get my grubby little paws on it!

I really enjoyed the presentation – it really didn’t feel like an hour had gone past, but it had *{:-( He kept threatening to keep going until 4 or 5pm on the subject, but I could have quite happily stayed and listened (and learnt!) for that long!

One interesting fact that I wasn’t aware of that cropped up, is that CPU’s only process one instruction at a time. Or at least, that’s how Oracle views it (oh, probably a highly simplified view of things there, but I read the wikipedia article on CPUs and a couple of other pages and I thought it best to stop before my brain leaked out of my ear!), in the case of the multi-instruction per CPU (ie. that just counts as X CPU’s).

I vaguely thought that CPU’s automatically handled several instructions at once, thereby serving several processes at once (ie. the concept of a CPU being 60% busy would be true). Instead, it’s either busy or not busy, and anything that arrives whilst the CPU is busy is put in a queue – akin to going down to the post office and joining the queue. “Please go to window 3 -> -> ->”!

Just goes to show how woefully lacking my general computing knowledge is! It took Doug a while to work out what I was asking about, because that’s such a basic, fundamental thing to know! Still, at least I know now! A day in which you learn something is never a waste!

Puzzling it out … again?

boneist June 19, 2009 Uncategorized 0

Just because I happened to have my camera still in my handbag after a recent holiday to Yorkshire, I took the opportunity of taking photos of some of my puzzles.

Here is a toffee tin that I’ve now appropriated to hold the puzzles that are currently out of the drawer:

Here’s the drawer where I keep my puzzles:

And just because, here’s my desk – you can tell I believe in keeping things fun at work, can’t you?!:

And here’s a close up of the left hand side of my desk:

– there’s a book missing from my shelf, namely the “Oracle PL/SQL Programming” bible book by Steven Feuerstein! And I know what you’re thinking – “Wot, no Tom Kyte?!” That’s because I’ve got his book at home *{:-)

Puzzling it out

boneist June 16, 2009 Uncategorized 0

Way back when I first started working in a “proper job” after uni some 10 years ago, I bought a puzzle (similar to this one, except the ball on mine is larger and a light wood in colour), and I couldn’t solve it. In fact, it took 3 months of being passed round the department before anyone solved it! (It was, incidentally, an Oracle developer who was contracting with us at the time who first solved it.)

Since that first puzzle, I have amassed quite a large collection, most of which lives in my drawer at work (I don’t have enough desk space to have all of them out!) whilst I rotate 3 or 4 periodically and leave them on my desk for others to try and solve.

Problem solving is perhaps my strongest skill, and I can see now that you’re beginning to get an inkling of where I’m going with this post! A large part of developing code involves problem solving – what’s the best design for the requirements? What’s the most efficient way of doing X and Y? What’s the cause of this bug that the users are reporting? and so on… I firmly believe that exercising the mind by doing puzzles such as these, Sudoku, crosswords, etc is one way of helping to keep the mind flexible and open to new suggestions and ideas (if nothing else, frustration is good for making you go “Oh, let’s just try this silly random thing I dismissed earlier!” and that can lead to all sorts of breakthroughs.)

Problem solving is not all the job entails, of course; you have to have a large dollop of common sense, not to mention a sense of proportion and reality (is this problem *really* worth solving?) but I maintain that it helps.

And in the meantime, I shall keep adding to my collection, whenever I see a new one…

Primary keys vs Unique Indexes (or Oracle vs Sybase…)

boneist January 30, 2009 Uncategorized 0

At work we have an internal chat system, and there is a channel devoted for SQL chat, that covers MS SQL Server, Sybase and Oracle (even though there’s a separate channel for Oracle developrs to talk amongst themselves!).

Anyway a question was asked this week: “what is the difference between a primary key and an index on a table?”
to which I gave the response: “a primary key is a constraint on the table, whereas an index is something that makes it easier to look up things in the table”

Someone else (let’s call him Dave) then chimed in with: “not if its a unique index, that can be a pk without it being a constraint…”

And lo the debate began! Of course, Dave is knowledgable about Sybase (of which I know only what I’ve picked up in the SQL chat channel from watching other people talk about it!) and thinks that Oracle is unfriendly and only works with it if he really has no other choice. 

SQL> create table test1 (col1 number);

Table created.

SQL> create unique index unq_t1_index on test1 (col1);

Index created.

SQL> create table test2 (col1 number);

Table created.

SQL> alter table test2
  2  add constraint fk_t2_t1_col1
  3  foreign key (col1)
  4  references test1 (col1);
references test1 (col1)
                  *
ERROR at line 4:
ORA-02270: no matching unique or primary key for this column-list

Cue comments that imply Oracle is rubbish for making you specify a constraint in order to have a foreign key referencing the table – Sybase (and presumably MS SQL Server) have, apparently, loads of examples of indexes enforcing constraints with just an index and no formal constraint declaration.

Then the following conversation takes place:

<< sybase demo of fk setup to reference a table with only a unique index >>
Dave: hence my point, only Oracle enforces the pk constraint defn 
Me:   for fk's, true 
Me:   but surely good design practice would also enforce it? 
Dave: we werent arguing design, we were debating whether the dbms 
      forces it ;) 
Me:   so what you're saying is that Oracle is better because it forces 
      better design? *{;-D 
Dave: pah 
Dave: :p

So, one for Oracle there! *{;-)

New Icons and more thoughts on the OTN Forum upgrade

boneist September 5, 2008 Uncategorized 0

I’m not going to OOW (maybe one day!) but I thought that the badge icons that Justin Kestley put in his post might be usable as some icons for lj! I’ve updated the posts I’ve already written with the applicable icons (I’ve nabbed the OTN and SQL ones), but I couldn’t have done it without the help of my dearly beloved, who basically did the magic to extract the icons from the sample shown on Justin’s post!

As for the forums, I can’t help but feel that the lack of a Beta site for testing forum upgrades has kinda bitten Oracle on the bum. From the complaints I’ve seen, a lot of people rely on the Oracle forums and I can’t help but feel that the Forums have a low priority in the overall structure of Oracle-the-company. However, it’s just like how I feel about spelling mistakes and little low-priority niggles in front ends: sometimes it’s the minor details that influence people’s perceptions. A site that is usable but contains loads of spelling mistakes and extra clicks and key presses will cause the site and the team/company behind the site to go down quickly in people’s estimation. This is what I think is happening with the forums – as people lose faith in the forums due to down time, slowness, incomprehensible formatting, etc, they’ll leave and their faith in Oracle to get things right will go down.

I know that part of the trouble comes from the Jive software (I had a scoot round their site the other day; scalable and performant… ?!) and the host of extra “features” that comes for free. However, in a technical forum, I really, really don’t think we need all those bells and whistles – I mean, you need to be able to quote previous text, write your own text possibly with bolding or italics or containing links, but much more than this is not needed – I’m never going to write and need different sized headings, for example!

The other bug-bear that people have is the new reward system. They’re upset that they no longer have the “golden turnip/radish/medal/whatever” next to their name and are now reduced to being called “Newbie” and are no longer in the top user section in their particular forum. The people that have complained are the ones that look on answering things in the forum as a competition, so it’s more about ego than it is about helping people. I’ve no problem being labelled a Newbie (and I suspect that many of the people I would consider to be experts aren’t bothered either), because I post a) to help people and b) to learn. Sure, it’s nice to be credited, but it’s by no means a right that I expect! I’d rather people looked at my answers and took them to show my level of expertise, rather than how many points I have. Heck, I’m far from being an expert! I’m good at searching, and I like helping, but I still make stupid mistakes! So looking on the number of posts I’ve made does not show any indication of how much of an expert I am!

I do have one problem with the current reward system, and that is when a question is marked as Answered, I find that I am less willing to bother looking at that post, especially if the site or my network is being slow at that point. If I’m doing it, I’m pretty sure others are also doing similar, including experts. Now if no-one is going into these answered posts to check that the answer is right, who is going to correct the wrongly marked answers?

I’m sure this won’t be much of an issue now, whilst things are new, but over time, I think less people will bother checking answered questions, meaning that when people do searches, they’re going to see answers marked correct when in actual fact they aren’t correct – or, whilst being technically correct, aren’t the best solution.

Oh well, maybe it won’t be much of an issue, but I think it’s human nature to expend energy on something that’s apparently been marked as completed.

I think that if a Beta version had been in place, a lot of these arguments could have been worked out in advance and my suggestion of having a post rating similar to that of the Amazon.com review rating system (1 out of 10 people found this useful) might have been implemented – and that would sort some of the complaints out!

Ah well, maybe next time there’s an upgrade a Beta can be set up first – if it’s truely Oracle’s intention to involve the community, then that would be a good way of doing so in the forums!

Having (your cake and eating it!)

boneist September 5, 2008 Uncategorized 0

I tried to help someone out today on a Bulletin Board System that I’ve been on for years.

Their question was:

Here is my dataset:

ID, Flag
1 1
1 0
1 0
2 1
2 1
2 1
3 0
3 0
3 0

Basically I want a list of IDs where Flag is ONLY 1, so in this case, 2.

Currently doing

select distinct id from table where id not in
  (select id from table where flag=0)
and  flag=1;

Seems overkill. Is there a better way?

I proposed:

select id
from   (select id, count(id) flag_count, sum(decode(flag, 1, 1, 0)) flag1_count
        from   my_tab
        group by id)
where  flag_count = flag1_count;

Which as any fule knows is exactly what the HAVING clause was meant to do:

select id
from   my_tab
group by id
having count(id) = sum(decode(flag, 1, 1, 0));

I seem to have a blind spot for this when it comes time to generating the SQL – sure, I know about the HAVING clause, but remembering it when I need it is an entirely different matter!

Sheesh!