今天的oracle面试题

Question

1、有一张表A,属性有Id ,name ,age这张表的数据量达到100万，要把所有的age都更新为age+1，请问怎么实现最高效？

Answer 1

高效？坐等大侠
我就会这样update table_name set num=num+1 ;

Answer 2

你可以回答，表设计不好，age会随着时间变化而变化，改用birthday，用date类型吧

Answer 3

这个是面试题，你就不要考虑设计问题了，就吧age当成个普通的int类型的列，把他全部加1。

Answer 4

100万也应该很快吧update table_name set num=num+1

Answer 5

如果有索引，先把索引删除后再update最后把索引重新创建一下因为索引对update影响很大

Answer 6

--在允许的情况下可以
1、create table newA as select id,name,age+1 age from A;
2、drop table A;
3、rename newA to A;

Answer 7

为什么这样很低效？
update table_name set num=num+1

Answer 8

设置自动提交行数，会快很多
set autocommit 1000;
update table_name set age=age+1;

Answer 9

dml 操作的缺点：
1、因为一次性修改的数据太多，而导致回滚段、临时表空间或者数据表空间爆满而失败，结果回滚又需要很长时间。没有指定分段提交的imp（导入)
操作也会出现这种情况。
2、因为该表的DML很频繁 ，如果执行的维护操作阻塞了这些DML，可能导致数据库的锁与阻塞很多，影响前台应用的正常使用。
3、因为该表利用MV或Trigger 做复制，瞬间几百万的更新可能会导致日志表爆满，甚至复制与同步出错。
|||||||||||||||
建议
以上的过程用到了前一个案例的方法，用到了如下的几项技术：
  1>循环更新，及时提交，减少对系统的压力与冲击
  2>采用跟新标志，可以断点继续。如以上的案例中，跟新成功，标志位则跟新为1，这样，
  通过查询临时表，就可以知道有多少数据被跟新了。同时，如果跟新是因为意外的情况，
  中途终止了，因为没有根系的标志都是0，很容易断点跟新，而不用从头开始新的更新。||||||||||
for example
案例2：
假定有一个1000W级别的数据表，如my_test，需要根据这个表的user_id汇总记录，跟新到my_users 表中，关联条件是 
my_test.user_id=my_users.id。汇总完成以后的记录可能是100W，my_users 没有MV或触发器在上面，但是又比较频繁的DML。
也就是说，跟新的记录数跟案例1一样，只有100W条记录，但是数据的获取逻辑复杂了很多，需要从别的表汇总以后获得。update my_users u set user_cnt=
(select user_cnt from
  (select user_id,count(*) user_cnt from my_test group by user_id) tmp
    where tmp.user_id=u.id)
  where exists
  (select null from my_test t where u.id=t.user_id);
以上的语句肯定是能执行的，但是效率不能保证，因为在关联跟新中还需要汇总，这样不仅耗费了大量的时间，而且，这个跟新与汇总操作
将耗费大量的Undo与Temp空间，可能导致Undo以及Temp都不足。
所以在这种方案中，建议先建立一个汇总表，把需要跟新的数据汇总出来，如:
SQL>create table tmp_test as select user_id,count(*) user_cnt ,0 flags from mytest group by user_id;
但是，汇总表也有一个问题，就是汇总表创建的时间点之后的跟新不能反映到跟新中去。不过，以上的一个Update语句一样不能解决这样的
问题。这个问题属于业务问题，这里假定my_test 可以是静态的表，或者新增加的数据可以跟新时间戳以后再同步。以上创建表的方式可以采用Nologging 与Append 的方式写入，这样的方式可以提高临时表的创建速度。在表的创建过程中，需要关注Undo
与Temp的使用情况。
监控undo 空间情况:
SQL>  select tablespace_name ,
  2   round(sum(bytes)/(1024*1024),2) free_space
  3   from dba_free_space
  4   where tablespace_name='UNDOTBS1'
  5   group by tablespace_name;TABLESPACE_NAME                FREE_SPACE
------------------------------ ----------
UNDOTBS1                           167.44
监控临时段的使用情况:SQL> select sum(nvl(blocks,0))*8/1024 Msize from v$sort_usage;     MSIZE
----------
临时表创建之后，我们就可以根据临时表来跟新 my_users 了，考虑到 my_users 上海由dml业务存在，这里也必须写一个循环来操作：
declare 
   row_num number :=0;
   begin 
   for c_user in (select user_id,user_cnt from tmp_test t where flags = 0) loop
   --跟新目标表
   update my_users t set t.user_cnt = c_user.user_cnt where id=c_user.user_id;
   --跟新临时表的标记位
  update tmp_test f set f.flags = 1 where user_id=c_user.user_id;
  --跟新临时表的标记位
  update tmp_test f set f.flags = 1 where user_id = c_user.user_id;
  --1000条提交一次
  if mod(row_num,1000)=0 then
  commit;
  end if;
  end loop;
  commit ;
  end;

Answer 10

跟我想的一样，表复制的速度比update的速度要快不少。

Answer 11

+1
设置自动提交行数，会快很多
set autocommit 1000;
update table_name set age=age+1;
+1

Answer 12

大批量更新会写很多UNDO，这个对资源消耗较多

Answer 13

批量更新速度会很快！
以下是我的：oracle高效更新测试代码--1.创建测试表，并插入200万条记录
create table tb
as 
select rownum id, '测试'||rownum name
  from dual
 connect by rownum<=2000000;
 
--2.删除表数据
--(1)直接使用delete删除
SQL> set timing on;
SQL> update tb set name = '测试1';
 
2000000 rows updated
 
Executed in 30.641 seconds--(2)使用存储过程删除
--创建存储过程
create or replace procedure forall_update_test
/**
 * PURPOSE : oracle中高效删除的测试
 * REVISION>
   Version      Date        Author        Description
   --------     --------    ----------    -------------------
   1.0          20100511    huangbiquan   1. create the procedure
*/
is
    type      rid_arr  is table of rowid index by binary_integer;
    v_rowid   rid_arr;
begin
    select rowid
      bulk collect
      into v_rowid
      from tb
     where rownum <= 10000;    forall i in 1..v_rowid.count
        update tb set name = '测试2' where rowid = v_rowid(i);
        dbms_output.put_line('update rows:'||v_rowid.count);end;--使用存储过程删除
SQL> exec dbms_output.enable(999999999999999999999999999999);
 
PL/SQL procedure successfully completedSQL> set serveroutput on;
SQL> set timing on;declare
    v_total   pls_integer;
begin
    select count(*)
      into v_total
      from tb;    while(v_total > 0) loop
        execute immediate 'begin forall_update_test; end;';
        commit;
        v_total := v_total - 10000;    end loop;
end;SQL> declare
  2      v_total   pls_integer;
  3  begin
  4      select count(*)
  5        into v_total
  6        from tb;
  7  
  8      while(v_total > 0) loop
  9          execute immediate 'begin forall_update_test; end;';
 10          commit;
 11          v_total := v_total - 10000;
 12  
 13      end loop;
 14  end;
 15  /
 
update rows:10000
update rows:10000
update rows:10000
......
update rows:10000
 
PL/SQL procedure successfully completed
 
Executed in 14.86 seconds
 
注意
update rows:10000
update rows:10000
update rows:10000
update rows:10000update rows:10000
update rows:10000
update rows:10000
 
PL/SQL procedure successfully completed
 
Executed in 311 seconds

Answer 14

相关注释是错误的！
  那是我之前做的oracle中高效删除实验的注释！