Grassroots Oracle: August 2014

Friday, 29 August 2014

Birds don't make the best ornithologists

This week I listened to episode 44 of one of my favourite podcasts - Inquiring Minds. It was an interview with David Epstein on the 'science of athletes'.

The podcast culminated with some really interesting points regarding the concept of 'talent' - what it really means, how it can be measured or influenced. One particularly interesting basis was the traditional idea that '10000 hours' training leads excellence in any particular skill, vs what's in the genes.
Exhibit A: Kalenjin (regional Kenya) long distance runners. Well worth the listen to get the full detail.

Another interesting example was a female softball player Jennie Finch that made the news in 2004 striking out professional male players. The men expected to have no problems hitting her out of the park, but players confused their perceived reliance on ability to track ball in flight and rely on 'talented' reaction time with what they actually do.

(Getty Images)

In fact, this article I found when searching for Jennie Finch goes into very similar detail (since it's written by the interviewee!) and suggests

Given the speed of the pitch and the limitations of our physiology, it seems to be a miracle that anybody hits the ball at all.

It's apparent that players interpret the pitcher's shoulder, body rotation, ball thread rotation in flight and other factors to calculate the region the hitter expects the ball to fly by. But since thrown underarm, all information players read to calculate where the ball will land - is lost.
They've even got a bigger, slower ball to hit.

They found likeness to chess, where a good player sees the entire board - a number of moves ahead.
I found likeness to pool/snooker. After the break I might see the next 4-5 balls I can pot in a row. Newer players are often overwhelmed just to work out which one to try.

I'm sure my game would slow right down if was faced with a different shaped table.

Pool tables I wouldn't enjoy

I don't do the topic justice, but I recommend both this particular episode and the podcast in general. I listen to podcast in my commute to work - I play to post my favourites one day, I'd be interested to hear any other recommendations.

The blog post title? A quote from David during the interview

Friday, 22 August 2014

OTech Magazine Issue 4 - APEX 5 and much more

Well it's the second half of August and this is my first real post for the month. There are two very good reasons for this:

1) We've just completed a major implementation at one of our clients - a tablet based application using APEX, which completely changes the face of a major arm of the business. It's a groundbreaking application with only one peer in the industry - which users are already claiming superiority to and it's had years to mature. It's been a very enjoyable project with a glowing success story - kudos to my Sage colleagues and the entire team.

2) Douwe Pieter van den Bos from OTech Magazine asked if I would like to contribute to their next quarterly issue, but with a catch - it had to be submitted pretty soon as they were already finalising the content. I hadn't actually heard of it until I received a LinkedIn request from Douwe. This is only the fourth issue so I don't feel too bad. I browsed through the previous issues and read what the magazine is all about and it was something to which I was more than happy to contribute.

I think Douwe had seen my series of blog posts on APEX 5 and asked if I would like to add something on that. I also recently presented on the topic in Perth, so I figured my Prezi was something I could quickly adopt into prose. It was a bit of a rush job and I feel it shows a little, but I plan to do the presentation again as an ODTUG webinar, so stay tuned if you want to hear the original format.

OTech Magazine #4

OTech Magazine is free, independently produced, well polished, digitally formatted for Oracle professionals. The editors carefully select authors & articles in efforts to obtain high quality content. My APEX 5 (EA2) article is in the just released "Summer 2014" issue. Yep, I quoted that because I'm not sure that global products should relate to a season - especially since I'm currently living in a "winter" climate. Yep, I quoted that too because we're enjoying mostly 22C sunny days - I blame climate change.

It confuses the heck out of me when I see a movie advertised for release in Winter 2015 - what range of months is that?!

Anyway, check out the OTech Magazine - you'll find yourself wanting to read the past issues.
http://www.otechmag.com/2014/otech-magazine-summer-2014/

Demystifying Oracle Unpivot

A couple of years ago I posted a simple example using PIVOT, converting rows to columns with the classic example of figures by months.

Oracle 11g R1 also introduced the UNPIVOT function, allowing columns to be converted into rows.

Problem

I've created an example that lists cities by row, but two attractions as two columns, with pairing attributes describing the reason for the attraction.

create table aus_attractions(id  number, city varchar2(50)
  , attraction1 varchar2(50)
  , attraction2 varchar2(50)
  , reason1 varchar2(50)
  , reason2 varchar2(50)
  );
insert into aus_attractions values (1, 'Perth','weather','beaches','sunny','white sand');
insert into aus_attractions values (2, 'Sydney','bridge','blue mountains','climb','scenic');
insert into aus_attractions values (3, 'Melbourne','culture','aussie rules','activities','crowds');

select id, city
      ,attraction1,attraction2
      ,reason1, reason2
from aus_attractions;

Not optimal relational data design

I'd like to see each attraction by row - 6 rows instead of 3.

You could solve this with a UNION ALL, and/or a WITH - but one day "they" will ask for 5 options, might as well unpivot.

Solution

Then we can turn our original statement into an inline view, serving the unpivot function.

select id, city, attraction, reason, rec_nbr
from ( -- original query:
     (select id, city
         ,attraction1,attraction2
         ,reason1, reason2
   from aus_attractions
   )
 unpivot               -- the magic operator
 ((attraction, reason) -- names of replacement columns
  for rec_nbr in (     -- new column defining data source in literal alias below
   -- split each group of fields in here
   (attraction1, reason1) as 'REC1' 
  ,(attraction2, reason2) as 'REC2'
  )
 )
);

ID      CITY       ATTRACTION        REASON       REC_NBR
------  ---------- ----------------- ------------ -------
1       Perth      weather           sunny        REC1
1       Perth      beaches           white sand   REC2
2       Sydney     bridge            climb        REC1
2       Sydney     blue mountains    scenic       REC2
3       Melbourne  culture           activities   REC1
3       Melbourne  aussie rules      crowds       REC2

6 rows selected

Awesome.