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