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

最新推荐文章于 2022-09-10 12:43:36 发布

深圳gg

最新推荐文章于 2022-09-10 12:43:36 发布

阅读量1.5k

点赞数

分类专栏： oracle内部原理文章标签： vector object sql table

本文链接：https://blog.csdn.net/stevendbaguo/article/details/8044290

版权

oracle内部原理专栏收录该内容

27 篇文章 0 订阅

订阅专栏

当表的索引列被更改，索引会生成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