Partitial Join Evaluation in 12c

Franck Pachot erläutert im dbi service Blog eine interessante Transformation, die in 12c eingeführt wurde: die Partial Join Evaluation (PJE). Diese Option erlaubt es dem Optimizer, einen Join, der eine distinkte Ergebnismenge liefern soll, intern in einen Semi-Join umzuwandeln und dadurch die Prüfung zu beenden, wenn ein erster Datensatz gefunden wird, der die Bedingung erfüllt. Seine Test-Query:
select distinct deptno,dname from dept join emp using(deptno)
wird in 11g über einen HASH JOIN mit folgendem HASH (more...)

The APEX_JSON package: Generating JSON from PL/SQL

In my previous blog post, I took a look at the new APEX_JSON package that ships with Apex 5.0 and its capabilities for parsing JSON.

In this blog post, I am going to look at how the APEX_JSON package can be used to generate JSON from data in your database using PL/SQL.

There are multiple ways of creating JSON output using the APEX_JSON package. There are several overloaded write() procedures which can output (more...)

Rob van Wijk and ad hoc Grouping

In March 2014, I wrote about two methods for ad hoc grouping: “Grouping Sequences” and “Start of Group“. I just found out that Rob van Wijk wrote on the same subjects two months earlier. We even used the same example! I mention Rob’s article for two reasons: If my explanation wasn’t clear enough, you can try Rob’s. When […]

Kscope 2014 Summary – It’s time to talk Cloud and Mobile!

So as always we begin with the basics, be sure to checkout the AuraPlayer family photo album of all of you who joined us for a toast throughout Kscope14. My Kscope in pictures.

You can revisit the slides from my sessions here:

Bring Your Legacy Applications to the Mobile World : Tips and tricks on how to begin your Mobile journey.
Experts Panel: Should I Migrate My Oracle Forms Applications? Experts Panel that addressed the future (more...)

Returning BLOB file size

Occasionally I'll want some form of report noting file sizes of blobs in a database.

The solution is relatively simple, and I thought I'd write it up here for a place to copy syntax each time.

APEX users also have a handy table to verify this against (apex_application_files). Well, a synonym/view that ultimately maps to the core table wwv_flow_file_objects$.

It contains a doc_size column, which is no doubt evaluated at some point during upload of (more...)

OK Watch, What’s the Time?

While we’re waiting for the mythical iWatch, Google has already released Android Wear, and the first two Android Wear watches are here.  And they’re ugly.

If I’m going to wear something like that on my wrist, I need a really compelling usecase. I’m not sure that getting even more notifications and reacting 0.8 seconds faster is what I’m looking for.

When my latest Nike FuelBand eventually dies (they seem to last about (more...)

Partion Views in 11.2

Zu den ersten Ergebnissen, die Google bei der Suche nach "Partition View" liefert, gehört Oracle7 Tuning, release 7.3.3. Jenes Release 7.3 wurde 1996 veröffentlicht und seit mehr als zehn Jahren wird man bei Oracle nicht müde zu betonen, dass die "Partition Views" als Feature deprecated sind - aber offenbar funktionieren sie auch in Release 11.2 noch immer, wie ich heute beim Durchspielen eines im OTN-Forum vorgestellten Beispiels feststellen konnte. Ich spare (more...)

SQL Plan Baselines und Parse Probleme

Jonathan Lewis erinnert in seinem Blog daran, dass Baselines keinen positiven Effekt auf Parse-Probleme haben: "The first thing that the optimizer does for a query with a stored sql plan baseline is to optimize it as if the baseline did not exist." In solchen Fällen sind SQL Profiles, SQL Patches oder einfache Query-Hints möglicherweise eine bessere Lösung.

Debugging Faulty Assumptions

I wanted to install Oracle JDevelper 12.1.3 – a version that I had been eagerly awaiting. Since my primary machine is a MacBook, I wanted to install it on OS X 10.9.3.

I downloaded the generic installer and found that the install didn’t run. Since OS X had disappeared from the documentation, I assumed that I would have to fiddle around until I found a combination of JDK and OS (more...)

Default-Werte für Datumsintervallendpunkte

Stew Ashton, der sich in seinem Blog in der Regel mit komplexen SQL-Queries beschäftigt, hält dort ein Plädoyer für den Verzicht auf NULL-Werte bei der Angabe der minimalen und maximalen Werte für Datumsintervalle. Stattdessen spricht er sich für die Verwendung der klassischen Minimal- und Maximalwerte aus, die durch den Oracle Datentyp DATE nahegelegt werden, also den 01.01.4712 B.C. (ich nehme an, das ist Oracles Version von der Erschaffung der Welt) und (more...)

Visual Studio Freedom

Some of my students want to learn to write C++ on a Windows OS. At some point, you simply surrender to the fact that many people prefer the Windows OS as a starting point. Traditionally, I’d recommend the Microsoft Visual Studio as their learning vehicle because it’s free to our students because of our relationship with Microsoft. It’s not free to graduates. When a graduate contacts me I recommend Code::Blocks. Just recently, one ask for (more...)

Incompetence or Malice?

We’ve just had a leak of 900,000 national identifier numbers here in Denmark. That’s about 16% of the total population, so it’s pretty big. These numbers are unique identifiers for a person (similar to Social Security Numbers) and are a good starting point for identity theft.

Never ascribe to malice that which can adequately be explained by incompetence.

Napoleon Bonaparte


So how did these numbers leak? Through plain incompetence and lack of procedures. It (more...)

SQL and date ranges: don’t use NULL

We often use NULL values in date ranges, usually in the end date to signify the indefinite future. I have decided that this is a bad idea; here are my reasons. 1) NULL should not mean anything According to the SQL standard, NULL means “unknown”. That is why you can’t really compare a NULL value to anything, […]

