Korrigierte CBO-Cardinality-Schätzungen für OR-Prädikate

Stefan Koehler weist in seinem Blog darauf hin, dass ein von Jonathan Lewis in Cost Based Oracle beschriebenes Problem in aktuelleren Releases behoben ist: OR-verknüpfte Range-Prädikate, die den kompletten Wertebereich einer Tabelle umfassen, werden nun in ein einfaches IS NOT NULL Prädikat umgewandelt. Das handliche Beispiel dafür ist in Blog und Buch eine Monatsliste mit der dafür definierten Einschränkung:
where MONTH_NO > 8 OR MONTH_NO <= 8
In 10.2.0.5 führte diese Ausgangslage (more...)

AU/X, NeXTSTEP, and Mac OS X

One thing that gets tedious in the IT community and Oracle community is the penchant for Windows only solutions. While Microsoft does an excellent job in certain domains, I remain a loyal Apple customer. By the way, you can install Oracle Client software on Mac OS X and run SQL Developer against any Oracle Database server. You can even run MySQL Workbench and MySQL server natively on the Mac OS X platform, which creates (more...)

DIY Disasters

When people take on home renovation “Do-It-Yourself” projects beyond their skills, disaster ensues. Apparently, this is so common that you can base a whole TV series on this theme – where professionals rescue the disastrous DIY project for the grateful and clueless amateur handyman.

I’ve seen the same thing happen in many IT projects. The people in the organization overestimate their own skills and are unable or unwilling to pay the cost of professional external (more...)

I Love Logs

It occurred to me a few days ago, as I was reading this article on DevOps, that I might actually be a DevOps.

I think of myself as a developer, but my current role is in a small team running a small system. And by running, I mean that we are 

  • 'root' and 'Administrator' on our Linux and Windows servers
  • 'oracle / sysdba' on the database side, 
  • the apex administrator account and the apex workspace (more...)

Zur Semantik der ON clause im ANSI Left Outer Join

Vielleicht wäre es sinnvoller, auf diesen Eintrag zu verzichten, weil er ein wenig peinlich ist - aber was soll's: gestern ist mir im Rahmen eines OTN Threads aufgefallen, dass meine Interpretation der ANSI OUTER JOIN Syntax bislang unzutreffend war. Ich verzichte auf nähere Erläuterungen zu meiner Fehleinschätzung (die für meine übliche Formulierung von ANSI OUTER JOINs ganz plausibel war), und erkläre lieber gleich, was das ON tatsächlich bedeutet:

drop table t1;
drop table t2;

create (more...)

SQL Developer’s Interface for GIT: Interacting with a GitHub Repository Part 2

In this post I’m going to show to synchronize the remote and local repositories after an existing file in local gets modified. What I’ll do is modify the sp_test_git.pls file in our local repository and then push those changes to the remote repository (GitHub).

First, I proceed to open the sp_test_git.pls file using SQL Developer, add another dbms_output line to it and save it. The moment I save the file, the Pending Changes (more...)

Show APEX IR detail view by default

Since the Detail/Icon views were introduced to Interactive Reports in Oracle APEX 4.0, I've used the detail view a number of times to represent information in ways that don't even look like your standard columnar reports.

One example you may have seen was the original apexblogs.info - this was a detail view IR showing aggregated posts of APEX bloggers.
It's now hosted at odtug.com/apex and this appears to use an alternative method (more...)

Unfocused Learning

The manager was proudly showing off his new IT classroom.

“Where is the projector?” I asked.

“Oh, we don’t need a projector. The instructor can just take over everybody’s screens.”

I have been teaching in environments like this, and it does not work well. When I have a piece of information or a computer screen projected in large format in front of everybody, the class has a common focus on the task. If (more...)

Read Committed Isolation Level im SQL Server

Im SQLPerformance.com Blog hat Paul White einige interessante Punkte zur Implementierung des Isolation Levels Read Committed im SQL Server aufgeschrieben. Grundsätzlich gibt es im SQL Server dieser Tage zwei Implementierungen für Read Committed, von denen die ältere (zumindest vom Herrn White) als Locking Read Committed bezeichnet wird. Da der Autor einen weiteren Artikel zum Thema ankündigt, starte ich an dieser Stelle eine Aufzählung:

Best UTL_FILE Practice

In a post a couple days ago, I promised to provide a best practice approach to reading external files with the UTL_FILE package. My first assumption is that you’re reading unstructured data because structured data is best read by external tables because external tables can read data much faster with the PARALLEL option.

My second assumption is that you’re you don’t know how to use or choose not to use the DBMS_LOB package; specifically, (more...)

IT Defence in Depth

The Heartbleed bug has shown that security vulnerabilities can pop up everywhere. Unfortunately, many IT organizations depend on a single security layer to secure their network – and as the ineffectiveness of the Maginot Line proved, that is a risky strategy. You need multiple security layers – what soldiers call Defence in Depth.Security LayersThis illustration is from my weekly Technology That Fits newsletter – sign up here.

Using UTL_FILE

Sometimes I’m surprised. Today, the surprise came when somebody pointed to an error in another author’s book. The person who asked the question had to send me a screen shot before I believed it.

The author’s code encounters the following error because the code was designed to loop through a multiple line file, and the code called the UTL_FILE.FOPEN procedure with three instead of four parameters:

DECLARE
*
ERROR at line 1:
ORA (more...)

Unique identifiers – but what do they identify

Most of the readers of this blog will be developers, or DBAs, who got the rules of Normalisation drummed into them during some phase of the education or training. But often we get to work with people who don't have that grounding. This post is for them. Feel free to point them at it.

Through normalisation, the tendency is to start with a data set, and by a methodical process extract candidate keys and their (more...)

In Defense of Joins – Recap

Over the past few articles we’ve looked at database joins. It started out with me noticing that joins appear to be getting bad press recently and wondering whether they really are the root of all evil that some people seem to think they are.

We seen that denormalizing removes joins, giving performance benefits to primary key lookups. This benefit can come at the cost of non-primary key lookup queries though – often the extra (more...)

Good IT hygiene

Everybody knows they need good dental hygiene – daily brushing and flossing, regular checkups at the dentist. But many IT professionals don’t realize they need good IT hygiene as well – regular maintenance, security patches, etc.

If you don’t practice good IT hygiene, you will experience pain down the line. I’d like to help you avoid that – get in touch.

The Riley Family, Part III



That's Mike and Lisa, hanging out at the hospital. Mike's in his awesome cookie monster pajamas and robe...must be nice, right? Oh wait, it's not. You probably remember why he's there, Stage 3 cancer. The joys.

In October, we helped to send the entire family to Game 5 of the World Series (Cards lost, thanks Red Sox for ruining their night).

In November I started a GoFundMe campaign, to date, with your help, (more...)

Toolmaking

My carpenter has been putting in a new floor in a room in my house, and I noticed that he makes some of his tools as he goes along.

It’s not that he doesn’t have a hammer and a cordless electric screwdriver. But every once in a while, he needs to move, align or support something in a way that his standard tools do not support. So he immediately builds an ad-hoc tool out of (more...)

Oracle Application Express (APEX) 4.2.5 is now available for download

A new minor release of Oracle Application Express (APEX) has been announced yesterday. Version 4.2.5.00.08 is now available for download. Check out the accompanying documentation for new features and bugs fixed in this release. You may also find interesting reading Joel's blog posting about Apex 4.2.5.

SQL Developer’s Interface for GIT: Interacting with a GitHub Repository Part 1

In my previous post, I showed how to clone a GitHub repository using SQL Developer. In this post I’m going to show to synchronize the remote and local repositories after remote gets modified.

Here I use GitHub to commit a file called sp_test_git.pls.  You can create files by clicking on the icon the red arrow is pointing to.

new_file

The content of the file is a PL/SQL procedure that prints a message.

file_content

At this (more...)

Information Evaporation

Have you noticed that knowledge about IT systems seems to evaporate over time? I’ve seen quite a few systems where the only knowledge left was a few pages of yellowed paper in a dusty binder.

Information EvaporationHowever, in some application systems, this does not happen. Why is that? Because the information about what the system does is stored in the code.

Keep system documentation inside your code to prevent information evaporation.