Postgres & Sequences

Many developers already know about the change from int to serial in Postgres. Serial is simpler because it automatically makes the column not null and links it to a sequence. The sequence is created with the following naming convention:

table_name_column_name_seq

The Postgres serial type always has a start with value of 1. Though, you can alter it immediately after creating the table.

You can create a serial surrogate key column like this:

CREATE TABLE new_hire
 (more...)

Extending in-lists

blah blah great for static values, but what about wild cards


SQL> create table t ( x varchar2(50));

Table created.

SQL> insert into t values ('stringasd');

1 row created.

SQL> insert into t values ('blah');

1 row created.

SQL> insert into t values ('more data');

1 row created.

SQL> insert into t values ('blah blah');

1 row created.

SQL> insert into t values ('some stuff with qwe in it');

1 row created.

SQL> insert into  (more...)

Making Longer Lists

For very long lists, we need the return values to be CLOBs. Alas, LISTAGG can only return VARCHAR2 lists, but XML and JSON aggregate functions can return CLOBs!

Interval expressions

I just learned a lot about these critters thanks to an ODC forum question: how to calculate the difference between two dates in calendar years and months, days, hours, minutes and seconds.

What version is my RAD stack?

A common follow up clarification on forums is regarding the version of the relevant tool.
Questions relating to Oracle APEX could be impacted by the APEX version, the database version, and perhaps the ORDS version - in addition to what browser is being used.

The information on the RAD stack can be resolved in one (concatenated) SQL query.

APEX was easy, there is a simple one row view, which ultimately translates to a function returning (more...)

Recursive Subquery

At my current assignment we are processing files coming from an online system to be inserted into our database (kind of a data warehouse). This is done using external tables and a scheduled job. The job just checks if there is a file available and will process this. The trouble is that the files might not make it to our database, for various reasons.

I want to be able to check if all the files (more...)

SORT GROUP BY NOSORT ROLLUP

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:

(a)
(a,b,c)
(a,b,c,d)

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

Introduction

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

GROUPING SETS and COLLECT Don’t Get Along

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),
       collect(z)
from t
 (more...)

EXPAND_SQL_TEXT – Much More Than Just Expanding Views

Overview

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

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

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:

DDL

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');
/

[collapse]

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

Plan  (more...)

v$sql_hint.target_level

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

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

12c

select * 
from table(
apex_string.split('A,B,C',',')
);

Result Sequence
---------------
A
B
C

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

(more...)

Mystery and imagination of DELETE RETURNING INTO

Uncategorized
| 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,