Thursday Thought – History & Creativity

My sister sent me this video not long after my daughter was born earlier this year - I finally watched it while cleaning up e-mails and I'm glad I did - I highly recommend you all take 2 minutes aside and just let these images of history wash through your brain.

If you think the first half had an air of familiarity, you might be thinking of this video from Carl Sagan - I certainly (more...)


Ein interessanter Hinweis von Jonathan Lewis, der auf eine Präsentation von Markus Winand bei den Trivadis CBO-Tagen zurückgeht (die ich mir auch gern angeschaut hätte, wenn ich mir dergleichen leisten könnte): in manchen RDBMS ist es möglich, mehrspaltige Vergleiche der folgenden Form durchzuführen:

where (col1, col2) < (const1, const2)

Die Logik dabei ist: das Prädikat liefert TRUE, wenn col1 < const1 ist, oder wenn gilt: col1 = const1, aber col2 < const2 (und NULL-Werte (more...)

Fedora VMWare Upgrade

New update of VMWare came out, and it is time to upgrade VMWare Tools. Here’s an update on the instructions for upgrading VMWare Tools:

  1. Navigate to the VMWare Menu, choose Virtual Machine and in the drop down menu Install VMWare Tools. This will mount a virtual CD in the Oracle Unbreakable Linux virtual machine and it launches the following dialog box:

  1. Open a terminal session by right clicking anywhere in the desktop, and then choose (more...)

They Took Away My Cloud

On Monday, a U.S. judge gave Microsoft control of 22 domains owned by domain hosting service Microsoft intended to filter out some domains used by malware, but promptly screwed up. The result was that millions of legitimate users could not access their servers.

This will happen again and again as infrastructure moves to centralized cloud providers. What do you think will happen if the server just above yours in the server rack (more...)

Two milestones, one offer

I stumbled across a blogging milestone while fact checking what I was going to open this post with - for me this month represents 5 years of blogging.

In that time I've learnt heaps, met new people, changed blog name, visited other cities to speak, and dived deep into APEX after a long time in Oracle Forms, published a video series & was recognised as an Oracle ACE.

Turns out Packt Publishing are celebrating (more...)


Yesterday was my first day at 18F!

What is 18F? We're a small, little-known government organization that works outside the usual channels to accomplish special projects. It involves black outfits and a lot of martial arts.

Kidding! Sort of. 18F is a new agency within the GSA that does citizen-focused work for other parts of the U.S. Government, working small, quick projects to make information more accessible. We're using all the tricks: small teams, (more...)

APEX_ESCAPE, a new (and better) way of HTF.ESCAPE_SC

Last week, at the yearly ODTUG Kscope Conference, I did my presentation "Getting Started with APEX Plugin Development". After the session Patrick Wolf, Principal Member of Technical Staff for APEX, pointed out an improvement that could be made.
In the presentation I point out the need to escape the input that you get from a user of the plugin in order to protect the plugin from unwanted use, like SQL Injection, Cross Site Scripting (more...)