APEX World 2014

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

Denormalizing for Performance Is a Bad Idea

Continuing the series on joins, I’m going to look at denormalization. This process reduces the number of joins necessary to return results for a schema.

One of the big arguments against normalizing data is “for performance”. The process of normalization creates new tables as relations are decomposed according to their functional dependencies. This means (more) joins are necessary to return the same results.

A google of “database normalization performance” turns up several articles like this (more...)

DBMS_JAVA Privilege Error?

It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.

You grant privileges to external file systems as the sys user with the grant_permission procedure of the dbms_java package, like

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read' (more...)

Better Data Modeling: My Top 3 Reasons why you should put Foreign Keys in your Data Warehouse

This question came up at the recent World Wide Data Vault Consortium. Seems there are still many folks who build a data warehouse (or data mart) that do not include FKs in the database. The usual reason is that it “slows down” load performance. No surprise there. Been hearing that for years. And I say […]

Log-Dateien parsen mit Postgres

Gerade ist mir klar geworden, wie handlich das Parsen von Log-Dateien in der postgres-Datenbank ist. Der gesamte Vorgang beschränkt sich auf folgende Schritte:

-- Anlage einer Tabelle mit einer einzigen Spalte (vom Typ TEXT)
create table my_log(log_line text);

-- Einlesen via copy-Kommando
copy my_log(log_line)
from 'pfad_der_log_dateien/log_datei.txt';

