MERGE using Change Data Capture (CDC)

Use MERGE to apply "Change Data Capture" input to a target table with one SQL statement.

SQL Server Admin-Skripts

Wenn ich mich wieder mal als DBA für einen SQL Server ausgeben möchte, sollte ich mich an folgenden Rat von Brent Ozar erinnern:
If you’re a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren’s free database maintenance scripts. They’re better than yours (trust me), and they give you more flexibility than built-in maintenance plans.
Im Fall meiner SQL Server "Skriptsammlung" hat der Herr Ozar jedenfalls recht: (more...)

Merge mit Prüfung auf Ungleichheit

Wenn man im Merge-Statement die Aktualisierung im "when matched" Zweig auf Fälle beschränken will, bei denen sich tatsächlich Änderungen der Daten ergeben haben - was nicht nur aus Gründen der Performance eine gute Idee ist, sondern auch dann, wenn man in ETL-Operationen eine Historie von Veränderungen pflegen möchte, dann kann man dazu eine Where-Bedingung verwenden, die die Gleichheit neuer und existierender Werte für alle betroffenen Felder überprüft. Zu diesem Zweck habe ich in der Vergangenheit (more...)

Join Cardinality mit Like-Vergleichen

Noch ein wichtiger Hinweis aus dem Scratchpad von Jonathan Lewis: zwischen den Releases 11.1.0.7 und 11.2.0.4 hat sich die Arithmetik der Berechnung der Cardinality beim Join über Like-Operatoren verändert: in der älteren Version wurde mit dem Standard-Wert von 5% für Vergleiche des Typs "column >= unbekannterWert" gerechnet, aber in 11.2.0.4 ist ein Bug-Fix am Werk, dessen Beschreibung lautet: "use 1/NDV+1/NROWS for col1 LIKE col2 selectivities" - (more...)

Adaptive Reoptimization

Vermutlich habe ich schon mal darauf hingewiesen, dass mir die diversen in 12c eingeführten Reoptimierungs-Optionen in ihrem Zusammenspiel recht komplex erscheinen - und dass ich fürchte, dass dabei Optimizer und analysierender Entwickler (also ich) unter Umständen den Überblick verlieren könnten. Immerhin hat sich jetzt Tim Hall die Mühe gemacht, einige Überblicksartikel zum Thema zu veröffentlichen - und da der Herr Hall in solchen Artikeln häufig prägnanter und pointierter erklärt als die Dokumentation, ist diese Einführung (more...)

Goodbye Flybe, Hello Oracle!

update people
set    employer = 'Oracle',
       job_title = 'Database Evangelist'
where  name = 'Chris Saxon';

That’s right, as of last week I’m now an Oracle employee! I’m joining Steven Feuerstein’s database evangelist team, a hugely exciting opportunity I’m honoured to be a part of. I’ll be joining Natalka, Dan and Todd in helping people customers get the most out of their Oracle databases.

My focus will be SQL and Oracle’s differentiating features (more...)

MERGE magic and madness

Using the MERGE statement, you can insert into, delete from and update the same table all at once: that is the magic. If you don’t pay attention, you can also make the database do a lot of unnecessary work: that is the madness! I’ve blogged a lot about comparing tables, then using MERGE to synchronize them. […]

Continuing the story

One of my 2015 new year's resolutions, was to finish the story I started on this blog. The story being a talk that I had delivered a couple of times 3-4 years ago on various Oracle/usergroup events. The talk is about why I think triggers should *not* be considered harmful. There is one specific use-case for triggers -- implementing validation code for what's called a multi-row constraint -- that I will treat in-depth on this (more...)

Happy New Year and Thanks

Happy New Year to all those kind enough to read my blog! 2014 was the year I started blogging and presenting at Oracle-related conferences. It has been a great experience. I enjoy studying specific problems in depth and being able to correct or improve my efforts without constraints. Thanks very much to all those who […]

SQL with Friends ?

I'm a regular player of the WordsWithFriends game from Zynga. With some of my regular opponents, we have some side chat. That might be something as simple as letting them know you won't be playing for a few days, or a joke arising from an odd sequence of words.

