Did You Know #30 – Order in a Predicate

I’ve known this for a while but never wrote about it. In KScope19 I attended a session by Alex Nuijten and during the session he asked a question about LIKE predicate that reminded me this, so here it is. Equality It’s quite obvious that if you have a column (let’s say ID) and you want … Continue reading "Did You Know #30 – Order in a Predicate"

Video : AVG, MEDIAN, MIN and MAX : Problem Solving using Analytic Functions

In today’s video we take a quick look at the AVG, MEDIAN, MIN and MAX aggregate and analytic functions.

You can get more information on these and more analytic functions here.

The star of today’s video is Sten Vesterli, in one of the rare moments at OpenWorld when he’s not in a wetsuit. 🙂




In the previous post we saw that the Oracle Optimizer has a special operation – SORT GROUP BY ROLLUP – for performing several aggregations of the same population in a single scan, as long as the grouping keys are in a “rollup form”; for example:


If there is an index that its leading part is the same as the rollup grouping key, and that at least one of its columns is defined (more...)

Not all GROUPING SETS are created equal


I’ve just realized, once again, that the Oracle Optimizer is even smarter than I thought.
The comments (by Iudith Mentzel and Thomas Mautsch) to my previous post, GROUPING SETS and COLLECT don’t get along, made me understand that not all GROUPING SETS were created equal.

The examples in this post are from Oracle 18.3

Extended Aggregation Options

GROUPING SETS, ROLLUP and CUBE are great features that enable us to perform several aggregations (more...)

Video : FIRST_VALUE and LAST_VALUE : Problem Solving using Analytic Functions

Today’s video is a quick run through the FIRST_VALUE and LAST_VALUE analytic functions.

You can find more information about these and other analytic functions in the following articles.

The star of today’s video is Lonneke Dikmans. She probably won’t believe me, but I probably quote/paraphrase her advice more often than any other person in the Oracle space.



Video : (more...)


I’ve recently got reminded that assuming something will work, just because it makes sense, doesn’t mean it will really work.

While reviewing some code a few days ago, I saw a query of the following form:

select 'X='||x, collect(z)
from t
group by x
union all
select 'Y='||y, collect(z)
from t
group by y;

I immediately recommended to convert it to use GROUPING SETS; like this:

select decode(grouping(x), 0, 'X='||x, 'Y='||y),
from t

EXPAND_SQL_TEXT – Much More Than Just Expanding Views


There are features in Oracle SQL that are implemented by other, older, features. This is a clever way for supporting a new syntax with low efforts and low risk – the Oracle Corp engineers only need to convert the SQL statement with the new syntax to an equivalent statement that uses the old syntax they already support. And Oracle has a perfect place for doing this conversion – the expansion stage in the parsing (more...)

Video : LAG and LEAD : Problem Solving using Analytic Functions

Today’s video gives a quick demo of the LAG and LEAD analytic functions.

There is more information about these and other analytic functions in the following articles.

The star of today’s video is Gwen (Chen) Shapira of Kafka fame!



Video : LAG and LEAD : Problem Solving using Analytic Functions was first posted on June 24, 2019 at 8:12 (more...)

Elegant MODEL clause

Frank Kulash recently asked a challenging question on OTN, which Chris227 answered using the MODEL clause. To help  fully understand his excellent answer, I'm going to go into detail.

Missing cents when dividing an amount

Alex Nuijten (@alexnuijten) wrote a blog post on this subject a few years ago. Since his blog doesn't accept comments at the moment, here is a variant I think is interesting.

Online Redo Log Switch Frequency Map

A query I find myself often running is the online redo log switch frequency map query, which queries the v$log_history/gv$log_history (for cluster databases) view and show the historical log switch frequency.

Why you might ask? Well it’s important to see how frequent log switches are occurring as Oracle’s rule of thumb is to not switch more then 3 logs per hour (20 minutes of redo) at peak DML activity to prevent excessive checkpoints.  The (more...)

With and without WITH_PLSQL within a WITH SQL statement

OK, let’s be honest right up front. The motivation for this post is solely to be able to roll out a tongue twisting blog post title Smile. But hopefully there’s some value as well in here for you if you’re hitting the error:

ORA-32034: unsupported use of WITH clause

First some background. A cool little enhancement to the WITH clause came along in 12c that allowed PLSQL functions to be defined within the scope of the (more...)

Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions

You may know that table() (kokbf$ collection functions), xmltable and json_table functions block Join-Predicate PushDown(JPPD).

Simple example:


create table xtest(a, b, c) as
select mod(level,1000),level,rpad('x',100,'x')
from dual
connect by level<=1e4
create index itest on xtest(a)
create or replace view vtest as
select a,count(b) cnt
from xtest
group by a
call dbms_stats.gather_table_stats(user,'xtest');


select distinct v.* 
from table(sys.odcinumberlist(1,2,3)) c, vtest v
where v.a = c.column_value;

Plan  (more...)


Today I wanted to give a link to the description of v$sql_hint.target_level to show that no_parallel can be specified for statement or object, and though it’s pretty obvious, but surprisingly I haven’t found any articles or posts about it, so this short post describes it.
v$sql_hint.target_level is a bitset, where
1st bit set to 1 means that the hint can be specified on statement level,
2nd – on query block level,
3rd (more...)

HiveMall: Transform Categorical features to Numerical

HiveMall is a machine learning library that sits on top of Hive and provides SQL interface to wide range of data preparation and machine learning algorithms.

A common task faced for many machine learning exercises is to convert the data from the format it is captured in (raw data) into a format that is required by the machine learning algorithms. Most ML tools will either have functionality built into the algorithms to do this automatically (more...)

Top time-consuming predicates from ASH

Sometimes it might be useful to analyze top time-consuming filter and access predicates from ASH, especially in cases when db load is spread evenly enough by different queries and top segments doesn’t show anything special, except usual things like “some tables are requested more often than others”.
Of course, we can start from analysis of SYS.COL_USAGE$: col_usage.sql

col owner format a30
col oname format a30 heading "Object name"
col cname format  (more...)

Visualising SQL Analytics Rolling Count with OracleJET in APEX

Back in around 2005, before the time of smart phones, I had some data.

I can't remember what the data was, but I was told that for it to be valid, it should roughly form a bell curve.

Sure, I'm sure I could have aggregated it, exported it to Excel, and plotted it to a graph, but this was 2005. There was no SQL Developer where I could copy & paste the results directly into (more...)

My #OUGN19 Presentations

As promised, here are the two presentations I gave on the OUGN 2019 Boat conference. I had a great time with old friends and new, and managed to have discussions about politics without anyone getting upset! Hope to see some of you again at OUG Ireland!

Function based tables

You've probably seen this somewhere already, no doubt from Connor, though I couldn't find much beyond Tim's post on pipelined functions - I can't find the right keywords to find related content.

I like table functions, so this will help me remember we no longer need to specify the table() operator in 18c (12.2).


select * 
from table(

Result Sequence

12c> select * from apex_string.split('A,B,C',',');


Mystery and imagination of DELETE RETURNING INTO

| Feb 7, 2019
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. Am I the only one who finds very odd this statement in the documentation of DELETE ... RETURNING INTO? "returning_clause This clause lets you return values from deleted columns, and thereby eliminate the need to issue a SELECT statement following the DELETE statement." I mean,