-- Parsen der Eingabe-Strings mit Hilfe der Funktion split_part in beliebig viele Abschnitte
-- „Split string on delimiter and return the given field (counting from one)“
select split_part(log_line, (more...)

In Defense of Joins

Joins appear (to me) to be getting a lot of bad press recently. In discussions I’ve had and articles I’ve read many give the position that joins are somehow inherently bad and to be avoided at all costs.

I’ve never been entirely convinced by the “joins are bad” arguments however. Partly because there’s few concrete cases actually demonstrating the (additional) cost of joins. Instead discussions tend to be hand-wavy arguments around extra CPU cycles used (more...)

Langwierige Statistikerstellung mit auto_sample_size

Nur ein knapper Hinweis: Jonathan Lewis zeigt, dass die Verwendung der auto_sample_size seit 11g in Kombination mit der Ermittlung des approximate NDV Mechanismus zu unerwartet langen Ausführungszeiten bei der Statistikerfassung führen kann. Betroffen sind:
  • Auf LOBs basierende virtual columns mit zugehörigen Indizes. Dabei wird das Ergebnis des Funktionsaufrufs im Index gehalten, aber in der Tabelle erscheint nur die Funktionsdefinition, so dass Oracle bei der Statistikerstellung den Funktionsaufruf für jede Zeile ausführen muss - und wenn (more...)

Remote Surprise

Here is an example of surprising behaviour from a remote DB from an OTN forum thread

Setup a link to a remote DB (I’ve used an actual remote DB and not tested a loopback)

Remote DB:

create table t1
(col1  varchar2(1));

Local DB:

create or replace view v1 
as 
select count(*) c1 from t1@l1;

Then alternate variations on this sequence of events:

1. On local DB execute SELECT:

SELECT * FROM v1;

2. On remote (more...)

Don’t Automate Half-Way

I watched in horror as the updated application was deployed to the test server. The application was fine, having been subjected to the first test by the developers, but the deployment process was bad. There were various home-built utilities to run with very specific parameters in order to build the deployment package that would then be semi-automatically installed on the test server. It took a new developer a whole day and 20-30 attempts to build (more...)

Advert: Packt celebrates 2000 books

No APEX5 blog post today, but it turns out today is the final day of Packt Publishing celebration of 2000 titles since their first book was published in 2004.
I like puns
I have the honour of being one of these titles with my Oracle APEX Techniques video series, and I've had the pleasure of reviewing a number of their books.

They have a buy one get one free offer but it ends today - (more...)

SQL Server 2014

Wie man im Official Microsoft Blog erfährt, läuft der SQL Server 2014 zum 01.April 2014 feierlich vom Stapel, was immerhin früh genug ist, um den Namen nicht zur Farce werden zu lassen.

Come back when we’re open

I subscribe to Microsoft Office 365 and had a payment stuck on an expired credit card. Even though there was a new card, the billing system kept trying the old one.

In a modern cloud service, I would open a service request and expect things to be handled for me. But this is Microsoft. And with the arrogance of the monopolist they used to be, they require you to please show up when they feel (more...)

APEX 5 first peek – What I missed

When tackling early adopter versions I've enjoyed having a play before reading too much about it. I did look through the EA1 feature descriptions to get a feel for the sort of things to expect, but I waited before watching David's videos to see how easy it was to adapt to the changes - even though I wasn't concentrating on the new page designer itself.

It was certainly worth watching the videos in the end because (more...)

ADF Book Bundle – get one free!

Packt Publishing has a “buy one, get two” offer that runs until Wednesday. If you don’t have my ADF books, this would be a great opportunity to buy my new book Oracle ADF Enterprise Application Development – Made Simple: Second Edition and get a free copy of Developing Web Applications with Oracle ADF Essentials (or the other way around ;-)

Quiz: What’s wrong with this update?

The following update forms the basis of a DIY connection pool. It is intended to allocate an unused session to whoever requests it:

declare

  sess connection_pool.session_name%type;
  
begin
  
  update connection_pool
  set    in_use = 'Y',
         last_used = systimestamp
  where  rowid in (
      select rowid 
      from   (
          select rowid
          from   connection_pool
          where  in_use = 'N'
          order  by last_used
      )
      where  rownum = 1
  )
  returning session_name 
  into      sess;
  
  dbms_output.put_line ( sess );
  
 (more...)

APEX 5 first peek – Features not yet implemented

It's an early adopter release, the APEX team have come a long way, yet there are still goodies to come.

What's on it's way

Some features aren't quite ready yet. Some you'll see, others you'll find out when you click on them - so no doubt we'll see a second, maybe even third early adopter release.

Notifications

The first you may notice if you've had a play is the notification message - this will be (more...)

Live from the 1st Annual World Wide Data Vault Consortium: Day 3

Well it was the last day of the 1st annual WWDVC. What an event is has been. Don’t forget you can see all the action by searching on #WWDVC on twitter. Agility and Data Vault Long time data vault advocate Tom Breur opened our closing day with a talk about how we should strive to […]

Overlapping ranges with priority

A few years ago, Alberto Dell’Era blogged about product prices with overlapping date ranges; “on an overlapping range, the strongest priority (lower number) wins.” His analysis, explanation and solution are all excellent. I’m revisiting the subject to suggest a few improvements (and mention MATCH_RECOGNIZE yet again). To explain more easily, Alberto uses letters (a,b,c,d,e) instead […]

Live from the 1st Annual World Wide Data Vault Consortium: Day 2

Wow has is been an amazing event with many amazing people from around the globe. I am VERY glad that I took the time and came to St Albans for this inaugural event. As with Day 1, there is just too much great information for me to adequately cover it in a blog post (or […]

Optimizing Oracle Performance von Cary Millsap

Amazon hat mich gebeten, mein kürzlich erworbenes Exemplar des Buches zu rezensieren, und bei solchen Anfragen neige ich zu Brechts: "Die etwas fragen/ Die verdienen Antwort". Und wenn ich es dort veröffentliche, dann kann ich es auch hier unterbringen:

Nachdem mir vor kurzem aufgefallen war, dass ich kein Exemplar von Cary Millsaps Standardwerk mehr besaß, habe ich mir noch mal eines gekauft und es auch noch einmal gelesen - und es gibt nicht viele Oracle-Bücher, (more...)