Prompt ================================
prompt
prompt Test 1.
prompt Rebuild before adding extra data
prompt
prompt ================================
prompt
prompt Test 1.
prompt Rebuild before adding extra data
prompt
prompt ================================
sys@SMART>create table t1(n1 number(38));
Table created.
sys@SMART>create index i1 on t1(n1);
Index created.
PL/SQL procedure successfully completed.
sys@SMART>
sys@SMART>begin
2 for i in 1..400000 loop
3 insert into t1 values(
4 trunc(power(10,14) * dbms_random.value)
5 );
6 commit;
7 end loop;
8 end;
9 .
sys@SMART>/
sys@SMART>begin
2 for i in 1..400000 loop
3 insert into t1 values(
4 trunc(power(10,14) * dbms_random.value)
5 );
6 commit;
7 end loop;
8 end;
9 .
sys@SMART>/
PL/SQL procedure successfully completed.
sys@SMART>analyze index i1 validate structure;
Index analyzed.
sys@SMART>sys@SMART>select
2 'Leaf blocks before rebuild: ' || lf_blks leaf_blocks
3 from index_stats;
2 'Leaf blocks before rebuild: ' || lf_blks leaf_blocks
3 from index_stats;
Leaf
Blks
--------------------------------------------------------------------
Leaf blocks before rebuild: 1472
Blks
--------------------------------------------------------------------
Leaf blocks before rebuild: 1472
1 row selected.
sys@SMART>alter index i1 rebuild pctfree 10;
Index altered.
sys@SMART>sys@SMART>analyze index i1 validate structure;
Index analyzed.
sys@SMART>sys@SMART>select
2 'Leaf blocks immediately after rebuild: ' || lf_blks leaf_blocks
3 from index_stats;
2 'Leaf blocks immediately after rebuild: ' || lf_blks leaf_blocks
3 from index_stats;
Leaf
Blks
-------------------------------------------------------------------------------
Leaf blocks immediately after rebuild: 1114
Blks
-------------------------------------------------------------------------------
Leaf blocks immediately after rebuild: 1114
1 row selected.
sys@SMART>
sys@SMART>begin
2 for i in 1..100000 loop
3 insert into t1 values(
4 trunc(power(10,14) * dbms_random.value)
5 );
6 commit;
7 end loop;
8 end;
9 .
sys@SMART>/
PL/SQL procedure successfully completed.
Index analyzed.
sys@SMART>sys@SMART>select
2 'Leaf blocks after further processing: ' || lf_blks leaf_blocks
3 from index_stats;
2 'Leaf blocks after further processing: ' || lf_blks leaf_blocks
3 from index_stats;
Leaf
Blks
------------------------------------------------------------------------------
Leaf blocks after further processing: 2227
Blks
------------------------------------------------------------------------------
Leaf blocks after further processing: 2227
1 row selected.
prompt
Prompt ================================
prompt
prompt Test 2:
prompt Without rebuilding.
prompt
prompt ================================
sys@SMART>
sys@SMART>drop table t1;
Table dropped.
sys@SMART>sys@SMART>begin
2 begin execute immediate 'purge recyclebin';
3 exception when others then null;
4 end;
5 end;
6 /
2 begin execute immediate 'purge recyclebin';
3 exception when others then null;
4 end;
5 end;
6 /
PL/SQL procedure successfully completed.
Table created.
sys@SMART>create index i1 on t1(n1);
Index created.
PL/SQL procedure successfully completed.
sys@SMART>
sys@SMART>begin
2 for i in 1..400000 loop
3 insert into t1 values(
4 trunc(power(10,14) * dbms_random.value)
5 );
6 commit;
7 end loop;
8 end;
9 .
sys@SMART>/
sys@SMART>begin
2 for i in 1..400000 loop
3 insert into t1 values(
4 trunc(power(10,14) * dbms_random.value)
5 );
6 commit;
7 end loop;
8 end;
9 .
sys@SMART>/
PL/SQL procedure successfully completed.
sys@SMART>sys@SMART>begin
2 for i in 1..100000 loop
3 insert into t1 values(
4 trunc(power(10,14) * dbms_random.value)
5 );
6 commit;
7 end loop;
8 end;
9 .
sys@SMART>/
2 for i in 1..100000 loop
3 insert into t1 values(
4 trunc(power(10,14) * dbms_random.value)
5 );
6 commit;
7 end loop;
8 end;
9 .
sys@SMART>/
PL/SQL procedure successfully completed.
sys@SMART>analyze index i1 validate structure;
Index analyzed.
sys@SMART>select
2 'Leaf blocks with no intermediate rebuild: ' || lf_blks leaf_blocks
3 from index_stats;
2 'Leaf blocks with no intermediate rebuild: ' || lf_blks leaf_blocks
3 from index_stats;
Leaf
Blks
----------------------------------------------------------------------------------
Leaf blocks with no intermediate rebuild: 1777
Blks
----------------------------------------------------------------------------------
Leaf blocks with no intermediate rebuild: 1777
1 row selected.
sys@SMART>alter index i1 rebuild pctfree 10;
Index altered.
sys@SMART>analyze index i1 validate structure;
Index analyzed.
Leaf
Blks
--------------------------------------------------------------------
Leaf blocks at end of test: 1393
Blks
--------------------------------------------------------------------
Leaf blocks at end of test: 1393
1 row selected.
实验的结果似乎违背的直觉。
第一个测试案例里：
1)我们先进行了一次数据的集中插入。完成后，索引的大小是1472个block.这个操作非常接近我们现实的情况。
2)然后我们重建了索引，索引大小降低到1114。
3)我们重新往表里插入了一批数据。这个时候索引的大小是2227。
第二个测试案例里：
1)跟案例一一样，进行了一次数据的集中插入。
2)不重建索引，直接再次进行一次数据插入，同案例一里面的步骤3.这个时候索引的大小是1777。竟然比案例一里的2227低。
1)跟案例一一样，进行了一次数据的集中插入。
2)不重建索引，直接再次进行一次数据插入，同案例一里面的步骤3.这个时候索引的大小是1777。竟然比案例一里的2227低。
这个违背直觉的实验，我想主要是因为索引重建后，导致索引的分裂比重建前更加的频繁。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-714000/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
in list查询计算cost
请登录后发表评论
登录
全部评论
