大表更新如何调整性能

Question

好久不提问了,大家这次帮我大表更新的性能问题最简单的更新一个列,供测试对比时间用的:13:37:43 SQL> update t_employees set headcount = 2;70236 rows updated.Elapsed: 00:00:42.45Execution Plan
----------------------------------------------------------
Plan hash value: 926905594----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |             | 70236 |   137K|  1015   (1)| 00:00:13 |
|   1 |  UPDATE            | T_EMPLOYEES |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_EMPLOYEES | 70236 |   137K|  1015   (1)| 00:00:13 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         73  recursive calls
      87397  db block gets
      85612  consistent gets
       9698  physical reads
   26424568  redo size
        555  bytes sent via SQL*Net to client
        560  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      70236  rows processed
这个42秒,慢了点然后是需要调整的sql,分两部分,一部分是插入临时表t_emp_headcount,另一部分是更新目标表t_employees:
插入不慢,更新很慢,不能容忍:10:47:15 SQL> INSERT INTO T_EMP_HEADCOUNT
10:47:22   2    SELECT MAX(A.LV)-1 HEADCOUNT, A.ROOTEMP FROM
10:47:22   3      ( SELECT LEVEL LV, CONNECT_BY_ROOT CORPID ROOTEMP
10:47:22   4        FROM T_EMPLOYEES
10:47:22   5        CONNECT BY NOCYCLE PRIOR CORPID = MGR_CORPID) A
10:47:22   6       GROUP BY A.ROOTEMP;70236 rows created.Elapsed: 00:00:08.90Execution Plan
----------------------------------------------------------
Plan hash value: 1388359246----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |             | 70236 |  1714K|  1017   (1)| 00:00:13 |
|   1 |  HASH GROUP BY                 |             | 70236 |  1714K|  1017   (1)| 00:00:13 |
|   2 |   VIEW                         |             | 70236 |  1714K|  1013   (1)| 00:00:13 |
|*  3 |    CONNECT BY WITHOUT FILTERING|             |       |       |            |          |
|   4 |     TABLE ACCESS FULL          | T_EMPLOYEES | 70236 |  1097K|  1013   (1)| 00:00:13 |
----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   3 - access("MGR_CORPID"=PRIOR "CORPID")
Statistics
----------------------------------------------------------
         46  recursive calls
       1010  db block gets
       4808  consistent gets
        285  physical reads
     200924  redo size
        750  bytes sent via SQL*Net to client
       1127  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      70236  rows processed10:47:33 SQL> UPDATE T_EMPLOYEES SET HEADCOUNT =
10:47:44   2  ( SELECT T_EMP_HEADCOUNT.HEADCOUNT
10:47:44   3        FROM T_EMP_HEADCOUNT
10:47:44   4        WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP
10:47:45   5  );70236 rows updated.Elapsed: 00:13:27.44Execution Plan
----------------------------------------------------------
Plan hash value: 2707919579--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                 | 70236 |   685K|  1015   (1)| 00:00:13 |
|   1 |  UPDATE            | T_EMPLOYEES     |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_EMPLOYEES     | 70236 |   685K|  1015   (1)| 00:00:13 |
|*  3 |   TABLE ACCESS FULL| T_EMP_HEADCOUNT |     1 |    25 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   3 - filter("T_EMP_HEADCOUNT"."ROOTEMP"=:B1)
Statistics
----------------------------------------------------------
         81  recursive calls
     125523  db block gets
   15640727  consistent gets
         31  physical reads
   32018776  redo size
        756  bytes sent via SQL*Net to client
       1053  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      70236  rows processed
我oracle是10.2.0.2.0, 
操作系统是solaris10盼高手指点

Answer 1

headcount上建索引没有效果,因为数据只有25种可能
使用位图索引好像也不合适, 表经常在更新好像consistent gets太多了

Answer 2

t_employees表的记录和t_emp_headcount的记录都在7W左右,
前一个是普通表,有60多个列,
后一个是临时表,只有两个列

Answer 3

