Quick Quiz – Sequence NEXTVAL and CURRVAL

Answer this in your head before trying it out or looking it up. Assume the following script is run in a single session on an Oracle database:

CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1;

CREATE TABLE t (a INTEGER, b INTEGER);

— Statement #1
INSERT INTO t VALUES (seq.NEXTVAL, seq.NEXTVAL);

— Statement #2
INSERT INTO t VALUES (seq.CURRVAL, seq.NEXTVAL);

— Statement #3
INSERT INTO t VALUES (seq.CURRVAL, seq.CURRVAL);


Which of the following is/are true?
  1. The inserted rows will be {1,2}, {2,3} and {3,3}.
  2. The inserted rows will be {1,1}, {2,2} and {2,2}.
  3. The inserted rows will be {1,1}, {1,2} and {2,2}.
  4. Statements #2 and #3 will each raise “ORA-08002: sequence SEQ.CURRVAL is not yet defined in this session”.


Oracle 101: The (Very) Basics

Oracle Database SQL Reference 10g Release 1 (10.1): Datatype Comparison Rules – Numeric Values

“A larger value is considered greater than a smaller one.”

“All negative numbers are less than zero and all positive numbers.”

“-1 is less than 100; -100 is less than -1.”

On first reading these statements seem obvious. The more you read them, however, the more they take on a deeper meaning, a deeper structure and beauty that transcends this, sublimates that and begins subtly but inexorably to change the way you view the world. You start to question their simplicity – are they perhaps over-simplifications of a more complex reality? Well, perhaps not.

I guess they had to include these statements for completeness, since later they get into the more complicated cases of comparison rules for dates and strings, which are not (necessarily) so obvious. For example, I haven’t come across anyone who thinks 12 April 1961 is greater than 20 July 1969, but I’m sure there are some.


And you thought OR was commutative…

I came across this at dbdebunk (ON THE NOTHING THAT’S WRONG WITH NULLS with Hugh Darwen, Fabian Pascal). Couldn’t let that go so I tested it under 9i (9.1) and 10g (10.2) and got identical results. The last two statements are logically equivalent, but gives different results!

SQL> create type point as object (x real, y real);/

Type created.

SQL> create table t (p point);

Table created.

SQL> insert into t values (point(null, null));

1 row created.

SQL> insert into t values (point(1, null));

1 row created.

SQL> insert into t values (point(1, 2));

1 row created.

SQL> select * from t;

P(X, Y)
——————————————————————————–
POINT(NULL, NULL)
POINT(1, NULL)
POINT(1, 2)

SQL> select * from t where p=p or not p=p;

P(X, Y)
——————————————————————————–
POINT(1, 2)

SQL> select * from t where not p=p or p=p;

P(X, Y)
——————————————————————————–
POINT(1, NULL)
POINT(1, 2)

According to CM, it looks like a bug. The filter predicates seem to be applied incorrectly by the optimiser:

where ( p=p ) or ( not p=p );

filter(“T”.”SYS_NC00003$”=”T”.”SYS_NC00003$” AND
“T”.”SYS_NC00002$”=”T”.”SYS_NC00002$” OR T.”P”T.”P”)

where ( not p=p ) or ( p=p );

filter(T.”P”T.”P” OR “T”.”SYS_NC00002$”=”T”.”SYS_NC00002$”)


Is this code actually unreachable?

PL/SQL User’s Guide and Reference (10.2): “4 Using PL/SQL Control Structures – Using the NULL Statement”

“…Note that the use of the NULL statement might raise an unreachable code warning if warnings are enabled.”

Example 4-23 Using NULL as a Placeholder When Creating a Subprogram

CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS
BEGIN — executable part starts here
NULL; — use NULL as placeholder, raises “unreachable code” if warnings enabled
END award_bonus;
/

Indeed, when I compile the above in 10.2 with PL/SQL warnings on, I get PLW-06002 as expected (due to bug 3680132 I get “Message 6002 not found; No message file for product=plsql, facility=PLW” but at least I can look it up in the reference).

“PLW-06002: Unreachable code”
“Cause: Static program analysis determined that some code on the specified line would never be reached during execution.”

I agree that a PL/SQL warning would be desirable in the case where a procedure has nothing but a NULL in it (probably a stub). Correct me if I’m wrong, but if I were to call award_bonus, surely the NULL is “executed” – therefore, it is reachable! A more appropriate warning would be something like “function/procedure does nothing”, or “get back to work you silly mug, you’ve forgotten to finish the code”. Maybe they just couldn’t be bothered making up another warning code.


Did you know that NULL is a “Boolean value”? Hmmm…

PL/SQL User’s Guide and Reference (9.2 and 10.2): “NULL Statement”

“The NULL statement and Boolean value NULL are unrelated.”

