Oracle Database Release 12c (12.1) introduced a new operator that we can use in SQL queries. The MATCH_RECOGNIZE operator allows us to detect patterns in our relational data. Specifically: it allows us to identify records that mark the beginning of a set of records that together form a pattern. In this set, each record satisifies a certain condition. The fact that in a set of records these conditions are met – in a certain (more...)
Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?
Erik van Roon
Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)
One of those things SQL developers are frequently looking at is the generation of rows: having a query return records that do not really exist. For example to generate test data or to produce records for all days in a month. Tom Kyte usually selects from data dictionary views. Various tricks make the rounds, for example based on CONNECT BY or CUBE or UNPIVOT. This blog article by Natalka Roshak (2015) compares various row generation (more...)
What is an ANTI-JOIN? And what is the difference between the SEMI-JOIN and the ANTI-JOIN?
First of all, both SEMI-JOIN and ANTI-JOIN are not in the SQL syntax but they are more a pattern. You might expect to be able to write something like:
[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc FROM dept d SEMI JOIN emp e ON (e.deptno = d.deptno) /
to get all the (more...)
email@example.com > create table t( pk int primary key check(pk > 0)); Table created. firstname.lastname@example.org > insert /*+ignore_row_on_dupkey_index(t(pk)) */ into t email@example.com > select trunc(dbms_random.value(1, 1e5)) from dual firstname.lastname@example.org > connect by level <= 1e5 email@example.com > / 63187 rows created.
Finding the first n gaps
firstname.lastname@example.org > variable n number email@example.com > exec :n := 1000 PL/SQL procedure successfully completed. firstname.lastname@example.org > set autotr (more...)
Could you tell me what the difference is between NVL and COALESCE?
NVL returns the value of the first argument if it doesn’t evaluate to NULL, otherwise it will return the value of the second argument.
COALESCE returns the first argument that doesn’t evaluate to NULL. That can be any one of the arguments.
So they are definitely similar – but there are significant differences.
First of (more...)
I have got a website with a search form. I want to display a limited number of results to the user and have him/her navigate through different pages. Is this possible using plain SQL?
Of course this is possible. It might take some thinking, but that has never hurt anyone (yet). First we need a table with some randomly sorted data in it. In this example I (more...)
Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.
To try things out, let's just start with a simple table with (more...)
SQL> conn andrew/reid
SQL> create table tab1 as
2 select table_name from dba_tables
Because my hero is Cary Millsap, I'm going to do what he did and publish my
Brendan Tierney and (more...)
ora:tokenizeto split a comma delimited string. Now I'll apply that technique to multiple rows, and show that it's faster than other methods.
column1 = column2 or (column1 is null and column2 is null)
An introduction to Oracle Database for beginners: What is a relational database management system?—Part II
An introduction to Oracle Database for beginners: What is a relational database management system?—Part IV
An introduction to Oracle Database for beginners: What is a relational database management system?—Part V
An introduction to Oracle Database for beginners: What is a relational database management system?—Part I
Some time ago I was asked to assist in fixing or at least finding the cause of a performance problem. The application ran fine until the Virtual Private Database (VPD) policy was applied. Oracle claims there should be near zero impact on your application when you implement VPD, then how is this possible?
First of all, the policy applied was a rather complex one. A complex query should be executed to determine if the current (more...)
Somethings it can be a challenge to know what is or isn't a reserved word. Yes we can check the Oracle documentation for the SQL reserved words and the PL/SQL reserved words. There are other references and (more...)