Home › Monthly Archives › October 2011

My UKOUG 2011 Conference Agenda

Sunday

OakTable registration opening times 11:00 – 12:00

Exhibition registration opening times – 15:00 – 18:00

12:15 – 13:15 OakTable Sunday – Tales From The OakTable
Mr Mogens Nørgaard, ACE Director
Miracle AS
Hall:
14:35 – 15:20 OakTable Sunday – Join Techniques
Mr Christian Antognini, OakTable
Trivadis AG
Hall:
15:30 – 16:15 OakTable Sunday – Statistics Gathering and Histograms – DOs and DON'Ts
Mr Wolfgang Breitling, OakTable
Centrex Consulting Corporation
Hall:
16:15 – 16:40 Tea break
16:40 – 17:30 Performance & High Availability Panel Session

Monday

Registration opening times 08:00 – 20:00

Exhibition opening times 10:00 – 20:45

09:00 – 09:20 UKOUG Introduction
09:20 – 09:50 Opening Keynote

Mark Sunday, Oracle

10:00 – 10:45 SQL Tuning
Mr Kyle Hailey, ACE
Delphix
Hall:
10:45 – 11:05 Coffee break
11:05 – 12:05 Partitioning 101
Mr Connor McDonald, ACE & OakTable
Independent
Hall:
12:15 – 13:15 Oracle Optimizer – Upgrading to Oracle Database 11g without Pain
Ms Maria Colgan
Oracle
Hall:
13:15 – 14:30 Lunch
14:30 – 15:15 High Availability Options for Modern Oracle Infrastructures
Mr Simon Haslam, ACE Director
Veriton Limited
Hall:
15:25 – 16:10 Implementing Effective Oracle Database Auditing in 3 Steps
Ms Tammy Bednar
Oracle
Hall:
16:35 – 17:35 Database I/O Performance: Measuring and Planning
Mr Alex Gorbachev, ACE & OakTable
Pythian
Hall:
17:45 – 18:30 Performance and Stability with Oracle 11g SQL Plan Management
Mr Doug Burns, ACE Director & OakTable
Bayvale Consulting Services Ltd
Hall:
18:45 – 19:45 Learning about Life through Business and Software

Cary Millsap, Method R Corporation

Exhibition Welcome Drinks 19:45 – 20:45

Focus Pubs and Inspiring Presentation Awards 20:45 – late

Tuesday

Registration opening times 08:00 – 20:00

Exhibition opening times 08:00 – 18:30

10:10 – 10:55 First Things First: Getting the basics right
Mr Niall Litchfield
Maxima Managed Services
Hall:
10:10 – 10:55 Who’s afraid of Analytic Functions?
Mr Alex Nuijten, ACE Director
AMIS
Hall:
10:55 – 11:15 Coffee break
11:15 – 12:15 Analysing Your Data with Analytic Functions
Mr Carl Dudley, ACE
University of Wolverhampton
Hall:
11:15 – 12:15 Execution Plan Interpretation
Mr Joze Senegacnik, ACE Director & OakTable
DbProf d.o.o.
Hall:
12:25 – 13:25 Oracle Optimizer – Best Practices for Managing Optimizer Statistics
Ms Maria Colgan
Oracle
Hall:
12:25 – 13:25 Tuning, Refactoring, and Instrumenting Code for Developers
Mr Barry McGillin
Oracle
Hall:
12:25 – 13:25 Troubleshooting the Most Complex Oracle Performance Problem I've Ever Seen
Mr Tanel Poder, ACE Director
Enkitec Europe
Hall:
13:25 – 14:40 Lunch
14:40 – 15:25 ASH Outliers: Detecting Unusual Events in Active Session History
Mr John Beresniewicz, OakTable
Oracle
Hall:
15:35 – 16:20 Can people be identified in the database?
Mr Pete Finnigan, OakTable
PeteFinnigan.com Ltd
Hall:
16:20 – 16:40 Tea break
16:40 – 17:40 Beating the Oracle Optimizer
Mr Jonathan Lewis, ACE Director & OakTable
JL Computer Consultancy
Hall:
17:50 – 18:35 Challenges and Chances of the 11g Query Optimizer
Mr Christian Antognini, OakTable
Trivadis AG
Hall:

Evening social and networking 20:00 – late

Wednesday

Registration opening times 08:30 – 13:30

Exhibition opening times 08:30 – 14:30

09:00 – 10:00 Statistics on Partitioned Objects
Mr Doug Burns, ACE Director & OakTable
Bayvale Consulting Services Ltd
Hall:
10:10 – 10:55 Using Oracle GoldenGate to Minimize Database Upgrade Risk
Mr Marc Fielding
Pythian
Hall:
11:20 – 12:20 Latch Internals in 2011
Mr Andrey Nikolaev
RDTeX
Hall:
12:30 – 13:30 Avoiding Execution Plan instability with SQL Plan Baselines
Mr Chris Dunscombe
Christallize Ltd
Hall:
14:25 – 15:10 Faster, Leaner, More Efficient databases – Index Organised Tables
Mr Martin Widlake, OakTable
ORA600
Hall:
14:25 – 15:10 Six Easy Pieces: Essentials of Database Tuning for Packaged Applications
Mr Mike Swing
TruTek
Hall:
15:20 – 16:05 Is This The Future of Load and Performance Testing?
Mr Chris Jones
Oracle
Hall:
16:15 – 17:00 What shape is your data?
Mr Niall Litchfield
Maxima Managed Services
Hall:

Writing “complex” SQL – a how to, pt1

Not so long ago, someone on our work Oracle IM channel asked a question, and then several people were amazed at the solution provided. I’m not sure why; it doesn’t seem so difficult to me, but it strikes me that the practice I’ve had via the SQL & PL/SQL OTN Forum probably has a lot to do with that.

So, anyway, I thought I would start a little series on writing complicated sql, by taking you through the steps I would typically take in order to solve the requirement.

I’ll start with that simple request from my colleague:

I need to display the numbers 1-12 in 3 rows of four columns, like so:

1 2 3 4
5 6 7 8
9 10 11 12

Looking in my SQL toolbox, the very first thing I reach for is how to create the list of numbers from 1 to 12:

with nums as (select level rn
              from   dual
              connect by level <= 12)
select *
from   nums;

which gives us:

   RN
-----
    1
    2
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12

Next thing, I know I need to pivot the results (I should add that I don't have 11g yet, so can't use the nice new functionality!), so now I know I need two things:

  • how to identify the row a number belongs to
  • how to identify the column a number belongs to

Let's take the first item - identifying the row each number belongs to. We know that the numbers 1-4 will go on the first row, 5-8 on the second and 9-12 on the third. I can acheive that by dividing each number by 4 and then take the ceiling value of it, like so:

with nums as (select level rn, ceil(level/4) grp
              from   dual
              connect by level <= 12)
select *
from   nums;

which gives us:

   RN        GRP
----- ----------
    1          1
    2          1
    3          1
    4          1
    5          2
    6          2
    7          2
    8          2
    9          3
   10          3
   11          3
   12          3

Next, I need to work out the column position each number belongs in - that's where the mod() function comes in handy!

with nums as (select level rn, ceil(level/4) grp, mod(level, 4) col_pos
              from   dual
              connect by level <= 12)
select *
from   nums;

which gives us:

   RN        GRP    COL_POS
----- ---------- ----------
    1          1          1
    2          1          2
    3          1          3
    4          1          0
    5          2          1
    6          2          2
    7          2          3
    8          2          0
    9          3          1
   10          3          2
   11          3          3
   12          3          0

So, having identified the row and the column position, we can now use pivoting to display the numbers in the desired format:

with nums as (select level rn, ceil(level/4) grp, mod(level, 4) col_pos
              from   dual
              connect by level <= 12)
select max(case when col_pos = 1 then rn end) col1,
       max(case when col_pos = 2 then rn end) col2,
       max(case when col_pos = 3 then rn end) col3,
       max(case when col_pos = 0 then rn end) col4
from   nums
group by grp;

Giving us:

 COL1       COL2       COL3       COL4
----- ---------- ---------- ----------
    1          2          3          4
    5          6          7          8
    9         10         11         12

Et voilà!