I can understand that this is to draw a distinction between “NULL” as a procedural statement and “NULL” as a literal. But why is NULL specifically identified as Boolean? So, the NULL statement is somehow related to NULL strings, NULL numbers, and NULL dates?

(I won’t even mention the problem with calling NULL a value, something which is prevalent throughout the literature, including the SQL standard.)



Simplify your SQL – Avoid Unnecessary Joins

Simplify your SQL – Avoid Unnecessary Joins

I can’t count how many times I’ve seen SQL like the following:

SELECT b.some_column
FROM a, b
WHERE a.id = :p_id
AND b.id = a.id;

(where table a is unique on a.id, and table b has a referential integrity constraint to a, i.e. b.id references a.id)

I’ve seen many variations on this theme, where tables are unnecessarily referenced in a query. In this case, the query should have been formulated as:

SELECT b.some_column
FROM b
WHERE b.id = :p_id;

To be fair, this can often be blamed on maintenance by multiple programmers over time; maybe at some point some data from table a was needed, and only later removed; or we have a cut-and-paste artifact by a less experienced programmer.

The only problem with this is code simplicity (generally, the simpler the code, the easier it is to understand it, and therefore to maintain it); this will not usually be a performance issue. On Oracle, as long as you have a unique constraint on a.id, the optimiser will generally infer that :p_id can be used to directly access table b, and will then probably do a quick index unique scan on the a.id index (technically, the optimiser should know it doesn’t even have to do this, because of the foreign key constraint on b.id -> a.id and the unique constraint on a.id).

[Aside: Of course, if those constraints did NOT exist, then the two queries above would be logically different, and only one or the other would be correct for a given situation. The programmer must in either case know why they are or are not querying any particular table.]

Here’s a simple example using the SCOTT sample schema (run on Oracle 10.1.0.2, but should be pretty much the same on any version). I’ll issue two queries that are logically equivalent; the first one will unnecessarily access DEPT; the second one will access EMP directly.

==========================================================

SQL> select emp.ename from emp, dept where emp.deptno = dept.deptno and dept.deptno = 10;

ENAME
———-
CLARK
KING
MILLER

Elapsed: 00:00:00.01

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=5 Bytes=60)
1 0 NESTED LOOPS (Cost=3 Card=5 Bytes=60)
2 1 INDEX (UNIQUE SCAN) OF ‘PK_DEPT’ (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=3)
3 1 TABLE ACCESS (FULL) OF ‘EMP’ (TABLE) (Cost=3 Card=5 Bytes=45)

Statistics
———————————————————-
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
442 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

SQL> select emp.ename from emp where emp.deptno = 10;

ENAME
———-
CLARK
KING
MILLER

Elapsed: 00:00:00.01

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=5 Bytes=45)
1 0 TABLE ACCESS (FULL) OF ‘EMP’ (TABLE) (Cost=3 Card=5 Bytes=45)

Statistics
———————————————————-
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
442 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

==========================================================

As you can see, the second query is much simpler. The performance is pretty much identical.

What I haven’t included above is the predicates for the plans; if I had you’d see that the optimiser does actually apply the “deptno = 10” predicate directly to EMP (thanks to Connor for pointing this out).

In these simple queries it may not seem much of an issue; but for queries involving many tables with many predicates, every bit of unnecessary code makes it all the harder to read, understand and maintain.


Small SAMPLE bug in Oracle 9i (ORA-30561)

If you’re using the SAMPLE clause in Oracle 9i, in combination with a join, be aware of this small gotcha. I found a workaround, thankfully.

select * from (select * from dual), (select * from dual sample (10));

Expected result: should return zero or one row (more or less at random)

Actual result (tested with 9.2.0.6.0): “ORA-30561: SAMPLE option not allowed in statement with multiple table references”

Workaround: put the query with the SAMPLE clause first, i.e.

select * from (select * from dual sample (10)), (select * from dual);

Note: It works fine in 10g (tested under 10.1.0.2.0) since 10g does not restrict how many SAMPLE clauses are in a query.


Avoiding Lost Updates #3: The Solution

Background: see my earlier posts Avoiding Lost Updates
and Avoiding Lost Updates #2: Updateable Views

I’ve discussed this problem with my colleagues and have come up with a number of potential solutions, one of which we believe is bulletproof.

The desired property of such a solution is to reduce the probability that an update of a view will leave the row with the same “version” number, to as close to zero as possible. A bulletproof solution would have a probability of exactly zero.

Solution #1:

Instead of taking the SUM of all the versions, shift the versions from the outer-joined tables (e.g. by multiplying by 10000, 100000, etc).

This reduces the likelihood of getting a false negative; but it doesn’t reduce it to zero, since it would still be theoretically possible to get an update that would cause compensating deletes and updates on the outer-joined tables.

Solution #2:

