Linux安全网 - Linux操作系统_Linux 命令_Linux教程_Linux黑客

绿色网站无广告
会员投稿 投稿指南 本期推荐:
搜索:
您的位置: Linux安全网 > Linux编程 > 数据库管理 > » 正文

UPDATE 问题

来源: Tiwen818 分享至:

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

 

 

 原创文章,如果转载,请标注作者:田文  CSDN地址:http://blog.csdn.net/tiwen818

 


Tags:
分享至:
最新图文资讯
1 2 3 4 5 6
验证码:点击我更换图片 理智评论文明上网,拒绝恶意谩骂 用户名:
关于我们 - 联系我们 - 广告服务 - 友情链接 - 网站地图 - 版权声明 - 发展历史