This is mostly about the UPDATE command. Features which I noticed, that many do not know about. Some are really useful, but most fall in the category of “interesting to know”.
An update can do that!?
1 – we can update multiple columns with a single subselect
Update t1
set (a,b) = (select t2.a,t2.b from t2 where t1.id = t2.id)
...
2 – the returning clause of an update can have an aggregation
update t1
set c1 = c1+100
returning sum(c1) into …
3 – updates can be restricted to a partition
update t1 partition (p1)
set ...
4 – an update with flashback data from the same table is possible
UPDATE employees u
SET salary = (SELECT e.salary
FROM employees e
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE)
WHERE e.last_name = 'Chung')
WHERE u.last_name = 'Chung';
5 – we can update a TABLE() expression
example from the docs
update TABLE(select h.people -- this is a nested table column
from hr_info h
where h.department_id = 280 ) p
SET p.salary = p.salary + 100;
6 – SELECT can be harmful because of the FOR UPDATE clause
Using only the SELECT privilege on a table we can lock the whole table by using SELECT FOR UPDATE. So using only select privileges we can do some serious harm for a running application. That is one reason why SELECT ANY TABLE is so dangerous to grant. The new READ privilege avoids that issue. It allows to SELECT a table but not to LOCK it. READ was introduced in Oracle DB 12.1.0.2
7 – SELECT FOR UPDATE allows to skip locked rows
select *
from employees
where department = 'IT'
for update of salary
skip locked;
8 – the LOG ERRORS clause can have a text (a simple expression) to indicate which update produced the error
Usually log errors is used during insert operations. But it is also possible for updates.
And the chance is high, that we will run multiple updates, so it makes even more sense to mark the single update by adding some text to the “tag” column.
create table t2 (id number primary key, str varchar2(100));
insert into t2 values (1, '10-AUG-2018');
insert into t2 values (2, '13-NOV-2018');
insert into t2 values (3, 'SEP-15-2018');
commit;
BEGIN
DBMS_ERRLOG.create_error_log (dml_table_name => 'T2');
END;
/
alter table t2 add dt date;
update t2
set dt = to_date(str)
log errors into ERR$_T2('update try 1, format='||sys_context('userenv','nls_date_format'))
reject limit unlimited;
select ora_err_mesg$, ora_err_tag$, id, str from err$_t2;
drop table t2;
drop table err$_t2;
| ORA_ERR_MESG$ | ORA_ERR_TAG$ | ID | STR |
|---|
| ORA-01858: a non-numeric character was found where a numeric was expected | update try 1, format=DD-MON-RR | 3 | SEP-15-2018 |
Our string to date conversion failed, but we captured the row that failed and also the sessions nls_date_format.
9 – materialized views can be made updateable
create materialized view myMV ...
refresh fast
with primary key for update
...;
update myMV
set col1 = 'ABC'
...;
But after a refresh the changes are lost!
10 – We can update a column to its DEFAULT value
Which is not the same as setting it to null (unless the column is declared with DEFAULT ON NULL).
alter table scott.emp modify hiredate default sysdate;
update scott.emp
set hiredate = default
where empno = 7900;
select * from scott.emp where empno=7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 07.11.18 12:38:22 950 30
restrictions and other features
11 – we can not combine select for update with a row limiting clause (fetch first)
select *
from emp
where empno = 7900
fetch first 1 row only
for update of job;
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
But we can select for update a table limited by rownum.
select *
from emp
where empno = 7900
and rownum = 1
for update of job;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7900 JAMES CLERK 7698 03.12.81 00:00:00 950 30
1 row selected.
The reason of cause is, how the row limiting clause is rewritten using the analytic ROW_NUMBER() function.
12 – A before update statement trigger can trigger twice
see also: this OTN thread
and The trouble with triggers by Tom Kyte.
BEFORE Triggers Fired Multiple Times
If an UPDATE or DELETE statement detects a conflict with a concurrent UPDATE, then Oracle Database performs a transparent ROLLBACK to SAVEPOINT and restarts the update. This can occur many times before the statement completes successfully. Each time the statement is restarted, the BEFORE statement trigger is fired again. The rollback to savepoint does not undo changes to any package variables referenced in the trigger.
13 – the MODEL clause has a RETURN UPDATED ROWS mode
This mode only returns rows that were updated by one of the rules.
select * from scott.emp
model RETURN UPDATED ROWS
dimension by (empno)
measures (job, sal, comm)
rules (job[7900] = 'DRIVER'
,comm[7900] = 300
);
EMPNO JOB SAL COMM
7900 DRIVER 950 300
Without the RETURN UPDATED ROWS setting all the rows would have been selected.
A slightly different effect can be reached using “RULES UPDATE”. The rule is used only for those rows, that existed already. No new rows will be created. Default is UPSERT (rows inserted and updated by the rules).
We now partition additionally by department.
select * from scott.emp
MODEL
partition by (deptno)
dimension by (empno)
measures (job, sal, comm)
rules UPDATE (
job[7900] = 'DRIVER'
,comm[7900] = 300
);
DEPTNO EMPNO JOB SAL COMM
20 7369 CLERK 800
30 7499 SALESMAN 1600 300
30 7521 SALESMAN 1250 500
20 7566 MANAGER 2975
30 7654 SALESMAN 1250 1400
30 7698 MANAGER 2850
10 7782 MANAGER 2450
10 7839 PRESIDENT 5000
30 7844 SALESMAN 1500 0
20 7902 ANALYST 3000
10 7934 CLERK 1300
30 7900 DRIVER 950 300
The rules are executed for each partition. And without the UPDATE setting, a new entry for department 10 and 20 would have been made. Since we used UPDATE, only one existing row was changed.
14 – an update can change rowids
ROWIDs are very stable. Row chaining will not change the rowid and not even row migration will do it.
But there are ways (I know two, there might be more) how the original rowid can change.
- An update on the partition key can move the row to a different partition.
- When updateing a row in a table compressed with Hybrid Columnar Compression, the ROWID of the row may change.
15 – An update with RETRY_ON_ROW_CHANGE hint is retried, if ORA_ROWSCN changed
doc: RETRY_ON_ROW_CHANGED
So far it is not clear what the RETRY_ON_ROW_CHANGE hint is good for. It was discussed that it might be used in connection with edition based redefinition (EBR) and cross edition triggers.
16 – parallel UPDATE is not supported for temporary tables.
Same goes for DELETE and MERGE.
Potential test code (not verified yet)
ALTER SESSION ENABLE PARALLEL DML
create global temporary table t1 (col1 number, col2 varchar2(100));
insert into t1
select level as col1,'0' as col2
from dual connect by level <= 100000;
update /*+ parallel(t1, 4) */ t1
set col2=sys_context('userenv','sid')
where col1 < 90000;
select col2, count(*) cnt from t1 group by col2;
drop table t1;
COL2 CNT
---- -----
612 89999
0 10001
We see only one session (sid=612), so the conclusion is that the parallel hint was ignored.
The code above is how I think, this can be tested. However the test also needs to ensure, that when using a real table, that more than one session is used and reported via the sys_context. I couldn’t verify that yet (didn’t work on livesql and parallel is not an option on Standard Edition, so don’t try it there).
17 – it is possible to update remote lobs
The 12.2 new features guide has some information ( 12.2 new features guide)
and there is a direct note in the description of the UPDATE command: 12.2. sql reference (UPDATE)
Starting with Oracle Database 12c Release 2 (12.2), the UPDATE statement accepts remote LOB locators as bind variables. Refer to the “Distributed LOBs” chapter in Oracle Database SecureFiles and Large Objects Developer’s Guide for more information.
SecureFiles: Distributed LOBs
So far I didn’t have the chance to test it. But it looks useful.
18 – To update an identity column is not allowed
create table t1 (id number generated as identity, name varchar2(100));
insert into t1(name) values ('Fred');
insert into t1(name) values ('Wilma');
insert into t1(name) values ('Barney');
update t1
set id = 4
where name = 'Fred';
ORA-32796: cannot update a generated always identity column
Also a virtual column can not be updated. However an invisible column can – unless it is virtual or an identity column.
The identity restriction is one of the main reasons, why I prefere to create a column as DEFAULT ON NULL with a value for the sequence.
create sequence t1_seq;
create table t1 (id number default on null t1_seq.nextval primary key, name varchar2(100));
19 – the number of updates against a table can be seen in xxx_TAB_MODIFICATIONS
select inserts, updates, deletes, truncated, timestamp
from USER_TAB_MODIFICATIONS
where table_name = 'MYTABLE';
INSERTS UPDATES DELETES TRUNCATED TIMESTAMP
763 15799 761 NO 07.11.18 12:29:18
Data is tracked since the last time the statistics gathering job updated statistics or more consice when the statistics job decided, that the data in that table is stale. Sometimes this corresponds to the LAST_ANALYZED column in xxx_TAB_STATISTICS.
Two interesting blog posts that cover this useful feature:
Ulrike Schwirn (in German): Tabellen Monitoring mit DBA_TAB_MODIFICATIONS und SYS.COL_USAGE$
Martin Widlake: DBA_TAB_MODIFICATIONS
20 – at least 19 SQL commands have an “UPDATE” keyword
The following SQL commands can have “UPDATE” as a syntax keyword in some of their clauses included. The SELECT command has three different clauses. A command that allows to use a SELECT and therefore also an UPDATE is not counted for its select clause.
I’m not sure if the list is complete, but I searched through all syntax diagrams of 18.1. Feel free to comment if you know of another statement that allows a specific UPDATE keyword. Maybe there is something new in 18.3.
| COMMAND | clause | additional info |
| ADMINISTER KEY MANAGEMENT | update secret | Doc: 18.1 SQL ref |
| ALTER AUDIT POLICY | add|drop actions update |
| ALTER INDEX | update block references | for IOTs only |
| ALTER TABLE | update indexes | avoids indices to become UNUSABLE |
| ANALYZE | validate ref update | compare and correct rowids for REF values |
| AUDIT|NOAUDIT | update table|view|mv | audit of the update command |
| CREATE AUDIT POLICY | update table|view|mv | audit (new version) of the update command |
| CREATE MATERIALIZED VIEW | with primary key for update | creates an updateable MV |
| CREATE OUTLINE | on update |
| CREATE PLUGGABLE DATABASE | container_map update | partitions created in cdb$root or application root are also updated in the new PDB. |
| CREATE TRIGGER | before|after update |
| EXPLAIN PLAN | for update |
| GRANT|REVOKE | update on table|update any table|update any cube|… |
| LOCK TABLE | share update | same as ROW SHARE, lock modes ROW SHARE and SHARE UPDATE |
| MERGE | when matched then update |
| SELECT | for update | locks the selected rows |
| SELECT | model return updated rows;
model … rules update | part of the model clause |
| UPDATE | whole command |
Fairly easy, once we know what to do. In the future I might use this session storage for other things too.
svenweller 