Ranges with NULLs 01: starting over

I have written a lot about ranges, mostly based on dates, and I have tried my best to avoid NULLs in “from” and “to” columns. I give up: NULLs are a fact of life and must be dealt with. This means revisiting all my previous work!

Current list of posts: 

What do I mean by “range”?

Let’s start with the SQL:2011 definition of PERIOD. A PERIOD identifies two datetime columns. The end datetime is greater than the start datetime. In the Oracle implementation, both start and end can be null.

A datetime value falls within the period if:

  • it is greater than or equal to the start datetime
    OR the start datetime is NULL
  • and it is less than the end datetime
    OR the end datetime is NULL.

A period that starts with NULL and ends with NULL will include any datetime value, including NULL!

My notion of “range” is basically an extension of this definition to more data types:

  • A range identifies a pair of values of the same data type.
  • The data type can be any scalar capable of equal, greater-than and less-than comparisons,
    though datetime types and numbers are by far the most common.
  • I tend to use “from” and “to” because “start” and “end” make me think of datetime ranges.
  • “From” is less than “to”
  • “From” and / or “to” can be NULL
  • A value falls within the range if:
    • it is greater than or equal to “from”
      OR “from” is NULL
    • and it is less than “to”
      OR “to” is NULL
  • In table definitions, I strongly recommend constraints
    • “from” < "to". This constraint should always be there.
    • “from” NOT NULL (unless a use case necessitates NULL).

Closed-Open ranges

When defining ranges, we always want to allow the possibility for two ranges to “meet”, meaning there is no gap between them yet they do not overlap.

The “closed-open” model allows this, because the “from” value belongs to the range but the “to” value does not.

If we have a range 1 to 2 and another range from 2 to 3:

  • The value 2 is not in the first range, but it is in the second.
  • There is no value from 1 to 3 that is in both ranges, so there is no overlap.
  • There is no value from 1 to 3 that is in neither range, so there is no gap.
  • In other words, every value from 1 to 3 is in exactly one of the two ranges.

This model will work will all types of range, and it makes some queries easier to write. It’s all good.

Objections to NULL in ranges

I once wrote SQL and date ranges: don’t make NULL mean something, where I gave five arguments against NULL. Here’s what I think as of today.

  1. “NULL should not mean anything”.
    I still agree with that, but what can you do?
  2. “We can use real values instead of NULL”.
    That may be the case for “from”, but not for “to” because there is no valid “to” value that is strictly greater than every valid “to” value!
  3. “Oracle doesn’t index NULLs”.
    Oracle does not index a row if all of the columns to be indexed are NULL. When this is a problem, it can be worked around.
  4. “Queries on NULLable ranges are hard”.
    We’ll see if I am up to the task…
  5. “Unique constraints don’t work with NULL”.
    Again, when this is a problem there are workarounds.

Next up: test data! If the queries are hard, I must try to test them thoroughly.

 

Advertisements

Optimistic Locking 9: proof of concept

To wind up this series about optimistic locking using the SCN, here is a sample API for querying and updating the DEPT table, using JSON as the format for exchanging data with the client application.

I want to make optimistic locking as unobtrusive as possible, even if it means more code for me:

  • the DEPT table does not have to be created with ROWDEPENDENCIES. The ORA_ROWSCN pseudocolumn reflects the latest change to the data block, not to one specific row.
  • the client application receives just one SCN when it queries data. Its only responsibility is to return the same SCN when it wants to update the data.
  • Being “optimistic”, I assume that most of the time the data block will not have changed, so the update will succeed directly.
  • If and only if the data block has changed, I check to see if the actual column values have changed.

The API

(Sorry about the lack of line numbers, there is a problem with the [ code ] tag at one point in the package body.)

create or replace package dept_json as
  function get_depts return varchar2;
  procedure update_depts(p_json in varchar2);
end dept_json;

The GET_DEPTS function returns one VARCHAR2 containing all the departments in JSON format, together with the SCN:

{
  "data_version_num" : 56781167,
  "depts" :
  [
    {
      "deptno" : 10,
      "dname" : "ACCOUNTING",
      "loc" : "NEW YORK"
    },
    {
      "deptno" : 20,
      "dname" : "RESEARCH",
      "loc" : "DALLAS"
    },
    {
      "deptno" : 30,
      "dname" : "SALES",
      "loc" : "CHICAGO"
    },
    {
      "deptno" : 40,
      "dname" : "OPERATIONS",
      "loc" : "BOSTON"
    }
  ]
}

In this simplistic proof of concept, the UPDATE_DEPTS API sends back JSON in exactly the same format with the same “data_version_num” and the changed data:

{
  "data_version_num" : 56781167,
  "depts" :
  [
    {
      "deptno" : 10,
      "dname" : "ACCOUNTING",
      "loc" : "NEW LOC"
    }
  ]
}

