In-Line Views versus Correlated Sub-queries – tuning adventures in a Data Warehouse Report

Events have taken a worrying turn recently. I’m not talking about Kim Jong Un’s expensive new hobby, although, if his parents had bought him that kite when he was seven…
I’m not talking about the UK’s chief Brexit negotiator David Davies quoting directly from the Agile Manifesto and claiming that the British were “putting people before process” in the negotiations, although Agile as a negotiating strategy is rather…untested.
I’m not even talking about the sunny Bank Holiday Monday we had in England recently even though this may be a sign of Global Warming ( or possibly a portent for the end of days).
The fact is, we have an Ashes series coming up this winter and England still haven’t managed to find a top order that doesn’t collapse like a cheap deckchair in a light breeze.

On top of that, what started out as a relatively simple post – effectively a note to myself about using the row_number analytical function to overcome a recent performance glitch in a Data Warehouse Application – also seems to have developed an unexpected complication…

Continue reading

Advertisements

REGEXP_LIKE – Happy thoughts whilst searching for multiple substrings in Oracle SQL

International relations seem to be somewhat tense at the moment with various World Leaders being publicly grumpy with each other.
To keep my mind off damoclesian digits dangling dangerously over Big Red Shiny Buttons, I really just want to hear some nice news to help me think happy thoughts.

I’d like to read about Luton Town because they’re doing quite well, or the England Cricket team winning a Test Series. I might even treat myself to a random Cat Video…

Continue reading

Dude, Where’s My File ? Finding External Table Files in the midst of (another) General Election

It’s early summer in the UK, which means it must be time for an epoch defining vote of some kind. No, I’m not talking about Britain’s Got Talent.
Having promised that there wouldn’t be another General Election until 2020, our political classes have now decided that they can’t go any longer without asking us what we think. Again.
Try as I might, it may not be possible to prevent the ear-worm phrases from the current campaign slipping into this post.
What I want to look at is how you can persuade Oracle to tell you the location on disk of any files associated with a given external table.
Specifically, I’ll be covering :

  • getting the name of the Database Server
  • finding the fully qualified path of the datafile the external table is pointing to
  • finding other files associated with the table, such as logfiles

In the course of this, we’ll be challenging the orthodoxy of Western Capitalism “If You Can Do It In SQL…” with the principle of DRY ( Don’t Repeat Yourself).
Hopefully I’ll be able to come up with a solution that is “Strong and Stable” and yet at the same time “Works For The Many, Not the Few”…
Continue reading

The Django Fandango Farrago – Looking at Django’s Physical Data Model Design

I’m sure I’m not the only Oracle Developer who, over the years, has conjured a similar mental image during a planning meeting for a new web-based application…

wibble

…and we’re going to use an ORM

If you want the full gory details as to why this is so troubling from an Oracle database perspective, it is a topic I have covered at length previously.

This time, however, things are different.
Yes, I am somewhat limited in my choice of database due to the hardware my application will run on (Raspberry Pi).
Yes, Django is a logical choice for a framework as I’m developing in Python.
But, here’s the thing, I plan to do a bit of an audit of the database code that Django spits out.
< obligatory-Monty-Python-reference >That’s right Django, No-one expects the Spanish Inquisition ! < obligatory-Monty-Python-reference / >

torturer

Donde esta el Base de datos ?!

I know, this is a character from Blackadder and not Monty Python, but I’ve often regretted the fact that there never seems to be a vat of warm marmalade around (or some kind of gardening implement for that matter), when you enter those all important application architecture discussions at the start of a project.

As a result, one or two further Blackadder references may have crept in to the remainder of this post…

Continue reading

Automated Testing Frameworks and General Rule-Breaking in PL/SQL

If there’s one thing that 2016 has taught us is that rules (and in some cases, rulers) are made for breaking. Oh, and that it’s worth putting a fiver on when you see odds of 5000-1 on Leicester winning the League.

Having lacked the foresight to benefit from that last lesson, I’ve spent several months looking at Unit Testing frameworks for PL/SQL. In the course of this odyssey I’ve covered:

This post is a summary of what I’ve learned from this exercise, starting with the fact that many of the rules we follow about good programming practice are wrong…
Continue reading

Testing Times – using ruby-plsql-spec for testing PL/SQL

There is method in the madness. It’s now clear that Donald Trump’s reluctance to commit to the Paris Climate Change Accord is because US methane emissions have been hugely under estimated. Yes, it turns out that there are many more Shy Trumpers in America than (almost) anyone expected.
Meanwhile, back in the UK we know that Brexit means Brexit but we still don’t know what Brexit means.
In amongst the chaos, UKIP have decided to take a fresh approach to the business of selecting a leader. This time, they’re staging a Cage Match.

Taking a leaf out of UKIP’s book I’ve decided to take a slightly unusual approach to Unit Testing my PL/SQL code.
Having looked at the SQLDeveloper Unit Testing Tool and utPLSQL, both of which utilise the database to persist objects, this time, I’m taking a look at a framework which takes a rather less database-centric approach, namely ruby-plsql-spec.

What I’ll be looking at is :

  • Installation of the framework and required components
  • A quick recap of the application being tested
  • Writing and executing Unit Tests
  • Summary and conclusions

Continue reading