SQL Brainteasers: Guess the Chocolate Bar

Some more SQL clues, this time to chocolate bars.

select iso_code
from   countries
where  capital_city = 'Taipei'
and    9 = roman;

select *
from   planets
where  position = 4;

select characters
from   thundercats
where  age = 'prepubescent';

select *
from   ships
where  crew = 'mutinous';

select *
from   cities
where  that_is = 'Eboracum';

select min(volume)
from   speech;

When you’ve got them, put your answers in the comments!

Compare and Sync without Primary Keys

I have written a lot about comparing and synchronizing tables. My examples always had both primary keys and non-key columns, so I could do updates along with inserts and deletes. What about the other tables? Here's a technique that works for them.

Convert to SQL Server?

I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a system_user name. The alternative, would be to convert the system_user table (more...)

Sync tables: generate MERGE using Unique constraint

In my post “Compare and sync tables: Generating the code“, I use the primary key constraint on the target table. A reader called “Bal” asked how to use a unique constraint instead. Test data I’m going to create a target table with a unique constraint, but without any NOT NULL constraints on the columns. UK1 […]

Parallel Query, OR Expansion und überflüssige Buffer Sorts

In den letzten Tagen hat Randolf Geist zwei Artikel zum Auftreten von überflüssigen Buffer Sort Operationen bei der Ausführung paralleler Queries veröffentlicht:

APEX5 Early Adopter 3 is nigh

I get this email that tells me apexea.oracle.com is about to be fitted out with an even slicker APEX5 Early Adopter 3.
It's coming soon to a browser near you
Stay tuned...

Are you ready to learn something new in 2015?

We all know the saying: When the student is ready, the teacher will appear My advice is to empty your cup, daily, so that when the teacher appears you will recognize them. Unless we are humble in our hearts and in our spirit, we are not open to new things and to learning. The teacher […]

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

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