KScope 2016 Acceptances

I've never been to KScope. Yes never.

I've always wanted to. Each year you hear of all of these stories about how much people really enjoy KScope and how much they learn.

So back in October I decided to submit 5 presentations to KScope. 4 of these presentations are solo presentations and 1 joint presentation.

This week I have received the happy news that 2 of my solo presentations have been accepted, plus my joint (more...)

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

Generierung von Bitlisten mit Hilfe von SQL am Beispiel einer Datum zu Monat Konvertierung

Ich hatte vor kurzem die Aufgabe erhalten eine Bitliste auf Basis eines Monats zu generieren.
Bedeutet, ich habe einen String mit 31 Zeichen der je Zeichen den Zustand 1 oder 0 einnehmen kann.
 - 1 steht für aktiv
 - 0 steht für inaktiv

Beispieldaten:
'0000000001010001000000000000000'
'0000000000000000111111000011010'

Um dies anhand eines verständlichen Beispiels zu verifizieren, habe ich mir eine Dienstplan-Tabelle ausgedacht.
Mehr »

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

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

Write Less with More – Part 8 (PL/SQL in the WITH Clause)

This post is part 8 of the Write Less with More series of blog posts, focusing on new features of Oracle 12c that allow us developers to write less than in previous versions in order to achieve the same functionality. Each part is dedicated for one new feature that solves some common development task. For more details, including the setup of the examples used throughout the series and the list of development tasks that drive (more...)

Write Less with More – Part 7 (Lateral Inline Views)

This post is part 7 of the Write Less with More series of blog posts, focusing on new features of Oracle 12c that allow us developers to write less than in previous versions in order to achieve the same functionality. Each part is dedicated for one new feature that solves some common development task. For more details, including the setup of the examples used throughout the series and the list of development tasks that drive (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 41: Why has my SQL execution plan changed?—A checklist

If the inputs to the CBO change, the plan can change. For example, changes to bind variables can cause the plan to change when it is hard parsed. Therefore one should never be surprised when plans change.(read more)

SQL: Calculate the past time between two dates in percent

Just a simple example how easy APEX can handle this kind of problem.

Example:
We have today the 06.11.2015 (11-06-2015) and we have two date values 01.11.2015 and 30.11.2015.
Now I want to know how much time has past in percent since the beginning (01.11.2015):
Result: 17 %
Mehr »

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

Write Less with More – Part 6 (SELECT FROM Package-Level Collection Types)

This post is part 6 of the Write Less with More series of blog posts, focusing on new features of Oracle 12c that allow us developers to write less than in previous versions in order to achieve the same functionality. Each part is dedicated for one new feature that solves some common development task. For more details, including the setup of the examples used throughout the series and the list of development tasks that drive (more...)

Write Less with More – Part 5 (Row Limiting)

This post is part 5 of the Write Less with More series of blog posts, focusing on new features of Oracle 12c that allow us developers to write less than in previous versions in order to achieve the same functionality. Each part is dedicated for one new feature that solves some common development task. For more details, including the setup of the examples used throughout the series and the list of development tasks that drive (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.

(more...)

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

REGEXP_LIKE Behavior

Often, the biggest problem with regular expressions is that those who use them sometimes don’t use them correctly. A great example occurs in the Oracle Database with the REGEXP_LIKE function. For example, some developer use the following to validate whether a string is a number but it only validates whether the first character is a number.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
  lv_input  VARCHAR2(100 (more...)

Create MySQL Index

Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT, UPDATE, REPLACE INTO, and DELETE statements. Indexes are also called fast access paths.

In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve (more...)

SQL*Plus Tricks

Have you ever wondered how to leverage substitution variables in anonymous block programs? There are several tricks that you can use beyond passing numeric and string values to local variable. The generic default appears to take a number unless you cast it as a string but that’s not really the whole story. The first two are standard examples of how to use numeric and string substitution values.

The following accept a numeric substitution value:

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 40: Why is it so hard to get SQL performance right the first time?

In the August 2015 issue of the NoCOUG Journal, we asked Stéphane Faroult why it is so hard to get SQL performance right the first time. His answer implies that SQL itself and the way it is taught are the problems.(read more)

Using CALIBRATE_IO

Using Oracle’s Resource Manager requires you to understand the IO dynamics. The first step requires you to run the CALIBRATE_IO procedure from the DBMS_RESOURCE_MANAGER package.

Oracle provides some great examples about how to use the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package in the Oracle Database Database PL/SQL Packages and Types Reference. The CALIBRATE_IO procedure returns the best answer when you provide a valid number of files, which you can capture by querying the V$ASM_DISK view.

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

Autonomous transaction to the rescue