Rob van Wijk

Last Friday at work, I overheard a conversation between two DBA's about a performance problem of a job which usually ran in 10 minutes, now taking more than 4 hours. I helped them pinpoint the problem: a simple SQL query which took >95% of the time. Clearly this query was the culprit. The query selects from just one table but with 10 filter predicates of this form: nvl(column,'@') = nvl(:Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1

As this post appears, I'm presenting on this subject for the Dutch Oracle User Group OGh. This blog post won't contain as much detail as the presentation itself, although it's pretty close. If you're interested to see and replay everything for yourself, you can find the material of the presentation in the "Presentations and papers" section and here directly. There are installation instructions inRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0

Seven years after the first Obfuscated SQL Code Contest, a new one has been organised on the PL/SQL Challenge website. I had fun writing my first entry back then, but in the back of my mind I regretted not doing something with ASCII art. So the new contest was a good pretext for me to finally fill in that gap. Here is my entry for the second Obfuscated SQL Contest: SQL> select listagg 2 (chrRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com3

A couple of months ago, Nienke Gijsen invited me to speak about materialized views at the upcoming Planboard DBA Symposium. Because I had the pleasure of presenting before, I knew the conference is always well organized and a pleasure to visit. So of course I accepted the invitation. We agreed I'd talk on "just" incremental refreshes of materialized views using materialized view logs and about myRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0

The fifth edition of OGh APEX World took place last Tuesday at Hotel Figi, Zeist in the Netherlands. Again it was a beautiful day full of great APEX sessions. Every year I think we've reached the maximum number of people interested in APEX and we'll never attract more participants. But, after welcoming 300 attendees last year, 347 people showed up this year. Michel van Zoest and Denes Kubicek Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0

I answered a few SQL questions on Stack Overflow recently, which I could solve easily by using the Tabibitosan method. It's such an elegant and efficient technique, I think it's worth giving it an extra bit of attention through this post. I'm certainly not the first to write about it: it was introduced to me by Japanese Oracle ACE Aketi Jyuuzou on OTN. He wrote a special forum post explaining hisRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com4

During a performance inspection of some E-Business Suite batch programs running on 11.2.0.3, I noticed almost 99% of the consistent reads missing in the tkprof file for a particular insert statement. It was a giant INSERT ALL statement where the accompanying select statement contained several subqueries in a "case when exists (query)" construct. And those subqueries in the select clause was what Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1

My colleague Ronald Rood recently had a nice SQL challenge for me. He had to perform an export of all the tables in a schema the old fashioned way and wanted to manually parallellize the operation. For that to work, all tables need to be assigned to a group. For the parallellization to work, the groups need to be balanced. If, say, all large tables are in one group, the parallellization won't Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2

Last Tuesday, the fourth OGh APEX World day took place, again at Figi Zeist. Several people have already written about this day: Christian Rokitta, about the announcements from David Peake in his keynote, Bart Peeters from iAdvise, with comments about several sessions and Rob de Gouw from Quobell, written in Dutch. Here is a short writeup about my experiences that day. For me, this year was a Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0

As announced on Twitter yesterday, my paper titled "Professional Software Development Using Oracle Application Express" has been put online. I'm copying the first two paragraphs of the summary here, so you can decide if you want to read the rest as well: Software development involves much more than just producing lines of code. It is also about version control, deployment to other environments, Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2

Yesterday I encountered a code snippet which taught me something I did not think was possible. But it is, as I'll show in this blogpost. It's not spectacular in any way, just convenient at most. When you need a function or procedure to retrieve some value, you'll start examining the existing code base if a function already exists that does the job. Probably you won't find an exact match, but Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2

My third time visiting the annual UKOUG conference in Birmingham started all wrong. At Schiphol Airport, the usual luggage check routine took place: laptop out of the suitcase, wallet/keys/belt apart, toothpaste apart. And afterwards putting everything back in. But I forgot to close the wheeled suitcase and when putting it on the ground, my MacBook Pro fell out. A quick inspection revealed that Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0

On Wednesday evening November 28, my colleague Marcel Hoefs and I will both do a one-hour knowledge session at Ciber Nieuwegein. What's new about it, is that the knowledge session is not only for Ciber colleagues, but for anybody who would like to attend. Both sessions will be in Dutch, so for the remainder of this post, I'll switch to Dutch and copy Marcel's invitation text: Graag nodig ik Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0

You may have seen an aggregate function like this in SQL queries: max(value) keep (dense_rank first order by mydate) or this analytic variant: max(value) keep (dense_rank last order by mydate) over (partition by relation_nr) Unfortunately, when you start searching for the "keep" clause, you won't find anything in the Oracle documentation (and hopefully because of this blogpost, people will Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com8

I was reading this presentation PDF of Hugh Darwen recently, called How To Handle Missing Information Without Using NULL. Several great thinkers and founders of the relational theory consider NULL as the thing that should not be. For example, one slide in the above mentioned PDF is titled SQL's Nulls Are A Disaster. And I found a paper with the amusing title The Final Null In The Coffin. I can Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com7

At CIBER we are very proud to announce that Cary Millsap will give his one day seminar called Mastering Oracle Trace Data in the Netherlands. The event will take place at the Carlton President Hotel at Utrecht on Wednesday, May 23. You can register and read more about this event here.The seminar is aimed at DBA's, database application developers, data warehouse specialists and anyone caring aboutRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2

Yesterday was the third annual APEX day, organized by the Dutch Oracle usergroup OGh. It's the biggest APEX only event in the world, I've been told, with approximately 280 attendees. Learco Brizzi, Marti Koppelmans and myself were very proud to again have a great lineup of presenters and presentations.The day started with a keynote by Patrick Wolf telling about and showing a lot of new 4.2 Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0

A hierarchical query is typically executed using a plan that starts with the operation CONNECT BY WITH FILTERING, which has two child operations. The first child operation implements the START WITH clause and the second child operation contains a step called CONNECT BY PUMP, implementing the recursive part of the query. Here is an example of such a plan using the well known hierarchical query on Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com6

Most of us know the SQL keyword EXISTS as the condition with a subquery you use in a WHERE clause. But if you look at the documentation of EXISTS, you'll see that it says nothing about just using it in a WHERE clause. It's just a sort of function that accepts a query as input and returns "TRUE if a subquery returns at least one row". The SQL language doesn't know about booleans, but it calls Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2

I have experienced a strange bug when constructing an example showing how a partitioned outer join works. I used a setup which resembles a situation I have consulted about recently for timeseries data. The table can hold multiple timeseries whose data is coming from multiple sources. The data itself contains volumes that are reported for every 5 minutes. This table looks like this:SQL> create Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com5