Getting the data

create or replace package body dept_json as
  ...
  function get_depts return varchar2 is
    l_json varchar2(4000 byte);
    l_scn number;
  begin
    l_scn := dbms_flashback.get_system_change_number;
    select json_object(
      'data_version_num' value l_scn,
      'depts' value json_arrayagg(
        json_object(
          'deptno' value deptno,
          'dname' value dname,
          'loc' value loc  
        ) order by deptno
      )
    )
    into l_json
    from dept;
    return l_json;
  end get_depts;
  ...
end dept_json;

Using the 12c SQL/JSON functions, I can query the data and convert it to JSON format immediately. I get an SCN just before querying the data. In a busy database, the actual “read SCN” may be slightly higher. See Optimistic Locking 5: “Read-consistent” SCN for a discussion.

Updating the data

create or replace package body dept_json as

  type tt_dept is table of dept%rowtype;  
  cursor c_request is select 0 read_scn, d.* from dept d;
  type tt_request is table of c_request%rowtype;
  ...
  procedure update_depts(p_json in varchar2) is
    lt_request tt_request;
    lt_try_again tt_request := new tt_request();
    c_char constant varchar2(1) := '*';
    lt_dept tt_dept := new tt_dept();
  begin
    select * bulk collect into lt_request
    from json_table(p_json, '$' columns
      read_scn number path '$.data_version_num',
      nested path '$.depts[*]' columns (
        deptno number path '$.deptno',
        dname varchar2(40) path '$.dname',
        loc varchar2(40) path '$.loc'
      )
    )
    order by deptno;

    forall i in 1..lt_request.count
      update dept set dname = lt_request(i).dname, loc = lt_request(i).loc
      where deptno = lt_request(i).deptno
      and coalesce(c_char, dname||loc) is not null
      and ora_rowscn <= lt_request(i).read_scn;
  ...
  end update_depts;
end dept_json;

This is the first part of the update procedure. I convert the JSON input to SQL format, then I do a conditional update: the update will succeed only if ORA_ROWSCN remains less that the SCN supplied by the GET_DEPTS function. The line in italics is necessary to ensure “restartability” of the update: see Optimistic Locking 7: Restartability for details.

Double-checking

After the first UPDATE, I gather the rows that were not updated into a new collection.

  procedure update_depts(p_json in varchar2) is
    lt_request tt_request;
    lt_try_again tt_request := new tt_request();
    c_char constant varchar2(1) := '*';
    lt_dept tt_dept := new tt_dept();
  begin
    -- I have removed the code for the first UPDATE
    for i in 1..lt_request.count loop
      if sql%bulk_rowcount(i) = 0 then
        lt_try_again.extend;
        lt_try_again(lt_try_again.last) := lt_request(i);
        dbms_output.put_line('deptno '||lt_request(i).deptno||': SCN too recent');
      else
        dbms_output.put_line('deptno '||lt_request(i).deptno||': first update succeeded');
      end if;
    end loop;

Now I use a flashback query to get the actual column values I sent to the client application in the first place.

    if lt_try_again.count > 0 then
      lt_dept := select_depts_flashback(lt_try_again);
  function select_depts_flashback(
    pt_request in tt_request
  ) return tt_dept is
    pragma autonomous_transaction;
    lt_dept tt_dept;
    lt_deptno sys.odcinumberlist := new sys.odcinumberlist();
  begin
    lt_deptno.extend(pt_request.count);
    for i in 1..pt_request.count loop
      lt_deptno(i) := pt_request(i).deptno;
    end loop;
    dbms_flashback.enable_at_system_change_number(pt_request(1).read_scn);
    select * bulk collect into lt_dept
    from dept where deptno in (select * from table(lt_deptno))
    order by deptno;
    dbms_flashback.disable;
    return lt_dept;
  end select_depts_flashback;

Finally, I do a second update that will only succeed if the actual column values have not changed. If there has been a change, I raise an exception. That will cause all of the updates to be rolled back automatically, thanks to Statement-Level Atomicity.

      forall i in 1..lt_try_again.count
        update dept set dname = lt_try_again(i).dname, loc = lt_try_again(i).loc
        where deptno = lt_try_again(i).deptno
        and decode(lt_dept(i).dname, dname, 0, 1)
          + decode(lt_dept(i).loc,   loc,   0, 1)
          = 0;

      for i in 1..lt_try_again.count loop
        if sql%bulk_rowcount(i) = 0 then raise_application_error(
          -20001,
          'DEPT row with DEPTNO = ' 
          || lt_dept(i).deptno 
          ||' already changed by another user. No updates have been made.'
          );
        else
          dbms_output.put_line('deptno '||lt_request(i).deptno||': second update succeeded');
        end if;
      end loop;
    end if;

  end update_depts;
