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
FROM   table1
WHERE  tab1col1 IN
       (      SELECT 
              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.

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.

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 »

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 »

Importing XML file with invalid character 22 (U+0016)

I have to import a set of XML files from time to time. Most of those XML files can be imported with out any problems. But at least one file includes a special character U+0016 which occurs randomly some where inside the file.

When I try to import that file I get this ORA- error message:
ORA-31011: XML-Parsing nicht erfolgreich
ORA-19202: Fehler bei XML-Verarbeitung
LPX-00217: Ungültiges Zeichen 22 (U+0016)
Error at line 39409 aufgetreten

Mehr »

Using Apache Drill REST API to Build ASCII Dashboard With Node

Read this article on my new blog Apache Drill has a hidden gem: an easy to use REST interface. This API can be used to Query, Profile and Configure Drill engine. In this blog post I will explain how to use Drill REST API to create ascii dashboards using Blessed Contrib. The ASCII Dashboard looks like Prerequisites Node.js Apache Drill 1.2 For this post, you will use the SFO

Did it really fix it 1: OFE & nasty FIRST_ROWS plan on

Plan degradations on upgrade are normal.

This one’s no different.

On further investigation, turned out application was setting optimizer_mode = first_rows somewhere.

First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode.

What does FIRST_ROWS mean?
From 11g doco:

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

If any sort of (more...)

SQL Tuning: Thinking in Sets / How and When to be Bushy

Below is a SQL statement from a performance problem I was looking at the other day.

This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.

You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best (more...)

Updating Identity Columns

During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Indentity Columns:

Is it possible to update an Identity Column?
During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:

SQL> create table t
2 (id number generated as identity
3 ,name varchar2(35)
4 );

Table created.


Fundamentals of SQL Writeback in Dodeca

One of the features of Dodeca is read-write functionality to SQL databases.  We often get questions as to how to write data back to a relational database, so I thought I would post a quick blog entry for our customers to reference.

This example will use a simple table structure in SQL Server though the concepts are the same when using Oracle, DB2, and most other relational databases.  The example will use a simple Dodeca (more...)

Add Color to your SQL

Topic: this post is about some simple techniques to add color to SQL scripts and their terminal output using ANSI escape codes.

Colors can be used to improve the output of command line tools. This is common practice, for example with the (bash) shell. Colors can also be very useful to improve the quality of the output of SQL scripts. In my experience this is not used frequently, probably because of the the need of (more...)

Convert CSV file to Apache Parquet… with Drill

Read this article on my new blog A very common use case when working with Hadoop is to store and query simple files (CSV, TSV, ...); then to get better performance and efficient storage convert these files into more efficient format, for example Apache Parquet. Apache Parquet is a columnar storage format available to any project in the Hadoop ecosystem. Apache Parquet has the following

SQL Plan Management Choices

My thoughts on SQL plan management decision points:


SQL Patches are also available and not covered in the above flowchart.

Rounding Amounts, the missing cent: with the MODEL clause

Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.

To create an example, first let's create a table with only three records in it.

SQL> create table t
2 as
3 select rownum + 42 id
4 from dual
5 connect (more...)

Rounding Amounts, the missing cent

Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences.
Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes).
Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99)
To solve this cent-problem, we decide that the (more...)

Oracle Database 12c: Statistics created as the data was loaded

As you know, Oracle Magazine July/Aug 2015 issue released. You can free subscribe to Oracle Magazine digital edition and you can read here or you can read technical  articles here.

I subscribed Oracle Magazine, in current issue have very interesting articles.

One of interesting article of Oracle Expert Tomas Kyte (as known AskTom) with title: On Learning from Mistakes

In this article Tom talks about Cost Based Optimizer and shown how optimizer learning from mistakes. (more...)

Row pattern matching nested within hierarchy

I've been playing around with MATCH_RECOGNIZE - the data pattern matching extension to SELECT that was introduced in version 12.

Most examples I've seen have used the default AFTER MATCH SKIP PAST LAST ROW as most often the logic dictates, that when we have found a match in a group of rows, we want to search for further matches after those rows to avoid unwanted "double" matches.

But can there be uses where we want (more...)

Apache Drill : How to Create a New Function?

Read this article on my new blog Apache Drill allows users to explore any type of data using ANSI SQL. This is great, but Drill goes even further than that and allows you to create custom functions to extend the query engine. These custom functions have all the performance of any of the Drill primitive operations, but allowing that performance makes writing these functions a little trickier

Object Type with Optional Attribute: Extra Constructor Function

When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types.
The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure.
If you are unfamiliair with this technique, there are some links at the (more...)