Recently I'd been sent an URL as a chat message, with a picture from a holiday. It was quite a long URL, with a dubious few (more...)

Popular Programming Languages

First of all, Happy New Years!

IEEE Spectrum published a ranking of the most popular programming languages. Computational journalist Nick Diakopoulos wrote the article. While it may surprise some, I wasn’t surprised to find SQL in the top ten.

07dataflow-1403643424680Nick weighted and combined 12 metrics from 10 sources (including IEEE Xplore, Google, and GitHub) to rank the most popular programming languages.

  • Compiled programming languages (Java [#1], C [#2], C++ [#3], C# [#4], Objective-C [#16])
  • Interpreted (more...)

Oracle 12c column upgrades

While playing with 12c I tried the upgrade to the DEFAULT column syntax that now allows sequences.

I came across a basic error, but it's just a small trap for new players.

CREATE TABLE seq_test(a NUMBER)
/

ALTER TABLE seq_test MODIFY (a NUMBER DEFAULT sage_seq.NEXTVAL)
/

SQL Error: ORA-02262: ORA-2289 occurs while type-checking column default value expression

*Cause: New column datatype causes type-checking error for existing column
default value expression.
*Action: Remove the default (more...)

UKOUG Annual Conference (Tech 2014 Edition)

The conference

This year the UKOUG's tour of Britain's post-industrial heritage brought the conference to Liverpool. The Arena & Convention Centre is based in Liverpool docklands, formerly the source of the city's wealth and now a touristic playground of museums, souvenir shops and bars. Still at least the Pumphouse functions as a decent pub, which is one more decent pub than London Docklands can boast. The weather was not so much cool in the 'Pool (more...)

SQL Brainteasers: Guess the Mythical Creature

Another batch of SQL brainteasers. This time the clues are to the powers of mythical creatures – the task is to guess the creature!

There’s six in all, can you get them all? Put your answers in the comments!

update target_of_gaze
set    state = 'stone';

begin
  delete from creature
  where  appendage = 'head';

  insert into creature (appendage)
  values ('head');

  insert into creature (appendage)
  values ('head');
end;
/

update  (more...)

Speakers looking for New Year’s Resolutions?

If you're a semi-regular speaker looking for a new year's resolution, how about learning this little ditty?



I never opened the clip of Daniel Radcliffe doing the same song not long ago on Jimmy Fallen - I don't really watch his movies I skipped by, but I did get baited by something on Facebook mentioning the reporter's (Kim Powell) a capella warm-up method.

Turns out it's a 1999 rap by Blackalicious, lyrics (more...)

I have an idea for an app, what’s next?

This is a question that I get asked quite often.  My first thoughts are:

1. An app isn't necessarily a business.
2. Can you generate enough revenue to pay for the development?
3. There is usually more to an app than just the app.
4. Which platforms?

I thought I'd take this opportunity to address each of these points in more detail.  Before I do this, I think it's important to say that I don't (more...)

Data Warrior 2014 in Review

The WordPress.com stats helper monkeys prepared a 2014 annual report for my blog. Not as many posts as 2013 (I was way busy), but sure got a lot more traffic than I realized! Here’s an excerpt: The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 58,000 times in […]

Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions (more...)

Schema Synonyme

Es gibt nicht viele Dinge, die in anderen RDBMS existieren und die mir bei Oracle ernsthaft fehlen - aber wenn ich mir ein solches fehlendes Feature wünschen dürfte, dann wäre die Möglichkeit, ein Schema umzubenennen, ein guter Kandidat. Möglicherweise wird mir dieser Wunsch in absehbarer Zeit erfüllt, denn Franck Pachot hat einen Underscore-Parameter entdeckt, mit dessen Hilfe sich die Erzeugung eines Befehls CREATE SCHEMA SYNONYM aktivieren lässt. Nun ja: eine Möglichkeit der Umbenennung wäre mir (more...)

Querying InnoDB Tables

Somebody ran into the following error message trying to query the innodb_sys_foreign and innodb_sys_foreign_cols tables from the information_schema database:

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

It’s easy to fix the error, except you must grant the process privilege. It’s a global privilege and it should only be granted to super users. You grant the privilege (more...)