主页 / dba / 问题 / 9372
Accepted
Jack Douglas
Asked: 2011-12-20 05:32:12 +0800 CST

物联网是否会为删除的每个块生成明显更多的撤消？

  • 772

物联网是一种类似索引的结构。删除大的连续数据块时，与从堆中进行类似的连续删除相比，会生成多少撤销？

oracle rollback

1 个回答

  1. Best Answer

    不，无论是 IOT 还是堆，这种类型的删除都会产生大致相同数量的撤消。

    相比之下，插入 IOT 会产生更多的撤销。

    堆：

    drop table foo;
create table foo( id integer not null, 
                  val char(100) default 'A' not null);
--
insert into foo(id) select level from dual connect by level<100000;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK              
---------------------- 
149                    
*/
--
analyze table foo compute statistics;
--
select blocks from all_tab_statistics where table_name='FOO';
/*
BLOCKS                 
---------------------- 
3274                   
*/
--
delete from foo;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK              
---------------------- 
5264                   
*/

    物联网：

    drop table foo;
create table foo( id integer not null primary key, 
                  val char(100) default 'A' not null) organization index;
--
insert into foo(id) select level from dual connect by level<100000;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK              
---------------------- 
2030                   
*/
--
analyze table foo compute statistics;
select leaf_blocks from all_ind_statistics where table_name='FOO';
/*
LEAF_BLOCKS            
---------------------- 
2942                   
*/
--
delete from foo;
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK              
---------------------- 
4741                   
*/
    • 2

相关问题