UPDATE T_EMPLOYEES SET HEADCOUNT =
 ( SELECT T_EMP_HEADCOUNT.HEADCOUNT
      FROM T_EMP_HEADCOUNT
      WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP） where exists select 1 from T_EMP_HEADCOUNT,T_EMPLOYEES where T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP

Answer 4

以前是没有临时表的,
插入和更新在一个sql里做,
这个一共执行了75分钟后来才建的这个临时表最简单的update都需要42.45秒, 这个大家能不能想下优化的办法?

Answer 5

update t_employees set headcount = 2;
这你句都不能快的话,那没有其它相同功能的语句能更快!索引用不上,analyze table statistics再试试
也有可能象你所说的需要update的数据太多了，占到表总记录一定比例．

Answer 6

UPDATE T_EMPLOYEES SET HEADCOUNT =
 ( SELECT T_EMP_HEADCOUNT.HEADCOUNT
      FROM T_EMP_HEADCOUNT
      WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP) 
      where exists
      ( select 1 from T_EMP_HEADCOUNT,T_EMPLOYEES where T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP);忘了说, exists我这里用不上,
因为t_employees和t_emp_headcount表里的corpid是一一对应的,每个都找得到但还是谢谢你的回复

Answer 7

表已经用dbms_stats.gather_table_stats分析过了我觉得简单的update能在一分钟内完成,
为什么按条件update会要13分钟,因为t_emp_headcount是小表,还是临时表啊大家再帮想想办法,谢谢了

Answer 8

       采用oracle Stream吧!

Answer 9

   很简单的问题呀。简单的update语句相当与逐行执行，所以他相当于是搜索条件最好的情况
   带条件的他就得对你的表整表扫描后在执行更新数据。如果记录有2万条，恰好你找的记录在
最后一条你说速度会慢不？这就是一个时间复杂度问题了。。

Answer 10

感觉你的sql有问题，你试试下面：UPDATE T_EMPLOYEES SET HEADCOUNT =
 (SELECT T_EMP_HEADCOUNT.HEADCOUNT
        FROM T_EMP_HEADCOUNT
       WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP);---你上面这句改为:UPDATE T_EMPLOYEES a SET HEADCOUNT =
 (SELECT T_EMP_HEADCOUNT.HEADCOUNT
        FROM T_EMP_HEADCOUNT
       WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP)
where exists(select 1 from T_EMP_HEADCOUNT where ROOTEMP = a.CORPID);

Answer 11

--虚拟一张表来进行操作，在数据量大的情况下效率还可以UPDATE (SELECT T_EMP_HEADCOUNT.HEADCOUNT
          FROM T_EMP_HEADCOUNT,T_EMPLOYEES
        WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP) a SET HEADCOUNT =a.HEADCOUNT

Answer 12

还可以通过插入速度比更新速度快，以及简单更新比条件更新快的特点创建表来操作。－－改正下
UPDATE (SELECT [需要的列名..T_EMP_HEADCOUNT.HEADCOUNT,T_EMPLOYEES.HEADCOUNT ]
          FROM T_EMP_HEADCOUNT,T_EMPLOYEES
        WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP) a SET a.T_EMP_HEADCOUNT.HEADCOUNT =a.T_EMPLOYEES.HEADCOUNT －－方法二
create table ttt as SELECT  [需要的列名..T_EMP_HEADCOUNT.HEADCOUNT,T_EMPLOYEES.HEADCOUNT ] FROM T_EMP_HEADCOUNT WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP;
delete from T_EMPLOYEES WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP;
update ttt set ttt.T_EMP_HEADCOUNT.HEADCOUNT=ttt.T_EMPLOYEES.HEADCOUNT;
insert into T_EMPLOYEES as select * from ttt;

Answer 13

我这个update的sql没加exists子句看起来确实比较别扭,
但是更新功能确实都实现了并且验证过了但是不知道没有exists会不会引起性能的问题,
我现在加了正在测, sql暂时还没跑完

Answer 14

16:05:34 SQL> UPDATE T_EMPLOYEES EMP SET HEADCOUNT =
           2  ( SELECT T_EMP_HEADCOUNT.HEADCOUNT
           3        FROM T_EMP_HEADCOUNT
           4        WHERE EMP.CORPID = T_EMP_HEADCOUNT.ROOTEMP
           5  )
           6  WHERE EXISTS
           7  (
           8    SELECT 1 FROM T_EMP_HEADCOUNT WHERE ROOTEMP = EMP.CORPID
           9  );
 
 
Executed in 742.532 seconds
性能差不多

Answer 15

 WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP
T_EMP_HEADCOUNT.rootemp上有索引吗？合适建索引吗？1)你的第一个sql执行要40秒，但只有7W条记录，明显不合理。
   要不是你的机器太差，要不就是有HWM的问题。
  sql>alter table T_EMPLOYEES move;
  然后再试试，会不会快点2)你最原始的sql语句（不用临时表的）怎么写的？

Answer 16

我的t_emp_headcount是临时表,on commit delete rows的那种, 建索引好像不合适吧?
而且全表扫描并不慢,代价只有21) 
t_employees的update要42秒我也觉得能优化,那麻烦你再提点建议呗?sql>alter table T_EMPLOYEES move;你的意思是让我转移下表空间或者是回缩高水位吗? 
这个表的数据组织应该是没有问题的,刚imp进去的测试的2) 我已经放弃原先的那个计划了
谢谢你的回复

Answer 17

T_EMP_HEADCOUNT 看你的rootemp有多少个不同的值，如果有很多不同的值，我认为还是可以建上。我建议先把这个表先改成一个普通的表，建索引，分析，再试试你的sql.从你的sql上来看，确实没什么好的优化了。T_EMPLOYEES应该有主键的吧，可以试试这种写法（可以看看他的执行计划）：update
(
    SELECT A.headcount a, T_EMP_HEADCOUNT.HEADCOUNT b 
      FROM T_EMPLOYEES A, T_EMP_HEADCOUNT
     WHERE A.CORPID = T_EMP_HEADCOUNT.ROOTEMP
)
set a=b;另外就是我猜测的高水位问题。 alter table t_employees move 是回缩高水位。 不知道这样之后你的sql执行情况是怎么样的。

Answer 18

作了两点修改, 效率有提高:1. 加了exists子句
2. 对t_emp_headcount建了索引诚挚感谢帮助我的同志们,
谢谢你们