Making SQL Great Again (SQL is Huuuuuge) Part VI

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

Making SQL Great Again (SQL is Huuuuuge) Part IV

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

Making SQL Great Again (SQL is Huuuuuge) Part V

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

An old dog learns a new trick

Reports of this blogs death have been greatly exaggerated. It has been very quiet here though while I worked on getting the Swedish part of Miracle started. It is now rocking the Stockholm market so it’s time to get back into more geeky stuff.

Talking of which. I have encountered Liquibase for database versioning time after time and always come to the conclusion that it is not what a DBA want to use. I recently took (more...)

Talking SQL Analytics with Connor

In about 22 hours I'll be talking with Oracle Developer Advocate (or the southern/eastern hemisphere AskTom adjunct) Connor McDonald about one of my favourite part of Oracle, being SQL Analytics.

If you haven't seen any of his YouTube videos recently, this will be a good chance to catch up and see how easy and practical SQL analytics are, and how you end up with simpler SQL.

I like to think of them like Excel calculations (more...)

Kim Berg Hansen on “Use Cases of Row Pattern Matching in Oracle 12c”

As I write this, I am listening to Kim Berg Hansen explain the MATCH_RECOGNIZE clause. He was kind enough to give me credit for some of the examples and mention this blog. In addition to my blog posts on the subject, you may enjoy my presentation on SlideShare. Please download it to see the animations!

Zerlegung von Tabellen in rowid ranges

Stew Ashton ist derzeit für einen signifikanten Anteil der ungelesen in meinem Blog-Reader wartenden Artikel verantwortlich, da er gerade eine vielteilige Serie zum Thema "Chunking tables" veröffentlicht hat (veranlasst durch eine entsprechende OTN-Anfrage von Jonathan Lewis bzw. White paper Bezugnahme von Bryn Llewellyn). Die genaue Anzahl der Artikel lasse ich aus, um diese Einleitung generisch zu halten und spätere Korrekturen der Anzahl vermeiden zu können. Freundlicherweise pflegt der Autor im ersten Artikel eine Liste der (more...)

SQL: Texte mit Umlauten und Sonderzeichen normieren

Im heutigen Beispiel möchte ich einmal kurz aufzeigen, wie Sie einen Text nach Ihren Bedürfnissen hin normieren können. Ich habe dazu die bekanntesten Länder der Erde in einer WITH Clause zusammengefasst und bilde mir anhand des Namens einen später verwendbaren normierten String.

Der Zielstring muss GROSSGESCHRIEBEN sein und darf keine Umlaute (ÖÜÄöüäß) und Sonderzeichen (, '.()-) beinhalten.
Mehr »

if you write SQL, be specific!

Today  I got a ticket from a developer where he claimed Oracle has a bug somewhere. The reason for this claim was a statement similar to
SELECT *
FROM   table1
WHERE  tab1col1 IN
       (      SELECT 
tab1col1 
              FROM   table2)


This query returned rows.

But when he run the inner query on it's own, he received
ORA-00904: "TAB1COL1": invalid identifier
00904. 00000 -  "%s: invalid (more...)

Chunking tables 8: second solution

Here is the second complete solution to the problem of dividing a table into chunks of equal size. This is the solution whose first version was marked "correct" on OTN. It is the basis for what Bryn Llewellyn calls "Approx_Method_Sql_Ashton_2" in his white paper.

Join mit Datums-Bereichen

Wenn man mit SQL arbeitet, dann gehört die Behandlung von Datumsintervallen zu den Themen mit eher beschränktem Unterhaltungswert. Insofern ist es sehr nützlich, dass Stew Ashton eine Artikelserie veröffentlicht hat, in der er diverse Spezialfälle genauer beleuchtet, mit denen man in diesem Kontext konfrontiert werden kann:

Chunking tables 7: prior sys_guid() ???

The second solution for dividing a table into equal chunks does not do a JOIN. Instead it expands extent rows that contain several chunk boundaries, using an obscure method that I need to explain.

Chunking tables 6: JOIN solution

At last, here is the first complete solution to the problem of dividing a table into 12 chunks with equal numbers of blocks. James Su and I proposed this solution on OTN, but without the "filtered_extents" optimisation.

Chunking tables 5: chunk boundaries

We still want to split a table into 12 chunks with equal numbers of blocks, so we can access each chunk using a rowid range. We have the extents we need and now we need to calculate the exact chunk boundaries.

Chunking tables 4: Histograms

After "only" 3 posts on chunking tables, we now have the raw materials for breaking up a table into 12 chunks with equal numbers of blocks, and for getting ROWID ranges that let us access the data. Now let's chunk!

Chunking tables 3: working with blocks

So far we've decided to access a table chunk by chunk, each chunk containing one twelfth of the tables's blocks. But first, how do we access data in a chunk of blocks? How do we even know what blocks a table has?

Show all views including a specific string

Seems to be a simple problem and easy to fix. Actually it is not because if you try one of these examples then you will fail:
Mehr »

Chunking tables 2: Requirement

As I mentioned in my previous post, the basic idea is to divide a table into 12 "equal chunks", each "chunk" being a rowid range that covers the same number of blocks (give or take 1). What does this mean? Why do this? Why divide by blocks and not rows? Here are my thoughts.

Primarys Keys and their supporting indexes

Sometimes things just happen which makes you realise that stuff you thought all experienced DBA’s knew about isn’t common knowledge. I suppose it’s a side effect of working with the same evolving product for over 25 years at dozens of clients, when the colleague you are talking to has a mere decade of experience at fewer companies (and therefore less diversity of challenge).

Today I noticed that a release was creating a table, then an (more...)

Oracle Spatial (Teil 3) – Entfernung von Punkten berechnen

Im heutigen Blogpost möchte ich kurz anhand eines Beispiels aufzeigen, wie Sie die Entfernung von SDO_GEOMTRY Punkten berechnen können. Hierbei gehe ich auch konkret auf das Problem des Verbindens von zwei Datenmengen anhand einer definierten Entfernung ein.
Mehr »