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.

RETURNING RECORD INTO

This is an idea for an enhancement to the PL/SQL syntax.

If I have the following declaration:

DECLARE
  in_record mytable%ROWTYPE;
  out_record mytable%ROWTYPE;
BEGIN

I can do this:

  INSERT INTO mytable VALUES in_record;

I can also do this:

  UPDATE mytable SET ROW = in_record WHERE ...;

I can do this, as long as I list each and every column, in the right order:

  INSERT INTO mytable VALUES in_record
  RETURNING cola, colb, colc INTO out_record;

But I (more...)

Ausführungspläne erzeugen und interpretieren

Bisher habe ich auf die Frage nach einer guten Erklärung für Ausführungspläne in der Regel auf Troubleshooting Oracle Performance von Christian Antognini verwiesen - oder auch auf die im Netz verfügbare (und hier verlinkte) gekürzte Version des sechsten Kapitels. Als Alternative oder Ergänzung dazu kommt inzwischen eine gerade von Jonathan Lewis für AllThingsOracle begonnene einführende Serie in Betracht:

Beyond Analytics: MODEL or MATCH_RECOGNIZE

Analytic functions are powerful and efficient, but sometimes they just aren’t enough. When you try analytics and they alone don’t solve the problem, it’s time to think about the MODEL clause – or upgrade to 12c and use MATCH_RECOGNIZE. All three can use partitions and ordering for simpler, more efficient processing. To illustrate, here’s a […]

Datenbank-Videos von Jens Dittrich

An der Universität des Saarlandes habe ich allerlei studiert, aber keine Informatik - trotzdem hätte ich auf die ebenso umfangreiche wie interessante Sammlung der Youtube-Videos von Jens Dittrich vielleicht schon früher mal hinweisen können. Hier findet man Einführendes aber auch komplexe Detailanalysen - und dabei zeichnen sich die Beiträge insbesondere auch durch die unterhaltsame Präsentation aus, für die der Herr Professor Dittrich schon allerlei Preise gewonnen hat. Einmal mehr muss ich bei Carl Einstein borgen: (more...)

Current_Schema and the Data Dictionary

Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.

The regular install script for Logger looks something like the following (parts removed and table names are changed):


set (more...)

Dutch Cars – Technology That Fits

I was just in Amsterdam last week, and they have the smallest cars I have ever seen:

small_carAt first, it looks counter-intuitive, given that the average Dutchman seems to be several meters tall. But really small cars is actually a very obvious solution for a crowded city like Amsterdam with many canals, narrow roads and very limited parking. These mini-cars are actually parked on the pavement, probably avoiding the 5 Euro per hour parking fee (more...)

When Joins Go Bad

So far in the joins series we’ve looked at the effect removing joins (via denormalization) has on performance. We’ve seen that joins can cause primary key looks to do more work. Lowering the normalization level to remove these can negatively impact “search” style queries though. More importantly, we’ve seen the real cost of denormalizing to remove joins is when updating records, potentially leading to concurrency waits and application bugs.

So are joins always “good”?

The (more...)

Änderung für NVL costing

Jonathan Lewis weist in seinem Blog darauf hin, dass die cardinality für Prädikate der Form: NVL(spalte, konstanter_datumswert) bis Version 11.1.0.7 mit dem Standardwert "5% for range-based predicate on function(col)" berechnet wurde, aber in 11.2.0.4 eine plausiblere Behandlung erfährt, die das Prädikat in den NULL-Fall und den Fall des Vergleichs der vorhandenen Werte aufspaltet, und dadurch deutlich zutreffendere Schätzungen liefert (was für ähnlich gelagerte Fälle auch schon in früheren Releases (more...)

This just in: Win Dinner with Monty at #KScope14

Amazing but true – you can now enter a contest to win dinner with ODTUG President Monty Latiolais at ODTUG’s annual conference KScope14. This year KScope will be held in beautiful Seattle, Washington from June 22nd – 26th. Who knows what amazing dinner adventure will be in store for the winner! Get the details here: […]

Unusable unique constraint

Another annoying thing about unusable indexes

I’m surprised that I can’t remember coming across this before before.

I want to archive some data from a bunch of partitions.

Unfortunately, I can’t follow the virtuous circle of data lifecycling and partition the table in such a way that I can archive all the data from a particular partition (or subpartition) via exchange partition.

Without going into too much detail and skipping a bunch of other steps, (more...)

Thinking it Through – Nest Smoke Alarm Fail

The company Nest, recently acquired by Google for the usual billions, makes smart thermostats and smoke detectors. Unfortunately, they did not think through the user experience of their Nest smoke and CO detector.

In principle, it’s great that you can turn off your smoke detector by waving your hand at it – like in “oh, cut it out, I just overcooked my microwave popcorn a bit.”

Less great is that if people experience an (more...)

Down with Firefox

Warning: this post is not technical and it is not about Oracle. Brendan Eich recently resigned under pressure from his job as CEO of Mozilla, the makers of Firefox. The reason given was a campaign contribution that Mr. Eich made in 2008. The State of California has the “referendum”: a proposition is submitted to a […]

APEX Shortcuts Use Case

Recently Martin described APEX Shortcuts, a shared component that I don't often use - and probably even less now.

APEX wizards create them for the delete process in forms, but I thought I'd describe an example of how you might use one for your own needs.

The sole purpose I've used them is to generate HTML text from PL/SQL - often for links to appear near text items. For example, you could use them (more...)

Denormalizing for Performance is a Bad Idea – Your Updates are Killing Me

In the previous article in the joins series we compared query performance between a third normal form schema and the same schema denormalized to second normal form. We then extended it the example so our denormalized schema was in just first normal form.

The normalized approach performed better overall. The differences were small though – generally just a few consistent gets and all the queries executed in under a second. As Jeff Atwood points out (more...)

PL/SQL vs SQL

plsql_logoThere is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.

The function defined is something like this: