Korrektur der Join Cardinality

Laut Randolf Geist ist es "one of the most obvious weak spots of the optimizer: A filter on a table that is joined to another table that has a skewed value distribution in the FK column(s)." Der Satz ist die Antwort auf eine Frage im OTN-Forum, die ein recht kompaktes Beispiel enthielt, bei dem der Optimizer sich hinsichtlich der Cardinality eines Joins massiv verschätzt. Hier eine vereinfachte Version des einfachen Beispiels:

-- 11.2. (more...)

Oracle 12c Release 1 is here!

After much anticipation the new Release 1 of Oracle 12c ( is finally available! Get it here: http://www.oracle.com/technetwork/database/enterprise-edition/overview/index.html What does this mean: Oracle’s ground breaking In Memory Database is now available! As they say…this is BIG. Get more details about In Memory here: http://www.oracle.com/technetwork/database/in-memory/overview/index.html I am very excited about the possibilities of what we can […]

Hiding a Java Source

The ability to deploy Java inside the Oracle database led somebody to conclude that the source isn’t visible in the data catalog. Then, that person found that they were wrong because the Java source is visible when you use a DDL command to CREATE, REPLACE, and COMPILE the Java source. This post discloses how to find the Java source and how to prevent it from being stored in the data catalog.

For example, (more...)

Einfügen von Zeilen aus Excel im SQL Developer

Dass der SQL Developer ein großartiges Tool ist, dem ich womöglich verfallen wäre, wenn ich meine Seele nicht an sqlplus verkauft hätte, habe ich vielleicht gelegentlich schon mal erwähnt. Ein in entsprechender Situation sehr nützliches Detail zeigt Jeff Smith in einem aktuellen Blog-Eintrag: die Möglichkeit, Daten aus Excel per Copy&Paste im SQL Developer in eine Tabelle einzufügen. Das zumindest kann ich mit sqlplus nicht durchführen...

An example of a user-defined aggregate function

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. May be user-defined aggregate functions are not among the most frequent hits in the life on a PL/SQL or APEX developer, but today I wanted to find an elegant solution for a problem submitted by a customer without reinventing the wheel: given a report containing the labor expressed as



Photo by Nate Whitehill

It’s been a few weeks since I returned from another awesome Kscope conference and I just realized that I never wrote about it.

For me, it was the first time visiting Seattle and I really liked it even when I only managed to walk around the downtown area. I had some concerns about how the weather was going to be but everything worked out very well with clear skies, temperature in (more...)

Take Self-Absorption to New Lengths

Observing tourists around me, I’ve noticed that the regular selfie doesn’t cut it anymore. The essential travel accessory for the modern self-absorbed traveller is a selfie stick:

Of course, the whole selfie concept is the opposite of a traditional tourist snapshot: Not “see what I saw,” but “see me”. But while you are at it, why not make yourself a spectacle while you take the picture. Hence, the selfie stick.

The picture is pretty much (more...)

Implizite Typ-Konvertierung und Index-Zugriff

Das Thema wurde sicherlich schon in diversen Blog-Einträgen erläutert - ich war aber zu faul, um danach zu suchen. Außerdem wollte ich hier mal wieder ein kleines Code-Beispiel unterbringen.

Um was es geht, ist Folgendes: Oracle sieht großzügig über fehlerhafte Typ-Angaben bei den Einschränkungen von Abfragen hinweg und korrigiert sie stillschweigend via implizite Typ-Umwandlung. Grundsätzlich ist das eine nette Geste, aber natürlich bringt das Verhalten gelegentlich auf Probleme mit sich. Unter anderem gibt es Situationen, (more...)

Putting my DB / Apex install through the wringer

I was mucking around trying to get APEX on one of my PCs to be visible on the internet.

This was just a proof-of-concept, not something I intend to actually leave running.

EPG on Port 8080

I do other testing on the home network too, so I already had my router configured to forward port 80 to another environment. That meant the router's web admin had been shifted to port 8080, and it wouldn't let (more...)

Charles Hooper über Troubleshooting Oracle Performance

Zu meinen für das laufende Jahr ernsthaft ins Auge gefassten Vorsätzen gehört es, eine Besprechung zum zweiten Band der Poetik des Aristoteles ... - ich wollte sagen: zur zweiten Auflage von Christian Antogninis Standardwerk Troubleshooting Oracle Performance zu schreiben. Da ich dazu aber noch ein paar Vorarbeiten zu leisten habe - zu denen die Lektüre des Buches zählt... -, beschränke ich mich vorläufig auf einen Link auf die umfangreiche und detaillierte (ich könnte auch sagen: geradezu einschüchternde) Besprechung (more...)

The Power of Positive Thinking

He can who thinks he can, and he can’t who thinks he can’t. This is an inexorable, indisputable law. – Orison Swett Marden    Filed under: Quotes Tagged: quote

Are you waiting for the Sure-Thing Boat?

The man who goes farthest is generally the one who is willing to do and dare. The sure-thing boat never gets far from shore. – Dale CarnegieFiled under: Quotes, Work-Life Balance Tagged: Dale Carnegie, quote

Order APEX column based on hidden data

An occasional question in the forums relates to issues ordering a particular column. It's one of those things that will probably keep coming up, so it's worth having another reference out here on the web.

The basic example stems from the need to order data that might contain characters.

with data as
(select '1' vc from dual union all
select '11' vc from dual union all
select '2' vc from dual union all
select 'a' (more...)

Multitenant Validation lab program for ISV partners

Hi Guys, As you all might have noticed from my social moves, we were running Multitenant Validation lab for ISV partners. Briefing on the program, the 2-day guided lab will enable the partners to test and validate their products/applications on Oracle 12c Multitenant architecture. Partners would be able to visualize and get hands-on with Multitenant … Continue reading

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...)