end dept_json;

 

A few tests

I’m going to create a DEPT table with the data in three different blocks.

drop table dept cascade constraints purge;
CREATE TABLE DEPT(
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14),
	LOC VARCHAR2(13) 
);
INSERT INTO DEPT VALUES	(10,'ACCOUNTING','NEW YORK');
commit;
INSERT /*+ append */ INTO DEPT 
select 20,'RESEARCH','DALLAS' from dual
union all
select 30,'SALES','CHICAGO' from dual;
commit;
INSERT /*+ append_values */ INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
commit;

select d.*,
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) block_num,
ora_rowscn
from dept d;

    DEPTNO DNAME          LOC            BLOCK_NUM ORA_ROWSCN
---------- -------------- ------------- ---------- ----------
        10 ACCOUNTING     NEW YORK           80742   56026114
        20 RESEARCH       DALLAS             85848   56026119
        30 SALES          CHICAGO            85848   56026119
        40 OPERATIONS     BOSTON             85849   56026122

>> Test 1: update with no intervening update
SQL> begin
  2  dept_json.update_depts(
  3  '{"data_version_num":56781167,"depts":[{"deptno":10,"dname":"ACCOUNTING","loc":"Test 1"}]}'
  4  );
  5  end;
  6  /
deptno 10: first update succeeded

PL/SQL procedure successfully completed.

>> Test 2: update with intervening select for update
SQL> select * from dept where deptno = 20 for update;

    DEPTNO DNAME          LOC          
---------- -------------- -------------
        20 RESEARCH       DALLAS       

SQL> commit;

Commit complete.

SQL> begin
  2  dept_json.update_depts(
  3  '{"data_version_num":56781167,"depts":[
  4  {"deptno":20,"dname":"RESEARCH","loc":"Test 2"},
  5  {"deptno":30,"dname":"SALES","loc":"CHICAGO"}
  6  ]}'
  7  );
  8  end;
  9  /
deptno 20: SCN too recent
deptno 30: SCN too recent
deptno 20: second update succeeded
deptno 30: second update succeeded

PL/SQL procedure successfully completed.

>> Test 3: update refused, intervening update
SQL> update dept set loc = 'Test 3a' where deptno = 30;

1 row updated.

SQL> commit;

Commit complete.

SQL> begin
  2  dept_json.update_depts(
  3  '{"data_version_num":56781167,"depts":[
  4  {"deptno":20,"dname":"RESEARCH","loc":"Test 2"},
  5  {"deptno":30,"dname":"SALES","loc":"Test 3b"}
  6  ]}
  7  ');
  8  end;
  9  /
deptno 20: SCN too recent
deptno 30: SCN too recent
...
Error report -
ORA-20001: DEPT row with DEPTNO = 20 already changed by another user. No updates have been made.

>> Test 4: update refused, intervening update with restart
/* In another session
update dept set loc = 'Test 4a' where deptno = 40;
*/
SQL> begin
  2  dept_json.update_depts(
  3  '{"data_version_num":56781167,"depts":[
  4  {"deptno":40,"dname":"OPERATIONS","loc":"Test 4b"}
  5  ]}'
  6  );
  7  end;
  8  /
  
/* In another session
commit;
*/
deptno 40: SCN too recent
...
Error report -
ORA-20001: DEPT row with DEPTNO = 40 already changed by another user. No updates have been made.

Conclusion

To repeat a bit of what I said in Optimistic Locking 4: the Good :

If you base optimistic locking on row version numbers, row timestamps, or checksums of row columns, then the client code has to keep track of that data and send it back with the updates. With the SCN, the data from each row is just user data, not technical data. It can be pivoted, unpivoted, aggregated or denormalized. With JSON, it is trivial to separate the SCN from all the user data.

All the client code has to do is keep the SCN somewhere and include it in the call to the update API.

P.S. the package body

