索引被更改时会生成undo,redo吗

来源：互联网发布：韩顺平js 编辑：程序博客网时间：2024/04/30 14:14

当表的索引列被更改，索引会生成undo,redo。下列就是实验：

SQL> create table bigtab as select * from dba_objects;

SQL> create index ind_object_id on BIGTAB (object_id);

SQL> select v$statname.name,value
2 from v$mystat, v$statname
3 where v$mystat.statistic# =v$statname.statistic#
4 and (v$statname.name ='redo size' or v$statname.name ='undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          46265464
undo change vector size                               14142540

SQL> update bigtab set object_id=200;

49198 rows updated

SQL> commit;
SQL> select v$statname.name,value
2 from v$mystat, v$statname
3 where v$mystat.statistic# =v$statname.statistic#
4 and (v$statname.name ='redo size' or v$statname.name ='undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          58150008
undo change vector size                              18286900

SQL> select (18286900-14142540)/1024/1024 undo,(58150008-46265464)/1024/1024 redo from dual;

UNDO REDO
---------- ----------
3.95236968 11.3339843

删除索引

SQL> update bigtab set object_id=200;

49198 rows updated

SQL>
SQL> select v$statname.name,value
2 from v$mystat, v$statname
3 where v$mystat.statistic# =v$statname.statistic#
4 and (v$statname.name ='redo size' or v$statname.name ='undo change vector size');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          62470084
undo change vector size                               20325404

SQL> select (20325404-18286900)/1024/1024 undo,(62470084-58150008)/1024/1024 redo from dual;

UNDO REDO
----------- ---------
1.94406890 4.11994552