column update order
geraint....@gmail.com
Is the following safe, can we rely on the order of the columns being
updated? (Oracle 10g)
update t set old_date = current_date, current_date = sysdate;
Thanks
Charles Hooper
Let's try a test:
CREATE TABLE T4 (
OLD_DATE DATE,
CURRENT_DATE DATE);
INSERT INTO
T4
SELECT
TRUNC(SYSDATE-10*(50-ROWNUM)) OLD_DATE,
TRUNC(SYSDATE-5*(50-ROWNUM)) CURRENT_DATE
FROM
DBA_OBJECTS
WHERE
ROWNUM <=10;
COMMIT;
SELECT
*
FROM
T4;
OLD_DATE CURRENT_DATE
18-JUL-2005 20-MAR-2006
28-JUL-2005 25-MAR-2006
07-AUG-2005 30-MAR-2006
17-AUG-2005 04-APR-2006
27-AUG-2005 09-APR-2006
06-SEP-2005 14-APR-2006
16-SEP-2005 19-APR-2006
26-SEP-2005 24-APR-2006
06-OCT-2005 29-APR-2006
16-OCT-2005 04-MAY-2006
UPDATE
T4
SET
OLD_DATE=CURRENT_DATE,
CURRENT_DATE=TRUNC(SYSDATE);
10 ROWS UPDATED
SELECT
*
FROM
T4;
OLD_DATE CURRENT_DATE
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
21-NOV-2006 03:41:45 20-NOV-2006 00:00:00
That came out a bit different than expected. Wonder what happened?
CREATE TABLE T4 (
O_DATE DATE,
C_DATE DATE);
INSERT INTO
T4
SELECT
TRUNC(SYSDATE-10*(50-ROWNUM)) OLD_DATE,
TRUNC(SYSDATE-5*(50-ROWNUM)) CURRENT_DATE
FROM
DBA_OBJECTS
WHERE
ROWNUM <=10;
COMMIT;
SELECT
*
FROM
T4;
OLD_DATE CURRENT_DATE
18-JUL-2005 20-MAR-2006
28-JUL-2005 25-MAR-2006
07-AUG-2005 30-MAR-2006
17-AUG-2005 04-APR-2006
27-AUG-2005 09-APR-2006
06-SEP-2005 14-APR-2006
16-SEP-2005 19-APR-2006
26-SEP-2005 24-APR-2006
06-OCT-2005 29-APR-2006
16-OCT-2005 04-MAY-2006
UPDATE
T4
SET
O_DATE=C_DATE,
C_DATE=TRUNC(SYSDATE);
10 ROWS UPDATED
SELECT
*
FROM
T4;
O_DATE C_DATE
20-MAR-2006 20-NOV-2006
25-MAR-2006 20-NOV-2006
30-MAR-2006 20-NOV-2006
04-APR-2006 20-NOV-2006
09-APR-2006 20-NOV-2006
14-APR-2006 20-NOV-2006
19-APR-2006 20-NOV-2006
24-APR-2006 20-NOV-2006
29-APR-2006 20-NOV-2006
04-MAY-2006 20-NOV-2006
That was expected.
An individual UPDATE, INSERT, or DELETE will be consistent as of the
time the DDL begins, as will a SELECT. Additionally, within a
transaction, all SELECTs, UPDATEs, INSERTs, and DELETEs will be
consistent as of the start of the transaction.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Charles Hooper
> An individual UPDATE, INSERT, or DELETE will be consistent as of the
> time the DDL begins, as will a SELECT. Additionally, within a
> transaction, all SELECTs, UPDATEs, INSERTs, and DELETEs will be
> consistent as of the start of the transaction.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Should state DML (manipulation) not DDL (definition), in case that
causes any confusion. DDL causes an implicit COMMIT.
What's in a namespace
"Charles Hooper" <hoope...@yahoo.com> schreef in bericht
news:1164081790.2...@f16g2000cwb.googlegroups.com...
Charles,
I may have overlooked something, but I still don't understand why the column
OLD_DATE is filled with sysdate in the first example, and filled correctly
in the second example, for in both cases you did:
UPDATE
T4
SET
O_DATE=C_DATE,
C_DATE=TRUNC(SYSDATE);
Thanks,
Shakespeare
Robert Klemme
Frankly, I don't see the difference between the two examples other than
the column names. Are you sure that the first output was not caused by
naming the column CURRENT_DATE?
SQL> select current_date from dual;
CURRENT_
--------
21.11.06
SQL> @ C:\Temp\update.sql
CURRENT_
--------
21.11.06
Tabelle wurde erstellt.
10 Zeilen wurden erstellt.
Transaktion mit COMMIT abgeschlossen.
ROW_SEQ OLD_DATE CURRENT_
---------- -------- --------
1 19.07.05 21.03.06
2 29.07.05 26.03.06
3 08.08.05 31.03.06
4 18.08.05 05.04.06
5 28.08.05 10.04.06
6 07.09.05 15.04.06
7 17.09.05 20.04.06
8 27.09.05 25.04.06
9 07.10.05 30.04.06
10 17.10.05 05.05.06
10 Zeilen ausgewählt.
10 Zeilen wurden aktualisiert.
Transaktion mit COMMIT abgeschlossen.
ROW_SEQ OLD_DATE CURRENT_
---------- -------- --------
1 21.03.06 21.11.06
2 26.03.06 21.11.06
3 31.03.06 21.11.06
4 05.04.06 21.11.06
5 10.04.06 21.11.06
6 15.04.06 21.11.06
7 20.04.06 21.11.06
8 25.04.06 21.11.06
9 30.04.06 21.11.06
10 05.05.06 21.11.06
10 Zeilen ausgewählt.
Tabelle wurde gelöscht.
Kind regards
robert
Charles Hooper
This does not work as expected:
UPDATE
T4
SET
OLD_DATE=CURRENT_DATE,
CURRENT_DATE=TRUNC(SYSDATE);
This _does_ work as expected:
UPDATE
T4
SET
OLD_DATE=T4.CURRENT_DATE,
CURRENT_DATE=TRUNC(SYSDATE);
SELECT
*
FROM
V$RESERVED_WORDS
WHERE
KEYWORD='CURRENT_DATE';
KEYWORD RESERVED
CURRENT_DATE N
Charles Hooper
>
> CREATE TABLE T4 (
> ROW_SEQ NUMBER,
> OLD_DATE DATE,
> CURRENT_DATE DATE);
>
> INSERT INTO
> T4
> SELECT
> TRUNC(SYSDATE-10*(50-ROWNUM)) c1,
> TRUNC(SYSDATE-5*(50-ROWNUM)) c2
> FROM
> AG_URL
> WHERE
> ROWNUM <=10;
>
> COMMIT;
>
> SELECT *
> FROM T4
>
> UPDATE
> T4
> SET
> OLD_DATE=T4.CURRENT_DATE,
> T4.CURRENT_DATE=TRUNC(SYSDATE);
>
> COMMIT;
>
> SELECT *
> FROM T4
>
> DROP TABLE T4;
Good catch. I noticed that you appended the table name in front of
CURRENT_DATE, while the OP did not. I suspect that the OP may have
overlooked the possibility of something else was happening in the
system.
Interestingly, when I performed the above test from home connecting
into a 10.2.0.2 database with a 8.1.7 client, the OLD_DATE column was
set to SYSDATE + 5/24, while in the office connecting into a 10.2.0.2
database with a 10.2.0 client, OLD_DATE was set to SYSDATE. I guess
consistent has more than one definition.
What's in a namespace
AAARGH, reserved words again. I can hear DA Morgan laughing from
here..........
Shakespeare
(what's in a reserved name?)
Robert Klemme
> Robert Klemme wrote:
<snip/>
> Good catch. I noticed that you appended the table name in front of
> CURRENT_DATE, while the OP did not. I suspect that the OP may have
> overlooked the possibility of something else was happening in the
> system.
Guess so.
> Interestingly, when I performed the above test from home connecting
> into a 10.2.0.2 database with a 8.1.7 client, the OLD_DATE column was
> set to SYSDATE + 5/24, while in the office connecting into a 10.2.0.2
> database with a 10.2.0 client, OLD_DATE was set to SYSDATE. I guess
> consistent has more than one definition.
For all I know, these are the rules (could not find the place in doc as
reference where it is stated): column values appearing on the right side
of an assignment in UPDATE refer /always/ to the old value - even in the
light of multi column updates. If they appear on the left the column
value is changed in the course of the update.
Kind regards
robert
Charles Hooper
Note: Transaction Isolation Levels can be altered at the session level.
The default transaction isolation level does not enforce that SELECTs,
UPDATEs, INSERTs, and DELETEs will be consistent as of the start of the
transaction. If a column value is updated and committed in another
session, the changed values will be visible in the second session. For
example:
__Session 1__
CREATE TABLE TEST1 (
X NUMBER(12,4),
Y NUMBER(12,4));
CREATE TABLE TEST2 (
I NUMBER(12,4),
J NUMBER(12,4));
INSERT INTO TEST1 VALUES (1,1);
SELECT
*
FROM
TEST1;
X Y
---------------------------------------
1 1
1 ROW SELECTED
Note that I did not commit in session 1.
__Session 2__
SELECT
*
FROM
TEST1;
X Y
---------------------------------------
0 ROWS SELECTED
INSERT INTO TEST2 VALUES (5,1);
1 ROW INSERTED
SELECT
*
FROM
TEST2;
I J
---------------------------------------
5 1
Note that I did not commit in session 2.
__Session 1__
SELECT
*
FROM
TEST2;
I J
---------------------------------------
0 ROWS SELECTED
__Session 2__
COMMIT;
TRANSACTION COMMITTED
Session 2 commits.
__Session 1__
Session 1 still has an active transaction in progress due to the
previous insert without commit.
SELECT
*
FROM
TEST2;
I J
---------------------------------------
5 1
UPDATE
TEST1
SET
(X,Y)=(
SELECT
I*1.5,
J*I*1.5
FROM
TEST2);
SELECT
*
FROM
TEST1;
X Y
---------------------------------------
7.5 7.5
COMMIT;
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
UPDATE
TEST1
SET
(X,Y)=(
SELECT
I*1.5,
J*J*1.5
FROM
TEST2);
SELECT
*
FROM
TEST1;
X Y
---------------------------------------
7.5 1.5
Now, the Transaction Isolation Level has been set to keep the
transaction consistent. An update was performed without a COMMIT to
start the transaction.
__Session 2__
SELECT
*
FROM
TEST1;
X Y
---------------------------------------
7.5 7.5
UPDATE
TEST2
SET
I=I*COS(.45),
J=J*SIN(.45);
SELECT
*
FROM
TEST2;
I J
---------------------------------------
4.5022 0.435
COMMIT;
TRANSACTION COMMITTED
Session 2 still sees the old values that were changed by session 1, as
expected. Session 2 commits so that session 1 can see the values that
it changed.
__Session 1__
SELECT
*
FROM
TEST2;
I J
---------------------------------------
5 1
Session 1 still can't see the changes made by session 2, even though
session 2 committed.
See Chapter 7 of "Expert Oracle Database Architecture" for a full
explanation.
Robert Klemme
> Note: Transaction Isolation Levels can be altered at the session level.
> The default transaction isolation level does not enforce that SELECTs,
> UPDATEs, INSERTs, and DELETEs will be consistent as of the start of the
> transaction. If a column value is updated and committed in another
> session, the changed values will be visible in the second session. For
> example:
From all that I can tell this thread is not about multi session
consistency but about column values referenced in an UPDATE statement.
The original question was
<quote>
Is the following safe, can we rely on the order of the columns being
updated? (Oracle 10g)
update t set old_date = current_date, current_date = sysdate;
</quote>
I guess what the OP really wanted to know was whether it is safe to
refer to a column value in an UPDATE statement in a right hand side
expression and always get the /old/ value - even if the same column is
referred to in a left hand side expression. And for all I know it is.
Kind regards
robert
geraint....@gmail.com
Robert Klemme wrote:
> From all that I can tell this thread is not about multi session
> consistency but about column values referenced in an UPDATE statement.
> The original question was
>
> <quote>
> Is the following safe, can we rely on the order of the columns being
> updated? (Oracle 10g)
>
> update t set old_date = current_date, current_date = sysdate;
> </quote>
>
> I guess what the OP really wanted to know was whether it is safe to
> refer to a column value in an UPDATE statement in a right hand side
> expression and always get the /old/ value - even if the same column is
> referred to in a left hand side expression. And for all I know it is.
>
> Kind regards
>
> robert
Yes, that's what I really wanted to know.
Thanks.
(Apologies for confusing the issue by using a reserved word in my
example)
Michel Cadot
<geraint....@gmail.com> a écrit dans le message de news: 1164129607.2...@f16g2000cwb.googlegroups.com...
I don't read the whole thread (apologies if this was already answered)
but the answer is, the column order does not matter, you always get
the values at the beginning of the statement in the right members.
SQL> select * from t4;
O_DATE C_DATE
---------- ----------
19/07/2005 21/03/2006
29/07/2005 26/03/2006
08/08/2005 31/03/2006
18/08/2005 05/04/2006
28/08/2005 10/04/2006
07/09/2005 15/04/2006
17/09/2005 20/04/2006
27/09/2005 25/04/2006
07/10/2005 30/04/2006
17/10/2005 05/05/2006
10 rows selected.
SQL> update t4 set c_date=trunc(sysdate), o_date=c_date;
10 rows updated.
SQL> select * from t4;
O_DATE C_DATE
---------- ----------
21/03/2006 21/11/2006
26/03/2006 21/11/2006
31/03/2006 21/11/2006
05/04/2006 21/11/2006
10/04/2006 21/11/2006
15/04/2006 21/11/2006
20/04/2006 21/11/2006
25/04/2006 21/11/2006
30/04/2006 21/11/2006
05/05/2006 21/11/2006
10 rows selected.
Regards
Michel Cadot
Charles Hooper
You are absolutely correct. I was trying to correct an error in a
statement that I made in a previous post of this thread, where I
indicated that Oracle ensures read consistency within a transaction -
it will, but not by default.
CREATE TABLE T4 (
O_DATE DATE,
C_DATE DATE);
INSERT INTO
T4
SELECT
TRUNC(SYSDATE-10*(50-ROWNUM)) OLD_DATE,
TRUNC(SYSDATE-5*(50-ROWNUM)) CURRENT_DATE
FROM
DBA_OBJECTS
WHERE
ROWNUM <=10;
COMMIT;
Taking a look at the above, is it possible to confuse Oracle and have
it not assign a previously existing value? The following update
statement attempts to set the O_DATE to the value from the previous
row, when sorted by O_DATE, and set the C_DATE to the value from the
next row, when sorted by O_DATE.
UPDATE
T4
SET
(O_DATE,C_DATE)=
(SELECT
LAG(O_DATE,1) OVER (ORDER BY O_DATE),
LEAD(C_DATE,1) OVER (ORDER BY O_DATE)
FROM
T4 X
WHERE
T4.O_DATE=X.O_DATE);
The results:
O_DATE C_DATE
==================== ====================
10 ROWS SELECTED