SQL Merge and Sequence gaps

I hope You my dear reader isn't one of the people cursed by requirement for gapless sequences ;)
Yesterday I just found another nail in the coffin for oracle sequence objects used as gapless sequence generators. Of course there are many causes not to do that, just like simple rollback, users changing their minds, network connection error, flushing cache etc. But I didn't know at least till yesterday, that MERGE statements generate gaps with quite big guarantee. It seems that Merge statement is generating the same number of calls for sequence next value as row count of source table or subquery.
Let's look at example:

CREATE TABLE target (pk number, data varchar2(10));
CREATE TABLE source (pk_s number, data_s varchar2(10));
INSERT INTO target VALUES (1, 'a');
INSERT INTO target VALUES (3, 'c');
INSERT INTO source VALUES (1, 'a');
INSERT INTO source VALUES (2, 'b');
INSERT INTO source VALUES (3, 'c');
INSERT INTO source VALUES (4, 'd');
COMMIT;
CREATE SEQUENCE seq START WITH 5;

So now we have 2 rows in target table, and 4 rows in source table. We'll compare them using data and data_s column. We'd insert only two rows, because another 2 already exists and even won't use WHEN MATCHED clause for update.

MERGE INTO target
USING source
ON (data = data_s)
WHEN NOT MATCHED THEN
INSERT VALUES (seq.nextval, data_s);
SQL> SELECT * FROM target;
PK DATA
---------- ----------
1 a
3 c
7 d
8 b

Although the sequence should have started from 5 new pk values are 7 and 8. Let's add another row into source and look what happens:

INSERT INTO source VALUES (5, 'e');
MERGE INTO target
USING source
ON (data = data_s)
WHEN NOT MATCHED THEN
INSERT VALUES (seq.nextval, data_s);
SQL> SELECT * FROM target;
PK DATA
---------- ----------
1 a
3 c
7 d
8 b
13 e

So now the value is 13 i.e. 8 + count of rows from source table (5). Now let's add another row and delete all previous.

INSERT INTO source VALUES (6, 'f');
DELETE source WHERE pk_s <=5;
MERGE INTO target
USING source
ON (data = data_s)
WHEN NOT MATCHED THEN
INSERT VALUES (seq.nextval, data_s);
SQL> SELECT * FROM target;
PK DATA
---------- ----------
1 a
3 c
7 d
8 b
13 e
14 f

As we can see pk is incremented only by 1.
After my experiments I found also metalink note "Merge Increments SEQUENCE.NEXTVAL for Both Insert and Update" (Note:554656.1), which also assures this as expected situation and not bug. So another reason not to worry about sequence values, just uniqueness of them ;)

Minus All and Intersect All


Today I rediscovered Oracle Mix ideas and remembered my surprise that there are also Minus all and Intersect all operators in SQL standard just like Union and Union all. I'll briefly remind You what exactly they were. Let's imagine we have two sets:

T1: 1, 2, 2, 2, 3, 4, 4
T2: 2, 3, 4, 4, 4, 5


Then T1 INTERSECT T2 is 2, 3, 4
T1 INTERSECT ALL T2 would be 2, 3, 4, 4


T1 MINUS T2 is 1
T1 MINUS ALL T2 would be 1, 2, 2


So by default Intersect and Minus have DISTINCT operator removing all non-unique values. But in case we add keyword ALL then all values and their cardinalities are counted as well. I think these operators could be quite handy in process of analysing two data sets and finding what exactly the difference is. So if You are thinking the same way then I kindly ask you to support my idea about intersect all and minus all with your voice in Oracle Mix.

Thank You in advance for any decision :) and I hope sooner or later (better sooner of course ;) to find that in New features guide.

DBMSes are different...

Recently had to do some work with SQL Server. As my experience with it is quite limited I've got a few interesting impressions, which probably are absolutely trivial for SQL Server adepts :)

Do you know that SELECT query can create new tables?

OK in SQL Server it can. So I had to create backup of a table and firstly was quite frustrated not finding anything like CTAS (CREATE TABLE ... AS SELECT). I was on a wrong track however. The right syntax is SELECT INTO as in following example:

CREATE TABLE persons (
prs_id INT IDENTITY NOT NULL PRIMARY KEY,
prs_name VARCHAR(30) NOT NULL) ;

INSERT INTO persons (prs_name) VALUES ('GINTS');
INSERT INTO persons (prs_name) VALUES ('JOE');

SELECT *
INTO persons_bkp
FROM persons;

SELECT * FROM persons_bkp;
-------------------------
1 GINTS
2 JOE


So till now I was under the impression that SELECT is DML statement however obviously that's not true at least for SQL Server.

Do you know that SQL Server can handle recursive queries?

At least since version 2005 it can. Long ago I have heard that SQL Server has nothing similar to START WITH CONNECT BY. This site comparing SQL Server and Oracle seemed to approve my thoughts. However DBMSes are different and one has to look for different ways accomplishing the same goals. And in SQL Server one can use Common table expressions (aka query factoring clause or with clause in Oracle world) to get hierarchical results. And BTW here SQL Server "outfunctions" Oracle because it allows to recursively reference the same common table in its definition, which is not allowed by Oracle. Here are two articles if you are interested in how exactly it is done:

• Recursive Queries Using Common Table Expressions
• Recursive Queries in SQL Server 2005

So in conclusion these two features which are possible in both DBMSes, but are implemented in completely different ways supports my previous article that all databases are different :)