create or replace package body dept_json as

  type tt_dept is table of dept%rowtype;  
  cursor c_request is select 0 read_scn, d.* from dept d;
  type tt_request is table of c_request%rowtype;

  function get_depts return varchar2 is
    l_json varchar2(4000 byte);
    l_scn number;
  begin
    l_scn := dbms_flashback.get_system_change_number;
    select json_object(
      'data_version_num' value l_scn,
      'depts' value json_arrayagg(
        json_object(
          'deptno' value deptno,
          'dname' value dname,
          'loc' value loc  
        ) order by deptno
      )
    )
    into l_json
    from dept;
    return l_json;
  end get_depts;

  function select_depts_flashback(
    pt_request in tt_request
  ) return tt_dept is
    pragma autonomous_transaction;
    lt_dept tt_dept;
    lt_deptno sys.odcinumberlist := new sys.odcinumberlist();
  begin
    lt_deptno.extend(pt_request.count);
    for i in 1..pt_request.count loop
      lt_deptno(i) := pt_request(i).deptno;
    end loop;
    dbms_flashback.enable_at_system_change_number(pt_request(1).read_scn);
    select * bulk collect into lt_dept
    from dept where deptno in (select * from table(lt_deptno))
    order by deptno;
    dbms_flashback.disable;
    return lt_dept;
  end select_depts_flashback;

  procedure update_depts(p_json in varchar2) is
    lt_request tt_request;
    lt_try_again tt_request := new tt_request();
    c_char constant varchar2(1) := '*';
    lt_dept tt_dept := new tt_dept();
  begin
    select * bulk collect into lt_request
    from json_table(p_json, '$' columns
      read_scn number path '$.data_version_num',
      nested path '$.depts[*]' columns (
        deptno number path '$.deptno',
        dname varchar2(40) path '$.dname',
        loc varchar2(40) path '$.loc'
      )
    )
    order by deptno;
    
    if lt_request.count = 0 then
      dbms_output.put_line('No depts found, so nothing to update.');
      return;
    end if;

    forall i in 1..lt_request.count
      update dept set dname = lt_request(i).dname, loc = lt_request(i).loc
      where deptno = lt_request(i).deptno
      and coalesce(c_char, dname||loc) is not null
      and ora_rowscn  0 then
      lt_dept := select_depts_flashback(lt_try_again);

      forall i in 1..lt_try_again.count
        update dept set dname = lt_try_again(i).dname, loc = lt_try_again(i).loc
        where deptno = lt_try_again(i).deptno
        and decode(lt_dept(i).dname, dname, 0, 1)
          + decode(lt_dept(i).loc,   loc,   0, 1)
          = 0;

      for i in 1..lt_try_again.count loop
        if sql%bulk_rowcount(i) = 0 then raise_application_error(
          -20001,
          'DEPT row with DEPTNO = ' 
          || lt_dept(i).deptno 
          ||' already changed by another user. No updates have been made.'
          );
        else
          dbms_output.put_line('deptno '||lt_request(i).deptno||': second update succeeded');
        end if;
      end loop;
    end if;

  end update_depts;
end dept_json;

Optimistic Locking 8: double-checking with flashback

Some optimistic locking methods, including the SCN method, can detect intervening updates even when column values have not really changed. With the SCN method, we can use flashback queries to confirm that real changes occurred.

Flashback queries are evil

As soon as we mention flashback queries, some wise DBAs will immediately object that such queries harm the “shared pool”. This is a serious objection; let me explain why.

Before executing a SQL statement, Oracle must parse it and establish an execution plan: this is called the “hard parse”. Hard parsing can be an expensive operation, and it can be a bottleneck since it uses resources that must be serialized. To avoid constant parsing, Oracle stores executable forms of SQL cursors and PL/SQL programs in the “library cache”, which is part of the shared pool. This enables “parse once, execute many”, a prerequisite for good performance in OLTP databases.

Imagine a SQL statement that must be hard parsed before every execution. That one statement will use up resources and slow down parsing of other new statements. Not good. Now imagine that the “cursor” resulting from that parse goes into the library cache: the cache will get bigger and bigger and slower to navigate, until finally other cursors start getting aged out. We now have a dysfunctional database that spends its time hard parsing instead of executing statements.

