Merging Overlapping Date Ranges

A recent question on the OTN forum asked about merging date ranges. Thanks to Karthick_App I realized that my previous blog on this subject was incomplete. Here is a solution that will merge any date ranges that meet, or that "overlap" in any way.

SQLDeveloper and Userdefined datatypes in tables

You might have tables that contain columns with a userdefined datatypes. For instance from 11g onwards SOASuite contain Integration B2B, with that datamodel

B2B works with advanced queueing with the queue-table ip_qtab based on the IP_MESSAGE_TYPE Oracle Type wich is defined like:
create or replace type IP_MESSAGE_TYPE as OBJECT (
In the queuetable you (more...)

The Fundamental theorem of arithmetic – SQL version

Every positive integer (except the number 1) can be represented in exactly one way apart from rearrangement as a product of one or more primes, see for example Wolfram MathWorld or Wikipedia.

Here is the SQL-Version, we compute this for all integers up to 100

with bound as
select 100 as bound from dual
n_until_bound as (
select level+1 n
from dual
connect by level <= (select bound.bound from bound)
primes_under_bound  (more...)

A Greedy Algorithm using Recursive subquery factoring

Today is friday and I like the twitter-hashtag #FibonacciFriday,
so I tweeted


Don’t be afraid of having a look at the wikipedia-site, the math is not complicated at all ( you don’t need more than adding natural numbers smaller than hundred ), nevertheless the theorem is nice from a (more...)

Fourth International NoCOUG SQL Challenge

The Fourth International NoCOUG SQL Challenge has been published in the May issue of the NoCOUG Journal available at Shakespeare’s story of Romeo and Juliet plays out in an American setting. In the NoCOUG version, Romeo sings American songs to Juliet and gives her gourmet dipped swizzled strawberries for her birthday. The bard of Avon must be rolling in his grave.

Entries should be attached as a comment to this announcement. (more...)

LoggerUtil: Create a Custom Template

Since I have written about my pet project about a month ago, I have made some major changes to the functionality of it.
If you haven't read that blog about my pet project, here's the synopsis:

I love Logger to instrument my code, I just don't like to type in all the bits and pieces to register all the input arguments when I write a new procedure or function. To solve this problem I have (more...)

Strategies for Minimising SQL Execution Plan Instability

Execution Plan Instability – What is the problem?

The Oracle Optimizer is a complex piece of software and with every release it becomes more complex.


In the beginning, the Optimizer was rule-based.

The Optimizer had a ranked list of heuristics used to optimize a query, picking the lowest ranked rule available.

This rule-based mode, whilst still in use with some internal Oracle dictionary queries, has been unsupported since version 10.1.

This means that (more...)

Viewing Models Details for Decision Trees using SQL

When you are working with and developing Decision Trees by far the easiest way to visualise these is by using the Oracle Data Miner (ODMr) tool that is part of SQL Developer.
Developing your Decision Tree models using the ODMr allows you to explore the decision tree produced, to drill in on each of the nodes of the tree and to see all the statistics etc that relate to each node and branch of the (more...)

Little quiz: Ordering/Grouping – Quess the output

How many times have you guessed the right answer? :)

select * from dual order by -1;
select * from dual order by 0;


select *                   from dual                                     order by -(0.1+0/1) desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;


select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0;
select 1 n,0 n,2  (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 37: POISED: A problem-solving method

P.O.I.S.E.D. is the acronym I invented for the performance tuning method described by Chris Lawson in The Art and Science of Oracle Performance Tuning; it stands for Problem, Observation, Interpretation, Solution, Execution, and Documentation. The Observation and Interpretation phases feed each other. The Interpretation phase concludes with a determination of root cause. If we are investigating poor performance a single SQL statement, the tool of choice for the observation phase (more...)

Overview of all time changes this year via SQL

inspired by Laurent Schneider’s remark concerning special time changes in Lord Howe Island

with dates as
trunc(sysdate, 'year') + level - 1 as day
connect by
extract(year from trunc(sysdate, 'year') + level - 1) = extract(year from trunc(sysdate, 'year'))

timezones as
vtn.TZNAME, listagg(vtn.TZABBREV, ', ') within group(order by vtn.tzabbrev) tzabbrevs
v$timezone_names vtn
group by

offsets as
v.*, d. (more...)

What’s this ‘WHERE 1=1′?

Since some time I have been adding WHERE 1=1 to all my queries.
I get queries like this:

  FROM emp e
 WHERE 1=1
   AND e.ename LIKE 'A%'
   AND e.deptno = 20

Lots of people ask me what’s the use of this WHERE 1=1.

You know I like to type as little as possible but here I am typing a lot of extra characters. And yet, it makes my development life a (more...)

Join Tables on Date Ranges

A recent question on the OTN SQL forum asked how best to join two tables related by ID and date range, in order to insert one row per date into a data warehouse. One solution was to expand the data from each table, creating one row per date, then join on date. I think it's more efficient to join on date range, then expand.

Visualizing Statspack Performance Data in SQL Developer

If you run Oracle Standard Edition or haven’t licenced Diagnostics Pack for Enterprise Edition, then you don’t have AWR and ASH Data available. This is when Statspack, the predecessor of AWR, comes in handy to keep a history of database performance metrics. But although Oracle still deliver Statspack with their recent DB releases (yes, even in […]


In this post I will try to show you how I used the Oracle Apex and the APEX_WEB_SERVICE  PL/SQL package to quickly send a request to a public Internet API and how I handled the response. The code below was written during a 'Hackday' and hasn't been extensively tested.

My use case is integrating Oracle Apex with the public Mendeley REST API for Mendeley Catalog Search.

The idea was to build an (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 36: The sisters of the mother of all SQL antipatterns

The mother of all SQL “anti-patterns” is the ad-hoc query. Here, the end-user of the application can specify any combination of parameters—for example, the job_id, the dept_id, or a combination of both—and the data is retrieved using a catchall query. This antipattern has two sisters who are almost as evil. These two sisters arise from the requirement to paginate through the records retrieved by a query.(read more)

Comparing NULLable Values

If a column or expression may be NULL, it is a pain to compare it to something else. Why? Because comparisons involving a NULL do not result in TRUE or FALSE: they result in NULL. I use the DECODE function to work around this problem: here's why - and how.

Real time sql monitoring – bulk bound statement

Moving through some observations of an ongoing investigation tackling the easiest ones first.

Previously I observed the slow execution of a recursive delete of statistics history but coming not from MMON but from a number of application processes which should have only been calculating stats for their own partitions.

Statement was sql id 9v9n97qj8z1dg:

delete /*+ dynamic_sampling(4) */
from sys.wri$_optstat_histhead_history
where savtime < :1
and rownum <= NVL(:2, rownum);

From the real time sql (more...)


Yesterday I mentioned issues with a recursive delete operation on statistics history.

This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL.

COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached.

For example:

create table t1 as select * from dba_objects;

alter session set statistics_level = all;


10 Cool things about the COMPARE_SYNC package

@thatjeffsmith recently recommended an article about making your blog more popular. The article said "lists of 10 things" were great ways to get more readers. Hey, if that's all it takes... STEW_COMPARE_SYNC is a package that generates SQL to compare data or synchronize tables. Here are 10 good reasons to use it.