Use ORA_ROWSCN instead of version.

This also reduces the likelihood of a false negative; in our view, astronomically so; but with a bit of mental gymnastics we can think of a contrived scenario in which the total of ORA_ROWSCN stays the same.

Solution #3:

Cause an update on the view to always do at least one update of one of the non-outer-joined tables (even if it is updating a column to the same value); and don’t include versions from outer-joined tables in the summed version.

This means that version will now always increase whenever an update occurs; therefore, the probability is zero, and the solution is bullet-proof!

Thanks to Howard and Maciej for their help.


Avoiding Lost Updates #2: Updateable Views

This is a followup to my earlier post “Avoiding Lost Updates: Protecting Data in a Multi-user environment”.

In this post I’ll talk about how this problem can be solved when you’re creating updateable views. Now, if you read the previous post, you’ll know the five options that I thought of (plus a sixth pointed out by Gary). For updateable views, if you want to protect against lost updates (in other words, “Last Update Wins” is not an acceptable solution), you can still use “Column Compare” and “Hash Compare” without any changes.

For the “Timestamp Compare”, “Version Compare” and Gary’s ORA_ROWSCN options, however, you’ll need to bring some data from the base tables up to the view’s SELECT clause.

For “Timestamp Compare” and ORA_ROWSCN, you need to get the latest of the timestamps/SCNs. You could use the GREATEST function for this (as long as ORA_ROWSCN is always greater for later transactions?).

It’s just as simple when you’re using “Version Compare”. It was pointed out by a colleague of mine that all you have to do is present the SUM of all the version columns from each base table that can be updateable via the view. This way, if any or all of the base tables experience changes, the “version” column in the view will increment by 1 or more. Most of the time, that is!

Let’s consider an example. We have the following imaginatively named base tables with a view on them:

CREATE TABLE table_a (
id NUMBER(12) PRIMARY KEY,
t VARCHAR2(10),
version NUMBER(38) NOT NULL);

CREATE TABLE table_b (
id NUMBER(12) PRIMARY KEY,
u VARCHAR2(30) NOT NULL,
version NUMBER(38) NOT NULL );

CREATE TABLE table_c (
id NUMBER(12) PRIMARY KEY,
v VARCHAR2(10) NOT NULL,
version NUMBER(38) NOT NULL );

CREATE TRIGGER table_a_ins_upd_row BEFORE INSERT OR UPDATE ON table_a FOR EACH ROW
BEGIN
IF inserting THEN :NEW.version := 1; ELSE :NEW.version := :OLD.version + 1; END IF;
END;
/

CREATE TRIGGER table_b_ins_upd_row BEFORE INSERT OR UPDATE ON table_b FOR EACH ROW
BEGIN
IF inserting THEN :NEW.version := 1; ELSE :NEW.version := :OLD.version + 1; END IF;
END;
/

CREATE TRIGGER table_c_ins_upd_row BEFORE INSERT OR UPDATE ON table_c FOR EACH ROW
BEGIN
IF inserting THEN :NEW.version := 1; ELSE :NEW.version := :OLD.version + 1; END IF;
END;
/

CREATE VIEW view_ab AS
SELECT table_a.id, table_a.t, table_b.u, table_c.v, table_a.version + table_b.version + table_c.version version
FROM table_a
LEFT OUTER JOIN table_b ON table_a.id = table_b.id
LEFT OUTER JOIN table_c ON table_a.id = table_c.id;

The view therefore shows all rows in table_a, as well as the corresponding rows in table_b and table_c if they exist.

Assume that the view_ab has instead-of-insert and update triggers that do the following:

Insert/update id and t on table_a.
If “u” is inserted or updated with a value, do the corresponding insert or update on table_b.
If “u” is cleared (set to NULL), delete it from table_b.
And the same for “v” in relation to table_c.

If I do this:

INSERT INTO view_ab (id, t, u, v) VALUES (1, 'A', 'B', 'C');

The view should have (1, ‘A’, ‘B’, ‘C’, 3). Version = 3 because we have one row in each of the three tables each with version = 1.

Now if I instead did:

INSERT INTO view_ab (id, t, u) VALUES (1, 'A', 'B');

The view should have the row (1, ‘A’, ‘B’, null, 2). Version = 2 because we have rows in table_a and table_b with version = 1, but no row in table_c.

The only purpose of the version column is to change if any insert, update or delete occurs on any of the base tables.

What if I do the following:

UPDATE view_ab SET u = NULL, v = 'C' where id = 1;

What will the view have? It will have (1, ‘A’, null, ‘C’, 2). Hang on there – I have done a change to the row, but the version has stayed the same! Why? Because the update trigger had to delete from table_b (reducing the total version to 1) and insert into table_c (increasing the total version back to 2).

How to solve this? This is answered in my next post.