Unfortunately, one of those statements that gets hard parsed every time is the AS OF flashback query :-(

To demonstrate this, I’ll run a simple AS OF query in a PL/SQL block, using the same SCN every time. Note that I flush the shared pool beforehand, so there will always be a first hard parse.

declare 
  l_scn number;
  l_cnt number;
begin
  dbms_flashback.disable;
  execute immediate 'alter system flush shared_pool';
  l_scn := dbms_flashback.get_system_change_number;
  for i in 1..10 loop
    select count(*) into l_cnt from T as of scn l_scn;
  end loop;
end;
/

Now I’ll query V$SQL_SHARED_CURSOR to see what cursors could not be “shared” and had to be hard parsed over and over again.

select child_number, parse_calls, executions, reason "Reason: AS OF SCN"
from v$sql
left join V$SQL_SHARED_CURSOR using(sql_id, child_number)
where sql_text like 'SELECT COUNT(*) FROM T%';

CHILD_NUMBER PARSE_CALLS EXECUTIONS Reason: AS OF SCN                                                               
------------ ----------- ---------- --------------------------------------------------------------------------------
           0           1          1 <ChildNode><ChildNumber>0</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           1           1          1 <ChildNode><ChildNumber>1</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           2           1          1 <ChildNode><ChildNumber>2</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           3           1          1 <ChildNode><ChildNumber>3</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           4           1          1 <ChildNode><ChildNumber>4</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           5           1          1 <ChildNode><ChildNumber>5</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           6           1          1 <ChildNode><ChildNumber>6</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           7           1          1 <ChildNode><ChildNumber>7</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           8           1          1 <ChildNode><ChildNumber>8</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           9           1          1 

Formatted content of first Reason:
<ChildNode>
  <ChildNumber>0</ChildNumber>
  <ID>21</ID>
  <reason>Flashback cursor(1)</reason>
  <size>3x4</size>
  <Flashback_cursor>0</Flashback_cursor>
  <As_of_cursor>1</As_of_cursor>
  <Flashback_session>0</Flashback_session>
</ChildNode>

Compare this to a normal query:

declare 
  l_cnt number;
begin
  execute immediate 'alter system flush shared_pool';
  for i in 1..10 loop
    select count(*) into l_cnt from T;
  end loop;
end;
/
select child_number, parse_calls, executions, reason "Reason: no flashback"
from v$sql
left join V$SQL_SHARED_CURSOR using(sql_id, child_number)
where sql_text like 'SELECT COUNT(*) FROM T%';

CHILD_NUMBER PARSE_CALLS EXECUTIONS Reason: no flashback
------------ ----------- ---------- --------------------
           0           1         10

Flashback queries are evil – not all the time

Instead of adding AS OF SCN to every table in my query, I can use DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER to set the “consistent read” SCN for every table in every query. Let’s see what happens with this technique:

declare 
  l_scn number;
  l_cnt number;
begin
  dbms_flashback.disable;
  execute immediate 'alter system flush shared_pool';
  l_scn := dbms_flashback.get_system_change_number - 1001;
  for i in 1..10 loop
    dbms_flashback.enable_at_system_change_number(l_scn+i);
    select count(*) into l_cnt from T;
    dbms_flashback.disable;
  end loop;
end;
/
select child_number, parse_calls, executions, reason "DBMS_FLASHBACK, increasing SCN"
from v$sql
left join V$SQL_SHARED_CURSOR using(sql_id, child_number)
where sql_text like 'SELECT COUNT(*) FROM T%';

CHILD_NUMBER PARSE_CALLS EXECUTIONS DBMS_FLASHBACK, increasing SCN
------------ ----------- ---------- ------------------------------
           0          10         10

Notice that there are 10 “parse calls” here. Oracle did a “soft parse”, that is it looked in the library cache for a cursor that could be reused, and it found one. This is extra work as compared to a normal query, but not nearly as much as a hard parse. More importantly, the library cache is not flooded with useless child cursors.

Conclusion

It turns out that we can safely use this technique under certain conditions:

  • The FLASHBACK and SELECT privileges must be granted on whatever tables we want to query;
  • The EXECUTE privilege must be granted on DBMS_FLASHBACK;
  • The SCN parameter of ENABLE_AT_SYSTEM_CHANGE_NUMBER must be increasing! If it decreases, we get hard parses and extra child cursors.
    This should never be a problem in the “optimistic locking” use case.

Optimistic Locking 7: Restartability

When we UPDATE, Oracle may find a discrepancy between the read-consistent and “current” versions of the data. If so, Oracle “restarts” the process. Our optimistic locking solution must make that happen when appropriate.

(It’s been a long time since my last post on this subject: see Optimistic Locking 4: the Good for context.)

What happens when we UPDATE?

(To simplify, I will only talk about the default READ COMMITTED isolation level.)

If we do an UPDATE without a prior SELECT FOR UPDATE,

  • Oracle will start by doing “consistent gets” of the target rows. This produces a result set that is read-consistent.
  • Next, Oracle will do “current gets” to lock and update the target rows.
    • If there is a predicate in the WHERE clause, such as WHERE HIREDATE > TRUNC(SYSDATE, 'YY'), and the column has been changed in the meantime, then Oracle will check whether the WHERE clause is still satisfied,
    • and if not then Oracle will “restart” the update processing.

Oracle will not stop until it has gotten and locked current versions of all the rows that meet the WHERE clause criteria in a read-consistent manner.

Re-evaluate, then (maybe) Restart

To make sure restarts happen when they should, we need to know:

  1. What columns set off the re-evaluation? To set off the re-evaluation, a changed column must be referenced in the WHERE clause or in a BEFORE UPDATE...FOR EACH ROW trigger. The ORA_ROWSCN pseudocolumn does not set off a re-evaluation.
  2. Once the re-evaluation is under way, what additional data is evaluated to decide whether to restart?
    If the re-evaluation takes place, the entire WHERE clause is evaluated.
    If ORA_ROWSCN is included in the WHERE clause, its “current” value is used in any comparison.

If we want to use ORA_ROWSCN for optimistic locking, we need to reference in the WHERE clause any columns we ourselves change. That will set off the re-evaluation, which will then use ORA_ROWSCN to determine whether some other process has changed the row since we last queried it.

See Avoiding Lost Updates with ORA_ROWSCN for a demonstration. Here is an example of an UPDATE that will restart when necessary:

update emp set sal = 800, deptno = 30
where empno = 7369
and ora_rowscn <= 13865770
and coalesce('*',sal || deptno) is not null;

I decided to use COALESCE to refer to the column values because it uses short-circuit evaluation. Once it determines that ‘*’ is not null, it doesn’t bother evaluating the concatenation of the columns. Also, concatenation implicitly converts every datatype to VARCHAR2 so I can list strings, dates, timestamps, numbers and intervals without worry. To prevent the Optimizer from removing that condition someday, I could replace ‘*’ by a bind variable with a non-null value.

Conclusion

ORA_ROWSCN can indeed be used as a “version number” that can be checked to avoid lost updates. The UPDATE statement just has to list the appropriate columns in the WHERE clause, but it doesn’t have to check them against values from a prior SELECT statement. The only real value that needs to be checked is ORA_ROWSCN.

In the next post I’ll address the problem of “false alarms”: ORA_ROWSCN will tell us that a row has previously been updated, but it will not say whether the values have really changed in the columns we care about.

CSV from CLOB with field enclosures

After my post about extracting CSV without enclosures from a CLOB, here is my solution for CSV with enclosures. It wasn’t easy…

How Oracle parses CSV

We can parse CSV in files using SQL*Loader, but I prefer External Tables with the ORACLE_LOADER access driver. Suppose an External Table has these parameters:

records delimited by NEWLINE
SKIP 0
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' NOTRIM
(or FIELDS CSV WITHOUT EMBEDDED NOTRIM in 12.2)

From the documentation and testing, what I see is this:

  1. With these parameters, we cannot embed record delimiters within quotes.
  2. When the field has no enclosures, all characters between commas are output, whitespace or not.
  3. When there are enclosures:
    1. Whitespace is allowed and stripped before and after enclosed fields, even with NOTRIM
    2. field terminators can be embedded between the enclosures
    3. record delimiters cannot be embedded: they end the record
    4. to be enclosed, field enclosures must be doubled: they are undoubled in the output
    5. Whitespace characters are: all characters considered whitespace by REGEXP ‘\s’
      9-13, 32 (space) – and 17 others if AL32UTF8!
    6. No second enclosure > error
    7. No delimiter between enclosed fields > error
    8. If there is a field delimiter at the end of a record, it ends the field but does not start a new field.

I admit that 3.A. (whitespace OK before and after enclosures) was a surprise to me.

My objective

I want to emulate the parameters listed above but I also want decent performance. For performance reasons, I decided to simplify the rules I follow:

  • 3.A. : no whitespace allowed before or after enclosed fields
  • 3.E. : I don’t care whether a character is whitespace or not.
  • 3.G. : the error is now “second enclosure not followed by a field delimiter”
  • Instead of LOG and BAD files, I log errors and bad input with DBMS_OUTPUT.PUT_LINE
  • I ignore extra newlines in the input, but I don’t attempt to suppress output with all null fields.

The code

This code should probably be written in C, which allows direct access to each character in a string. However, anyone who can install a C program on the Database server can probably copy the CLOB to a file and use External Table functionality directly! To make my PL/SQL as fast as possible, I use a technique from my COBOL and C days: the GOTO. With this type of code, calling subprocedures would measurably increase CPU usage.

create or replace function parse_csv_json_array(
  p_str in varchar2,
  p_log integer default null
) return varchar2 authid current_user is
/*
Objective: minimal service for parsing CSV with enclosures, similar to:
RECORDS DELIMITED BY NEWLINE
SKIP 0
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' NOTRIM
(or FIELDS CSV WITHOUT EMBEDDED NOTRIM in 12.2)
> EXCEPT: I allow no whitespace between field enclosures and field terminators.

- record delimiter = NEWLINE, field terminator = "," and field enclosure = '"'
  all are hard coded for the moment.
- record delimiter cannot be embedded in enclosed field.
- without enclosures, everything between field terminators and / or record delimiters is output
- with enclosures:
  - no whitespace allowed before or after enclosed fields
  - field terminators can be embedded between the enclosures
  - to be enclosed, field enclosures must be doubled: they are undoubled in the output
  - no second enclosure > 'Ending field enclosure not found'
  - no terminator after enclosed field > error 'Ending field enclosure not followed by field terminator'
  - If there is a field delimiter at the end of a record, it ends the field but does not start a new field.
- Instead of LOG and BAD files, I log errors and bad input with DBMS_OUTPUT.PUT_LINE
- I ignore extra newlines in the input, but I do not try to suppress output with all null fields.
- The input contains multiple records, so create an array of arrays: one inner array per record
*/
  l_out varchar2(4000);
  i_str integer;
  max_i_str integer;
  max_i_rec integer;
  i_end integer;
  i_num_backslash integer;
begin
  if p_str = '[]' then
    return p_str;
  end if;
  i_str := 3;
  max_i_rec := 2 - 2; -- end of "preceding record", counting 2 for length of record delimiter
  max_i_str := length(p_str) - 2;
  l_out := '[["';

  <<start_record>>
  i_end := max_i_rec + 3; -- length of record delimiter + 1
  i_end := instr(p_str, '\n', i_end);
  if i_end = 0 or i_end is null then
    -- end of record and end of input
    max_i_rec := max_i_str;
  else
    max_i_rec := i_end - 1;
    -- found \n at beginning, skip
    if max_i_rec < i_str then
      i_str := i_str + 2; -- length of record delimiter
      goto start_record;
    end if;
    -- check for '\\' before n
    i_num_backslash := 0;
    while substr(p_str, i_end-i_num_backslash-1,1) = '\' loop
      i_num_backslash := i_num_backslash + 1;
    end loop;
    if mod(i_num_backslash,2) = 1 then
      -- false alert, there was '\n' in the input and json_array made it '\\n'
      goto start_record;
    end if;
  end if;

  <<start_field>>
  if substr(p_str, i_str, 2) = '\"' then
    -- enclosures, so must do one character at a time
    i_str := i_str + 2;
    goto during_enclosed;
  else
    -- no enclosures, search for end of field in record
    i_end := instr(substr(p_str, i_str, max_i_rec - i_str + 1)||',', ',');
    l_out := l_out || substr(p_str, i_str, i_end - 1);
    i_str := i_str + i_end;
    if i_str <= max_i_rec + 1 then
      -- end of field, not end of record
      l_out := l_out || '","';
      goto start_field;
    elsif max_i_rec < max_i_str then
      -- last field of record, not last record
      i_str := max_i_rec + 3;
      l_out := l_out || '"],["';
      goto start_record;
    else
      -- last field of record, last record of input
      l_out := l_out || '"]]';
      goto end_input;
    end if;
  end if;

  <<during_enclosed>>
  i_end := instr(p_str, '\"', i_str);
  if i_end = 0 or i_end > max_i_rec then
    dbms_output.put_line('Ending field enclosure not found, input follows:');
    dbms_output.put_line('<'||json_value(p_str, '$[0]')||'>');
    l_out := null;
    goto end_input;
  end if;
  l_out := l_out || substr(p_str, i_str, i_end - i_str);
  i_str := i_end + 2;
  if substr(p_str, i_str, 2) = '\"' then
    l_out := l_out || '\"';
    i_str := i_str + 2;
  elsif substr(p_str, i_str, 1) = ',' then
      l_out := l_out || '","';
      i_str := i_str + 1;
      goto start_field;
  elsif i_str > max_i_str then
    l_out := l_out || '"]]';
    goto end_input;
  elsif i_str > max_i_rec then
    l_out := l_out || '"],["';
    i_str := max_i_rec + 3;
    goto start_record;
  else
    dbms_output.put_line('Ending field enclosure not followed by field terminator, input follows:');
    dbms_output.put_line('<'||json_value(p_str, '$[0]')||'>');
    l_out := null;
    goto end_input;
  end if;
  goto during_enclosed;

  <<end_input>>
  return l_out;
end parse_csv_json_array;
/

Test results

I took five columns from DBA_OBJECTS and added one VARCHAR2(100) and one NUMBER(4). I tested with four sizes of CLOBS: 100,000 records, 200,000 records, 400,000 records and 800,000 records. I compared input with no enclosures and with all fields enclosed.

Compared to the “simple” CSV solution, this solution is about 50% to 70% slower, depending on how many fields are enclosed. However, the number of records extracted per second remains stable as volume increases.

Please let me know if this solution is of practical interest to anyone…

Extract from CLOB with JSON objects

On AskTOM, Kim Berg Hansen recently used JSON_OBJECT to parse CLOB data in “name=value” format. I added a variant based on my work with CSV data.

Kim decided to use objects instead of arrays for an excellent and important reason:

  • a JSON object is an unordered collection of name/value pairs.
  • a JSON array is an ordered list of values.

CSV data is an ordered list of values, going from left to right, so a JSON array is the obvious choice. The AskTOM question concerned name/value pairs that were not necessarily in the same order and not necessarily all there! A JSON object was the natural choice.

I’m really liking JSON more and more: not only is it simple, but it seems pretty easy to figure out how best to use it for different problems.

Instead of copying everything here, I’m going to be lazy for once and invite you to read my contribution here:

More on JSON_TABLE

You will see some of the same ideas:

  • Use PIPE_CLOB to cut the CLOB into VARCHAR sized bites;
  • Use JSON_ARRAY on the whole bite to escape characters if needed;
  • Use REPLACE to form an overall array, but this time of objects;
  • Then use JSON_TABLE to generate one row per object and one column per value.

Hope this helps…

Extract CSV from CLOB with JSON arrays

Marc Bleron blogged about CSV CLOBs and JSON_TABLE two years ago. Here’s my contribution to improve on a great idea.

“Simple” CSV

The Oracle Database does a fine job of parsing CSV data in flat files, using the External Tables facility. Unfortunately, this service is not available for CSV data in VARCHARs or CLOBs. Marc showed that JSON_TABLE (and XML_TABLE) can parse “simple” CSV if it is reformatted. What is “simple”?

CSV data consists of records and fields within records.

  • Records are delimited by NEWLINE (or some other string).
  • Fields are terminated by commas (or some other string),
  • If necessary, some or all fields can be enclosed by double quotes " (or some other string).

When Marc says “simple”, he means that fields are never enclosed. This is important, because enclosed fields may contain the double quote (provided it is present twice in a row) and / or the comma. With “simple” CSV, we know that all commas are true field terminators and we don’t have to replace "" with " .

“Simple” also means that there is no trimming of whitespace: you get everything between the commas.

Finally, Marc assumes there is no terminator after the last field of the record, even though Oracle allows it.

So, “simple” CSV has delimited records with terminated fields that are never enclosed. There is no trimming of whitespace and the last field in the record is not terminated.

My contribution

  • First of all, I break the CLOB into VARCHAR2 bites using the pipelined table function PIPE_CLOB (as explained in my previous post).
  • Then I remove any field terminator that immediately precedes a record delimiter.
  • Then I use JSON_ARRAY over the entire VARCHAR2 in case some characters need to be escaped.
  • Then I do several REPLACES such that:
    • each record becomes a JSON array of string values, and
    • those arrays are included in one overall array.
  • Finally, I use JSON_TABLE to break the overall array into rows and the inner arrays into columns.

Note that everything before the COLUMNS clause in JSON_TABLE is generic, because the inner arrays can contain any number of elements.

To demonstrate, here is a CLOB containing data from the EMP table, with a trailing comma added:

7369,SMITH,CLERK,7902,1980-12-17T00:00:00,800,,20,
7499,ALLEN,SALESMAN,7698,1981-02-20T00:00:00,1600,300,30,
7521,WARD,SALESMAN,7698,1981-02-22T00:00:00,1250,500,30,
7566,JONES,MANAGER,7839,1981-04-02T00:00:00,2975,,20,
7654,MARTIN,SALESMAN,7698,1981-09-28T00:00:00,1250,1400,30,
7698,BLAKE,MANAGER,7839,1981-05-01T00:00:00,2850,,30,
7782,CLARK,MANAGER,7839,1981-06-09T00:00:00,2450,,10,
7839,KING,PRESIDENT,,1981-11-17T00:00:00,5000,,10,
7844,TURNER,SALESMAN,7698,1981-09-08T00:00:00,1500,0,30,
7876,ADAMS,CLERK,7788,1987-05-23T00:00:00,1100,,20,
7900,JAMES,CLERK,7698,1981-12-03T00:00:00,950,,30,
7902,FORD,ANALYST,7566,1981-12-03T00:00:00,3000,,20,
7934,MILLER,CLERK,7782,1982-01-23T00:00:00,1300,,10,

And the code:

with last_term_removed as (
  select replace(column_value, ','||chr(10), chr(10)) str
  from table(pipe_clob((select c from t), 3500))
)
, json_data as (
  select '[' ||
    replace (
      replace(json_array(str), ',', '","'),
      '\n',
      '"],["'
    )
    || ']' jstr  
  from last_term_removed
)
select sql_data.*
from json_data j, json_table(
   j.jstr, '$[*]'
   columns empno    number        path '$[0]'
         , ename    varchar2(128) path '$[1]'
         , job      varchar2(128) path '$[2]'
         , mgr      number        path '$[3]'
         , hiredate date          path '$[4]'
         , sal      number        path '$[5]'
         , comm     number        path '$[6]'
         , deptno   number        path '$[7]'
) sql_data;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10

 

Scalability: yes!

When Marc tested reading CLOBs directly, performance went bad as the CLOB increased in size:

Rows Seconds
91336 2.2
182672 4.3
365344 9.4
730688 22
1461376 840

 

In my tests with very similar data, the number of rows per second remains about the same:

LINES SECS LINES_PER_SEC AVG_LINES_PER_SEC PCT_DIFF
91336 1.374 66475 68517 -3
182672 2.6 70258 68517 2.5
365344 5.35 68289 68517 -.3
730688 10 73069 68517 6.6
1461376 22 66426 68517 -3.1