Update的概述：复杂的索引要求，效率优化
隐藏在update后面的约束
定义用于更新其他数据集的数据为源数据，被更新的数据称为目标数据，原数据集合与目标数据之间的关联键，在源数据集中一定唯一。不会存在着目标数据一次更新过程中被更新2次。
create table b1 –目标数据
(
cust_id varchar2(10),
cust_name varchar2(10)
)
CUST_ID CUST_NAME
123 xxx
122 yyy
create table b2 原数据集合
(
cust_id varchar2(10),
cust_name varchar2(10)
)
CUST_ID CUST_NAME
122 uuu
122 ppp
update b1
set cust_name= (select cust_name from b2 where b2.cust_id=b1.cust_id)
因此update 一定有0..1-->0..n之间的关系
--
更新源表数据范围
考虑到以下的例子，编写人员来希望满足条件 B.keyname = O.keyname and O.Othercolumn = Other_value的记录被更新，给过所有记录被更新
update table name B
set columnname =
( select value
from lookup O
where B.keyname = O.keyname
and O.Othercolumn =Other_value);
create table name
(keyname int,
columnName varchar2(25)
)
create table lookup
(keyname int PRIMARY KEY,
value varchar2(25),
otherColumn int
)
insert into name values ( 100, 'Original Data');
insert into name values ( 200, 'OriginalData' );
insert into lookup values ( 100, 'NewData', 1 );
select * from name
KEYNAME COLUMNNAME
------------------------------------------------------------------
100 OriginalData
200 OriginalData
select * from lookup
KEYNAME VALUE OTHERCOLUMN
---------------------------------------------------------------------
100 NewData 1
update name b
set columnname =
( select value
from lookup O
where B.keyname = O.keyname
and O.Othercolumn = 1);
2
update name
set columnName = ( select value
from lookup
where lookup.keyname= name.keyname
and otherColumn = :other_value )
where exists ( select value
from lookup
where lookup.keyname =name.keyname
and otherColumn = 1)
3
update
( select columnName, value
from name, lookup
where name.keyname = lookup.keyname
and lookup.otherColumn = 1 )
set columnName = value
2 与 3 那个快？
这要看具体情况
但是视图更新编写很简单
一般 如果name表小，而look表很大
In general, if "NAME" was veryvery small and "LOOKUP" was very very large (yet
indexed on keyname/othercolumn/value) --the where exists would be very safe.
--------------------------------------
1、根据某字段是否存在另一结果集中来更新当前表
UPDATE ORG_TABLE
SET FIELD1=XXXX
WHERE FIELD_XXX IN (SELECT FIELD1 FROMREF_TABLE WHERE ORA_TABLE.F1=REF_TABLE.F1 AND REF_TABLE.F2.......)
是用EXIST 还是 IN?
结论：还记得当SELECT的时候 1、在9I以及以前的版本中，如果 后面的结果集比较大 用EXIST 如果用结果集表小，用IN
对于UPDATE又如何？
对于9i 的update 建议依然如此，特别是参考表中利用到索引，结果集较小，结合源表与参考表索引
举例说明：
----------------------------------------------
SQL> DESCbig_table
Name Type Nullable Default Comments
-------------------------- -------- ------- --------
ID NUMBER
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAMEVARCHAR2(30) Y
OBJECT_ID NUMBER
DATA_OBJECT_IDNUMBER Y
OBJECT_TYPE VARCHAR2(19) Y
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19) Y
STATUS VARCHAR2(7) Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
select COUNT(*) from big_table WHEREID<11004
建立参考表
----------------------------------------------------------
DROP TABLE BIG_TABLE_ID PURGE
CREATE TABLE BIG_TABLE_ID AS SELECT ID FROM BIG_TABLE
UPDATE BIG_TABLE
SET subobject_name='XXXXXX'
WHERE ID IN (SELECT ID FROM BIG_TABLE_IDWHERE ID<4989002)
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=23311 Depth=0 Cardinality=249451
UPDATE Depth=1 Object name=BIG_TABLE Object owner=SCOTT
HASH JOIN RIGHT SEMI Cost=23311 Depth=2 Cardinality=249451
TABLE ACCESS FULL Cost=1872 Depth=3 Cardinality=249450 Object name=BIG_TABLE_ID Object owner=SCOTT
TABLE ACCESS FULL Cost=16535 Depth=3 Cardinality=4978004 Object name=BIG_TABLE Object owner=SCOTT
发现半HASH -JOINSEMI,对此作出解释，同时解释IN(ESIST) 与 TABLE1,TABLE2 HASH JOIN之间的区别
当我们调节ID后面的参数时候发现
1、HASH JOIN SEMI后面的顺序发生变化
2、成本发生变化
UPDATE BIG_TABLE
SET subobject_name='XXXXXX'
WHERE EXISTS (SELECT NULL FROM BIG_TABLE_IDWHERE BIG_TABLE.ID=BIG_TABLE_ID.ID ANDID<498000 )
UPDATE BIG_TABLE
SET subobject_name=(CASE WHEN ID IN (SELECTID FROM BIG_TABLE_ID ) THEN 'XXXXXX' ELSE subobject_name END)
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=16492 Depth=0 Cardinality=4989002
UPDATE Depth=1 Object name=BIG_TABLE Object owner=SCOTT
TABLE ACCESS FULL Cost=16492 Depth=2 Cardinality=4989002 Object name=BIG_TABLE Object owner=SCOTT
TABLE ACCESS FULL Cost=1872 Depth=2 Cardinality=49890 Object name=BIG_TABLE_ID Object owner=SCOTT
看看在REF表上建立索引后，能否优化
CREATE UNIQUE INDEX TTTEXT_IDX ON BIG_TABLE_ID(ID) PARALLEL NOLOGGING
UPDATE BIG_TABLE
SET subobject_name=(CASE WHEN ID IN (SELECTID FROM BIG_TABLE_ID ) THEN 'XXXXXX' ELSE subobject_name END)
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=16492 Depth=0 Cardinality=4989002
UPDATE Depth=1 Object name=BIG_TABLE Object owner=SCOTT
TABLE ACCESS FULL Cost=16492 Depth=2 Cardinality=4989002 Object name=BIG_TABLE Object owner=SCOTT
FILTER Depth=2
INDEX UNIQUE SCAN Cost=2 Depth=3 Cardinality=1 Object name=TTTEXT_IDX Object owner=SCOTT
发现索引被用上
UPDATE BIG_TABLE
SET subobject_name='XXXXXX'
WHERE ID IN (SELECT ID FROM BIG_TABLE_IDWHERE ID<4989)
观察改变ID<4989
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=5 Depth=0 Cardinality=1
UPDATE Depth=1 Object name=BIG_TABLE Object owner=SCOTT
NESTEDLOOPS Cost=5 Depth=2 Cardinality=1
INDEX RANGESCAN Cost=3 Depth=3 Cardinality=1 Object name=BIG_TABLE_PK Object owner=SCOTT
INDEX UNIQUE SCAN Cost=1 Depth=3 Cardinality=1 Object name=TTTEXT_IDX Object owner=SCOTT
发现仅仅通过索引连接就能得到计算结果，而且当结果集变大的时候JOIN方式发生改变（NESTEDJOIN SEMI ===>HASH JOIN SEMI）
UPDATE BIG_TABLE
SET subobject_name='XXXXXX'
WHERE EXISTS (SELECT NULL FROM BIG_TABLE_IDWHERE BIG_TABLE.ID=BIG_TABLE_ID.ID ANDID<49800)
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=764 Depth=0 Cardinality=38801
UPDATE Depth=1 Object name=BIG_TABLE Object owner=SCOTT
HASH JOIN SEMI Cost=764 Depth=2 Cardinality=38801
INDEX RANGESCAN Cost=80 Depth=3 Cardinality=38801 Object name=BIG_TABLE_PK Object owner=SCOTT
INDEX RANGESCAN Cost=103 Depth=3 Cardinality=249450 Object name=TTTEXT_IDX Object owner=SCOTT
与IN 一样
就以上的例子，ORA_TABLEJOIN REF_TABLE 存在 1..0-1的关联关系
UPDATE (SELECT T2.ID,T1.subobject_name FROMBIG_TABLE T1,BIG_TABLE_ID T2 WHERE T1.ID=T2.ID(+) AND T2.ID<49000) V
SET V.subobject_name=(CASE WHEN V.ID ISNULL THEN 'XXXXXX' ELSE V.subobject_name END)
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=751 Depth=0 Cardinality=38001
UPDATE Depth=1 Object name=BIG_TABLE Object owner=SCOTT
HASH JOIN Cost=751 Depth=2 Cardinality=38001
TABLE ACCESS BY INDEX ROWID Cost=642 Depth=3 Cardinality=38001 Object name=BIG_TABLE Object owner=SCOTT
INDEX RANGESCAN Cost=79 Depth=4 Cardinality=38001 Object name=BIG_TABLE_PK Object owner=SCOTT
INDEX RANGESCAN Cost=103 Depth=3 Cardinality=249450 Object name=TTTEXT_IDX Object owner=SCOTT
就目前的例子，其实还有更简单的写法
UPDATE (SELECT T2.ID,T1.subobject_name FROMBIG_TABLE T1,BIG_TABLE_ID T2 WHERE T1.ID=T2.ID AND T2.ID<490) V
SET V.subobject_name= 'XXXXXX'
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=751 Depth=0 Cardinality=38001
UPDATE Depth=1 Object name=BIG_TABLE Object owner=SCOTT
HASH JOIN Cost=751 Depth=2 Cardinality=38001
INDEX RANGESCAN Cost=79 Depth=3 Cardinality=38001 Object name=BIG_TABLE_PK Object owner=SCOTT
INDEX RANGESCAN Cost=103 Depth=3 Cardinality=249450 Object name=TTTEXT_IDX Object owner=SCOTT
调节T2.ID<490，可以看到JOIN的方式发生变化
以上是视图更新更新，这是一种新的更新方法
视图更新介绍
----------------------------------------------------------
alter session setnls_date_format='yyyy-mm-dd'
视图更新事例：
create table a1
(
a1v1 number,
a1v2 number,
a1flag varchar2(2)
)
INSERT INTO A1(A1V1,A1V2,A1FLAG)VALUES(11,22,'t');
INSERT INTO A1(A1V1,A1V2,A1FLAG)VALUES(11,23,'f');
INSERT INTO A1(A1V1,A1V2,A1FLAG)VALUES(12,22,'t');
----------------------------------
11 22 t
11 23 f
12 22 t
create table a2
(
a2v1 number,
a2v2 number,
a2flag varchar2(2)
)
INSERT INTO A2(A2V1,A2V2,A2FLAG)VALUES(11,31,null);
INSERT INTO A2(A2V1,A2V2,A2FLAG)VALUES(12,35,null);
INSERT INTO A2(A2V1,A2V2,A2FLAG) VALUES(13,88,null);
A2V1 A2V2 A2FLAG
-----------------------------------
11 31
12 35
13 88
update a2
set a2v2=(select a1v2 from a1 where a1.a1v1=a2.a2v1 )
发生以下错误：
ora-01427 单行子查询返回多个行
update a2
set a2v2=(select a1v2 from a1 where a1.a1v1=a2.a2v1 anda1.A1FLAG='t')
--成功
select * from a2
A2V1 A2V2 A2FLAG
--------------------------------
11 22
12 22
13 null
select null from a1,a2 where a1.a1v1=a2.a2v1 and a1.A1FLAG='t'
null
---
null
null
update a2
set a2v2=(select a1v2 from a1 where a1.a1v1=a2.a2v1 anda1.A1FLAG='t')
where exists (select null from a1 where a1.a1v1=a2.a2v1 anda1.A1FLAG='t')
select * from a2
A2V1A2V2 A2FLAG
-----------------------------
11 22
12 22
13 88
-----------------------------------------------------------------------------------------------------
update
(select a1.a1v2 v1 ,a2.a2v2 v2 from a1,a2 where a1.a1v1=a2.a2v1 and a1.A1FLAG='t')
set v2=v1
ora-01779 无法修改与非键值保存表对应的列
ALTER TABLE SH.A1 drop CONSTRAINT pka1
ALTER TABLE SH.A1 ADD CONSTRAINT pka1 PRIMARY KEY (A1FLAG, A1V1)
update
(select a1.a1v2 v1 ,a2.a2v2 v2 from a1,a2 where a1.a1v1=a2.a2v1 and a1.A1FLAG='t')
set v2=v1
ora-01779 无法修改与非键值保存表对应的列
ALTER TABLE SH.A1 drop CONSTRAINT pka1
ALTER TABLE SH.A2 ADD CONSTRAINT pka2 PRIMARY KEY (A2V1)
update
(
select a1.a1v2 v1 ,a2.a2v2 v2,a1.a1flag
from a1,a2
where a1.a1v1=a2.a2v1 and a1.A1FLAG='t'
)
set v2=v1
update
(select a1.a1v2 v1 ,a2.a2v2 v2 from a1,a2 where a1.a1v1=a2.a2v1 and a1.A1FLAG='t') p
set p.v2=p.v1
ALTER TABLE SH.A1 drop CONSTRAINT pka1
ALTER TABLE SH.A2 drop CONSTRAINT pka2
select * from a1 for update
A1V1 A1V2 A1FLAG
--------------------------------------------------
11 22 t
18 23 f
12 22 t
ALTER TABLE SH.A1 ADD CONSTRAINT pka1 PRIMARY KEY (A1V1)
update
(select a1.a1v2 v1 ,a2.a2v2 v2 from a1,a2 where a1.a1v1=a2.a2v1 and a1.A1FLAG='t') p
set p.v2=p.v1
successful
select * from a2
A2V1 A2V2 A2FLAG
----------------------------------
11 22
12 22
13 88
知道，怎么避免错误ora-01779无法修改与非键值保存表对应的列了
只要在where access后面的关联条件数据源表上存在主键（或者唯一性要求）
这个要求也太严格了
尝试试图主键方法
ALTER TABLE SH.A1 drop CONSTRAINT pka1
ALTER TABLE SH.A1 ADD CONSTRAINT pka1 PRIMARY KEY (A1FLAG, A1V1)
select * from a2 for update
A2V1 A2V2 A2FLAG
-----------------------------------------
11 31
12 35
13 88
11 99
--create view va1a2 as
select a1.a1v2 v1 ,a2.a2v2 v2 from a1,a2 where a1.a1v1=a2.a2v1 and a1.A1FLAG='t'
ALTER TABLE SH.A1 drop CONSTRAINT pka1
ALTER TABLE SH.A1 ADD CONSTRAINT pka1 PRIMARY KEY ( A1V1)
update
(select a1.a1v2 v1 ,a2.a2v2 v2 from a1,a2 where a1.a1v1=a2.a2v1 and a1.A1FLAG='t') p
set p.v2=p.v1
select * from a2
A2V1 A2V2 A2FLAG
11 22
12 22
13 88
11 22
因此
尝试试图主键方法是走不通的，有点郁闷。
能否这样
create view v_a1 as
select * from a1 where A1FLAG='t'
select * from v_a1
A1V1 A1V2 A1FLAG
-----------------------------------
11 22 t
12 22 t
alter view v_a1 add constraint v_a1_pk primary key (a1v1) disable novalidate
update
(
select aa1.a1v2 v1 ,a2.a2v2 v2 from v_a1 aa1, a2 where aa1.a1v1=a2.a2v1
) p
set p.v2=p.v1
ok success
看来 oracle 没有什么做不到的
----------------------------------------------------------------------------------------
多表的视图更新
create table a ( x int primary key, y int );
create table b ( x references a primary key );
create table c ( x references b primarykey, y int );
insert into a values ( 1, null );
insert into a values ( 2, null );
insert into b values ( 1 );
insert into c values ( 1, 100 );
select * from a;
update ( select a.y a_y, c.y c_y
from a, b, c
where a.x = b.x and b.x = c.x )
set a_y = c_y;
select * from a
在视图更新过程中，只有被更新表才会被锁定
select type, id1, (select object_name fromuser_objects where object_id = id1 ) oname from v$lock
视图更新过程中的别名问题
update
( select a.pants, b.pants
from test a, test2 b
where a.ssn = b.ssn)
set a.pants = b.pants
set a.pants = b.pants
*
ERROR at line 5:
ORA-00904: invalid column name
并行update
conn sh/sh
UPDATE customers
SET(cust_first_name, cust_street_address) =
(SELECT cust_first_name, cust_street_address
FROM customers1
WHERE customers1.cust_id = customers.cust_id)
WHERE cust_id IN(SELECT cust_id FROM customers1);
在上面的语句中，自查询会影响性能的
在customers的cust_id上存在主键
以下是执行计划，2个表全是全表扫描
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=665 Depth=0 Cardinality=54380
UPDATE Depth=1 Object name=CUSTOMERS Object owner=SH
HASH JOIN RIGHT SEMI Cost=665 Depth=2 Cardinality=54380
TABLE ACCESS FULL Cost=330 Depth=3 Cardinality=55500 Object name=CUSTOMERS1 Object owner=SH
TABLE ACCESS FULL Cost=332 Depth=3 Cardinality=55500 Object name=CUSTOMERS Object owner=SH
TABLE ACCESS FULL Cost=331 Depth=2 Cardinality=1 Object name=CUSTOMERS1 Object owner=SH
ALTER TABLE customers1 ADD PRIMARY KEY (cust_id);
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=337 Depth=0 Cardinality=55500
UPDATE Depth=1 Object name=CUSTOMERS Object owner=SH
HASH JOIN Cost=337 Depth=2 Cardinality=55500
INDEX FAST FULL SCAN Cost=2 Depth=3 Cardinality=55500 Object name=SYS_C0010472 Object owner=SH
TABLE ACCESS FULL Cost=332 Depth=3 Cardinality=55500 Object name=CUSTOMERS Object owner=SH
TABLE ACCESS BY INDEX ROWID Cost=2 Depth=2 Cardinality=1 Object name=CUSTOMERS1 Object owner=SH
INDEX UNIQUE SCAN Cost=1 Depth=3 Cardinality=1 Object name=SYS_C0010472 Object owner=SH
发现cost降低了，但是customers依然是全表扫描，这是由于cust_id in ()后面的结果集，太大了
增加一个filter,让cust_id in的结果集降下来，发现
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=158 Depth=0 Cardinality=158
UPDATE Depth=1 Object name=CUSTOMERS Object owner=SH
NESTED LOOPS Cost=158 Depth=2 Cardinality=158
TABLE ACCESS BY INDEX ROWID Cost=158 Depth=3 Cardinality=159 Object name=CUSTOMERS Object owner=SH
INDEX RANGESCAN Cost=2 Depth=4 Cardinality=159 Object name=CUSTOMERS_PK Object owner=SH
INDEX UNIQUE SCAN Cost=0 Depth=3 Cardinality=1 Object name=SYS_C0010472 Object owner=SH
TABLE ACCESS BY INDEX ROWID Cost=2 Depth=2 Cardinality=1 Object name=CUSTOMERS1 Object owner=SH
INDEX UNIQUE SCAN Cost=1 Depth=3 Cardinality=1 Object name=SYS_C0010472 Objectowner=SH
要更新的表的索引被使用了，这可是一个好消息，因此 in(的查询语句)上建立索引有双重的意义
在数据仓库中，还可以利用并行机制，再提高效率
删除前面的customers1上的主键以及索引
重新建立并行索引，并以此建立主键
CREATE UNIQUE INDEX diff_pkey_ind ONcustomers1(cust_id) PARALLEL NOLOGGING;
ALTER TABLE customers1 ADD constraint pk1 PRIMARY KEY(cust_id) -- on index (diff_pkey_ind)
select * from customers1 where cust_id<5000
analyze table customers1 computestatistics
for table
for all indexed columns
for all indexes;
UPDATE customers
SET(cust_first_name, cust_street_address) =
(SELECT cust_first_name, cust_street_address
FROM customers1
WHERE customers1.cust_id = customers.cust_id)
WHERE cust_id IN(SELECT cust_id FROM customers1 where cust_id<30000)
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=335 Depth=0 Cardinality=570
UPDATE Depth=1 Object name=CUSTOMERS Object owner=SH
PXCOORDINATOR Depth=2
PXSEND QC (RANDOM) Cost=335 Depth=3 Cardinality=570 Object name=:TQ10002 Object owner=SYS
HASH JOIN Cost=335 Depth=4 Cardinality=570
PX RECEIVE Cost=2 Depth=5 Cardinality=570
PX SEND HASH Cost=2 Depth=6 Cardinality=570 Object name=:TQ10001 Object owner=SYS
PX BLOCK ITERATOR Cost=2 Depth=7 Cardinality=570
INDEX FAST FULL SCAN Cost=2 Depth=8 Cardinality=570 Object name=DIFF_PKEY_IND Object owner=SH
BUFFER SORT Depth=5
PX RECEIVE Cost=331 Depth=6 Cardinality=15933
PX SEND HASH Cost=331 Depth=7 Cardinality=15933 Object name=:TQ10000 Object owner=SYS
TABLE ACCESS FULL Cost=331 Depth=8 Cardinality=15933 Object name=CUSTOMERS Object owner=SH
TABLE ACCESS BY INDEX ROWID Cost=2 Depth=2 Cardinality=1 Object name=CUSTOMERS1 Object owner=SH
INDEX UNIQUE SCAN Cost=1 Depth=3 Cardinality=1 Object name=DIFF_PKEY_IND Object owner=SH
发现索引并行工作
还可以利用视图并行工作机制，实现并行update
UPDATE /*+ PARALLEL(cust_joinview) */
(SELECT /*+ PARALLEL(customers)PARALLEL(customers1) */
customers.cust_first_name AS c_name,
customers.cust_street_address AS c_addr,
customers1.cust_first_name AS c_newname,
customers1.cust_street_address AS c_newaddr
from customers,customers1
WHERE customers.cust_id =customers1.cust_id) cust_joinview
SET c_name = c_newname, c_addr = c_newaddr
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=95 Depth=0 Cardinality=999
UPDATE Depth=1 Object name=CUSTOMERS Object owner=SH
PXCOORDINATOR Depth=2
PX SEND QC (RANDOM) Cost=95 Depth=3 Cardinality=999 Object name=:TQ10001 Objectowner=SYS
HASH JOIN Cost=95 Depth=4 Cardinality=999
PX RECEIVE Cost=2 Depth=5 Cardinality=999
PX SEND BROADCAST Cost=2 Depth=6 Cardinality=999 Object name=:TQ10000 Object owner=SYS
PX BLOCK ITERATOR Cost=2 Depth=7 Cardinality=999
TABLE ACCESS FULL Cost=2 Depth=8 Cardinality=999 Object name=CUSTOMERS1 Object owner=SH
PX BLOCK ITERATOR Cost=92 Depth=5 Cardinality=55500
TABLE ACCESS FULL Cost=92 Depth=6 Cardinality=55500 Object name=CUSTOMERS Objectowner=SH
题外话 rownum的妙用
SELECT cust_id FROM customers1where cust_id<3000
34
表明结果集合只有34条记录
UPDATE customers
SET(cust_first_name, cust_street_address) =
(SELECT cust_first_name, cust_street_address
FROM customers1
WHERE customers1.cust_id = customers.cust_id)
WHERE cust_id IN(SELECT cust_id FROM customers1 where cust_id<3000
--and rownum<40
)
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=662 Depth=0 Cardinality=1592
UPDATE Depth=1 Object name=CUSTOMERS Object owner=SH
HASH JOIN RIGHT SEMI Cost=662 Depth=2 Cardinality=1592
TABLE ACCESS FULL Cost=331 Depth=3 Cardinality=1592 Object name=CUSTOMERS1 Object owner=SH
TABLE ACCESS FULL Cost=331 Depth=3 Cardinality=1593 Object name=CUSTOMERS Object owner=SH
TABLE ACCESS FULL Cost=331 Depth=2 Cardinality=1 Object name=CUSTOMERS1 Object owner=SH
上年的语句表明，oracle不知道有34条记录，所以走了全表扫描；
UPDATE customers
SET(cust_first_name, cust_street_address) =
(SELECT cust_first_name, cust_street_address
FROM customers1
WHERE customers1.cust_id = customers.cust_id)
WHERE cust_id IN(SELECT cust_id FROM customers1 where cust_id<3000
and rownum<40
)
但是增加了 and rownum<40 结果应该没有什么变化
但是oracle知道了只能最多返回39条记录，执行计划还是走索引了
update 与 merge
drop yanle alpha purge;
CREATE TABLE alpha (
idNUMBER(2) PRIMARY KEY,
NAME VARCHAR2(100) UNIQUE,
status CHAR(1)
);
drop yanle beta purge
CREATE TABLE beta (
networkid NUMBER(2),
NAME VARCHAR2(100),
PRIMARY KEY (NAME, networkid)
);
INSERT INTO alpha VALUES (1, 'Jim', 'A');
INSERT INTO alpha VALUES (2, 'Eric', 'A');
INSERT INTO alpha VALUES (3, 'Ryan', 'A');
COMMIT;
INSERT INTO beta VALUES (10, 'Jim');
INSERT INTO beta VALUES (10, 'Eric');
INSERT INTO beta VALUES (20, 'Ryan');
COMMIT;
UPDATE (
SELECT a.status
FROM alpha a, beta b
WHERE a.NAME = b.NAME
AND b.networkid = 10
) SET status = 'X';
--出现错误
update alpha
set status='X'
where name in (select name from beta wherenetworkid=10)
select * from alpha
merge into alpha a
using ( select b.*
from alpha a, beta b
where networkid = 10
and a.name = b.name ) b
on ( a.name = b.name )
when matched then update set status = 'X'
when not matched then insert (id) values ( null );
select * from alpha
在10g版本中
merge into alpha a
using ( select b.* from beta b where networkid = 10 ) b
on ( a.name = b.name )
when matched then update set status = 'X'
select * from alpha
------------------------------------------
update 后的例外数据处理：
----------------------------------------------------------
假定有一张表，在该表上存在若干约束，目前所有数据都遵守所有约束
如果对该表进行update后可能使某些数据违反某些约束而导致失败，下面的方法解决这样的问题
1、让所有约束实效
alter table big_table disable constraint unq;
.......
2、update big_table
......
3、建立例外表
create table exceptions(row_id rowid,
owner varchar2(30),
table_namevarchar2(30),
constraintvarchar2(30));
4、例外数据处理
alter table big_table enable constraint unq exceptions into exceptions
alter table big_table enable constraint unq
delete from big_table where rowid in ( select row_id from exceptions );
alter table big_table enable constraint unq exceptions into exceptions
在10g
create table consexcep
(
a1 varchar2(10) primary key,
a2 varchar2(11)
)
begin
DBMS_ERRLOG.CREATE_ERROR_LOG(’raises’,’errlog_conexcp’);
end;
insert into consexcep
values('001','bbb')
LOG ERRORS INTO errlog (’my_bad’) REJECTLIMIT 10;
insert into consexcep
values('002','bbbdd')
LOG ERRORS INTO errlog (’my_bad’) REJECTLIMIT 10;
insert into consexcep
values('001','ppp')
LOG ERRORS INTO errlog (’my_bad’) REJECTLIMIT 10;
SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id,sal FROM errlog;
create table consexcep
(
a1 varchar2(10) primary key,
a2 varchar2(11)
)
begin
DBMS_ERRLOG.CREATE_ERROR_LOG('CONSEXCEP','errlog_conexcp');
end;
insert into consexcep
select '001','bbb' from dual
LOG ERRORS INTO errlog_conexcp ('xxxx')REJECT LIMIT 10;
insert into consexcep
values('002','bbbdd')
LOG ERRORS INTO errlog_conexcp('xxxx') REJECT LIMIT 10;
insert into consexcep
values('001','ppp')
LOG ERRORS INTO errlog_conexcp ('xxxx') REJECT LIMIT 10;
SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, t.*FROM errlog_conexcp t;
select * from consexcep
A1 A2
--------------------
001 bbb
002 bbbdd
update consexcep
set a1='001' where a1='002'
----------------------------??????????????????????????????
update consexcep
set a1='003' where a1='002'
LOG ERRORS INTO errlog_conexcp ('update') REJECT LIMIT 10;
update consexcep
set a1='001' where a1='003'
LOG ERRORS INTO errlog_conexcp ('update') REJECT LIMIT 10;
select * from errlog_conexcp
/*
在1og sqlreference p1324有下面一段话
error_logging_clause
The error_logging_clause has the samebehavior in an UPDATE statement as it does in an
INSERT statement. Please refer to theINSERT statement error_logging_clause on
page 18-62 for more information.
*/
分析函数结果更新自身的一个字段
-------------------------------------------------------
CREATE TABLE t1
(idNUMBER(2) NOT NULL PRIMARY KEY,
grp NUMBER(2) NOT NULL,
text VARCHAR2(10) NOT NULL);
INSERT INTO t1 (id, grp, text) VALUES (1,1, 'Atlanta');
INSERT INTO t1 (id, grp, text) VALUES (2,1, 'Boston');
INSERT INTO t1 (id, grp, text) VALUES (3,1, 'Chicago');
INSERT INTO t1 (id, grp, text) VALUES (4,2, 'Dog');
INSERT INTO t1 (id, grp, text) VALUES (5,2, 'Eagle');
INSERT INTO t1 (id, grp, text) VALUES (6,2, 'Fish');
现在想在表中增加一列，希望以grp分组后，再按照text排序
达到以下效果
ID GRP TEXT ORD
----------------------------------------------------------
1 1 Atlanta 1
2 1 Boston 2
3 1 Chicago 3
4 2 Dog 1
5 2 Eagle 2
6 2 Fish 3
我们从分析函数知道，要实现一上操作，可以使用以下分析函数
SELECT t1.*, ROW_NUMBER() OVER (PARTITION BY grp ORDER BY text) AS ord2 from t1 order by ord2;
增加一列
ALTER TABLE t1
ADD ord NUMBER(2);
update t1 set t1.ord=ROW_NUMBER() OVER (PARTITION BY grp ORDER BY text
--ora-30438错误
改写
UPDATE
(SELECT a.id, a.ord, b.ord2 FROM t1 a, (SELECT id, ROW_NUMBER() OVER (PARTITION BY grp ORDERBY text) AS ord2 FROM t1) b
WHERE a.id = b.id)
SET ord = ord2
当执行的时候，有以下错误
ORA-01779: cannot modify a column whichmaps to a non key-preserved table
update t1 set t1.ord=
(
select v.ord3 from
(
SELECT t1.id, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY text) AS ord3 from t1 order by ord3
) v
where t1.id=v.id
)
成功
merge into t1
using ( select rowid, row_number() over (partition by grp order by text)rn
from t1 ) t2
on (t1.rowid = t2.rowid)
when matched thenupdate set ord = rn
--when not matched then insert (id) values (null)
成功
-------------------------------------------------
用一个表的多个字段更新另一个表的多个字段
drop table t1purge;
drop table t2purge;
create table t1 ( x int, y int, z int );
create table t2 ( a int, b int,c int );
insert into t1 values(1,2,3);
insert into t1 values(4,5,6);
insert into t2 values(4,15,16);
insert into t2 values(1,12,13);
insert into t2 values(3,22,23);
select * from t1
select * from t2
update ( selectt1.x,t1.y,t1.z,t2.a,t2.b,t2.c from t1,t2 where t1.x = t2.a )
set y = b,
z=c
ORA-01779: cannot modify a column whichmaps to a non key-preserved table
alter table t2 add constraint t2_pk primarykey(a);
update ( selectt1.x,t1.y,t1.z,t2.a,t2.b,t2.c from t1,t2 where t1.x = t2.a )
set y = b,
z=c
这种情况下，需要的信息来源的表上有唯一性约束
----------------------------------------------
rollback;
这一次测试用t1表信息更新t2， 同时用用t2表信息更新t1 ,会出现什么情况
alter table t1 add constraint t1_pk primarykey(x);
select * from t1
update ( selectt1.x,t1.y,t1.z,t2.a,t2.b,t2.c from t1,t2 where t1.x = t2.a )
set y = b,
c=z
出现 ora-01776错误，无法通过联结试图同时更新2个表；
题外话: 多表多字段同时更新的实现
drop table t1purge;
drop table t2purge;
drop table t3 purge;
create table t1 ( x int primary key, a int);
create table t2 ( y int primary key, b int);
create table t3 ( z int primary key, c int);
create or replace view v
as
select x, a, b, c
from t1, t2, t3
where x = y
and x = z
update v set a = 1 where x = 1;
update v set b = 1 where x = 1;
update v set c = 1 where x = 1;
update v set a = 1, c = 1 where x = 1;
update v set a = 1, c = 1 where x = 1
*
ERROR at line 1:
ORA-01776: cannot modify more than one basetable through a join view
create or replace trigger v_Itrigger
INSTEAD OF INSERT ON V
begin
insert into t1 values ( :new.x, :new.a );
insert into t2 values ( :new.x, :new.b );
insert into t3 values ( :new.x, :new.c );
end;
/
create or replace trigger v_Utrigger
INSTEAD OF UPDATE ON V
begin
update t1 set a = :new.a, x = :new.x where x = :old.x;
update t2 set b = :new.b, y = :new.x where y = :old.x;
update t3 set c = :new.c, z = :new.x where z = :old.x;
end;
/
Trigger created.
update v set a = 1, c = 1 where x = 1;
insert into v values ( 0, 1, 2, 3 );
select * from t1;
X A
---------- ----------
0 1
select * from t2;
Y B
---------- ----------
0 2
select * from t3;
Z C
---------- ----------
0 3
update v set a = 2, c = 3 where x = 1;
现在我们又可以，对多表多字段update了
效率比较
触发器的重新启动对象
create table t_trigger (a int,b int);
insert into t_trigger values(1,1);
commit;
create or replace trigger t_tr
before update on t_trigger
for each row
declare
begin
dbms_output.put_line('old a='||:old.a);
dbms_output.put_line('old a='||:old.a);
dbms_output.put_line('old b='||:old.b);
dbms_output.put_line('old b='||:old.b);
end t_tr;
session1
updatet_trigger set b=b+1;
old a=1
old a=1
new b=2
new b=2
session2
update t_trigger set a=a+1;
被阻塞
session1
commit;
session2
old a=1
old a=1
new b=1
new b=1
old a=1
old a=1
new b=2
new b=2
为什么？触发器被触发2次
如果触发器中引用:new :old的时候，在before的情况下，先找出数据块（一致性读取）,
如果修改trigger
create or replace trigger t_tr
after update on t_trigger
for each row
declare
begin
dbms_output.put_line('old a='||:old.a);
dbms_output.put_line('old a='||:old.a);
dbms_output.put_line('old b='||:old.b);
dbms_output.put_line('old b='||:old.b);
end t_tr;
session1
updatet_trigger set b=b+1;
old a=1
old a=1
new b=2
new b=2
session2
update t_trigger set a=a+1;
被阻塞
session1
commit;
session2
old a=1
old a=1
new b=2
new b=2
原创文章，如果转载，请标注作者：田文 优快云地址：http://blog.youkuaiyun.com/tiwen818
扫一扫
2561
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
