不同基值的列所建的索引高度和枝节点数
基值越低，索引高度越高，枝节点越多，叶子节点也多，而且叶子节点数多于联合索引（以该基值低字段开头，和索引简化存储的数据有关）
select * from test_a;
select count(0) from test_a;
alter table test_a add id number;
alter table test_a add name varchar2(20);
create index i_test_a_id on test_a(id);
create index i_test_a_name on test_a(name);
create index i_test_a_idname on test_a(id,name);
create index i_test_a_nameid on test_a(name,id);
-- Created on 2013-7-10 by ZHONGYANMING
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 1..10000 loop
insert into test_a values(j,'abcdefghijklmn');
commit;
end loop;
end;
select s.sid, n.name, s.value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid in (select sid from v$mystat)
and value > 0
and n.name like '%split%';
analyze index i_test_a_id validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
analyze index i_test_a_name validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
analyze index i_test_a_idname validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
analyze index i_test_a_nameid validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
-- Created on 2013-7-10 by ZHONGYANMING
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 1..100000 loop
insert into test_a values(j,'abcdefghijklmn');
commit;
end loop;
end;
select s.sid, n.name, s.value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid in (select sid from v$mystat)
and value > 0
and n.name like '%split%';
analyze index i_test_a_id validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
analyze index i_test_a_name validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
analyze index i_test_a_idname validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
analyze index i_test_a_nameid validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
-- Created on 2013-7-10 by ZHONGYANMING
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 1..1000000 loop
insert into test_a values(j,'abcdefghijklmn');
commit;
end loop;
end;
select s.sid, n.name, s.value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid in (select sid from v$mystat)
and value > 0
and n.name like '%split%';
analyze index i_test_a_id validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
analyze index i_test_a_name validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
analyze index i_test_a_idname validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
analyze index i_test_a_nameid validate structure;
select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
SQL> analyze index i_test_a_id validate structure;
Index analyzed
Executed in 0 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_ID 1 8 0 1
Executed in 0.031 seconds
SQL> analyze index i_test_a_name validate structure;
Index analyzed
Executed in 0 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_NAME 1 8 0 1
Executed in 0.031 seconds
SQL> analyze index i_test_a_idname validate structure;
Index analyzed
Executed in 0 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_IDNAME 1 8 0 1
Executed in 0.031 seconds
SQL> analyze index i_test_a_nameid validate structure;
Index analyzed
Executed in 0.016 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_NAMEID 1 8 0 1
Executed in 0.032 seconds
SQL>
SQL> -- Created on 2013-7-10 by ZHONGYANMING
SQL> declare
2 -- Local variables here
3 i integer;
4 begin
5 -- Test statements here
6 for j in 1..10000 loop
7 insert into test_a values(j,'abcdefghijklmn');
8 commit;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed
Executed in 1.563 seconds
SQL>
SQL> select s.sid, n.name, s.value
2 from v$sesstat s, v$statname n
3 where s.statistic# = n.statistic#
4 and sid in (select sid from v$mystat)
5 and value > 0
6 and n.name like '%split%';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
3119 leaf node splits 135
3119 leaf node 90-10 splits 115
Executed in 0.047 seconds
SQL>
SQL> analyze index i_test_a_id validate structure;
Index analyzed
Executed in 0.032 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_ID 2 24 1 19
Executed in 0.032 seconds
SQL> analyze index i_test_a_name validate structure;
Index analyzed
Executed in 0.016 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_NAME 2 56 1 44
Executed in 0.016 seconds
SQL> analyze index i_test_a_idname validate structure;
Index analyzed
Executed in 0.032 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_IDNAME 2 48 1 38
Executed in 0.031 seconds
SQL> analyze index i_test_a_nameid validate structure;
Index analyzed
Executed in 0.016 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_NAMEID 2 48 1 38
Executed in 0.031 seconds
SQL>
SQL> -- Created on 2013-7-10 by ZHONGYANMING
SQL> declare
2 -- Local variables here
3 i integer;
4 begin
5 -- Test statements here
6 for j in 1..100000 loop
7 insert into test_a values(j,'abcdefghijklmn');
8 commit;
9 end loop;
10 end;
11 /
SQL>
PL/SQL procedure successfully completed
Executed in 36.297 seconds
SQL>
SQL> select s.sid, n.name, s.value
2 from v$sesstat s, v$statname n
3 where s.statistic# = n.statistic#
4 and sid in (select sid from v$mystat)
5 and value > 0
6 and n.name like '%split%';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
3119 leaf node splits 1745
3119 leaf node 90-10 splits 1085
3119 branch node splits 3
Executed in 0.031 seconds
SQL> analyze index i_test_a_id validate structure;
Index analyzed
Executed in 0.094 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_ID 2 256 1 236
Executed in 0.016 seconds
SQL> analyze index i_test_a_name validate structure;
Index analyzed
Executed in 0.078 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_NAME 3 640 4 589
Executed in 0.031 seconds
SQL> analyze index i_test_a_idname validate structure;
Index analyzed
Executed in 0.093 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_IDNAME 2 512 1 462
Executed in 0.015 seconds
SQL> analyze index i_test_a_nameid validate structure;
Index analyzed
Executed in 0.109 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_NAMEID 3 512 3 462
Executed in 0.047 seconds
SQL>
SQL> declare
2 -- Local variables here
3 i integer;
4 begin
5 -- Test statements here
6 for j in 1..1000000 loop
7 insert into test_a values(j,'abcdefghijklmn');
8 commit;
9 end loop;
10 end;
11 /
SQL>
SQL>
SQL>
PL/SQL procedure successfully completed
SQL> set timing on
SQL>
SQL> select s.sid, n.name, s.value
2 from v$sesstat s, v$statname n
3 where s.statistic# = n.statistic#
4 and sid in (select sid from v$mystat)
5 and value > 0
6 and n.name like '%split%';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
2887 leaf node splits 16292
2887 leaf node 90-10 splits 8989
2887 branch node splits 64
Executed in 0.031 seconds
SQL> analyze index i_test_a_id validate structure;
Index analyzed
Executed in 0.593 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_ID 3 2560 5 2414
Executed in 0.031 seconds
SQL> analyze index i_test_a_name validate structure;
Index analyzed
Executed in 0.609 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_NAME 3 6528 40 6257
Executed in 0.031 seconds
SQL> analyze index i_test_a_idname validate structure;
Index analyzed
Executed in 0.578 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_IDNAME 3 4864 9 4685
Executed in 0.016 seconds
SQL> analyze index i_test_a_nameid validate structure;
Index analyzed
Executed in 0.61 seconds
SQL> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;
NAME HEIGHT BLOCKS BR_BLKS LF_BLKS
------------------------------ ---------- ---------- ---------- ----------
I_TEST_A_NAMEID 3 4864 20 4685
Executed in 0.016 seconds
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27573546/viewspace-765988/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
PGA结构
请登录后发表评论
登录
全部评论
