ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 不同基值的列所建的索引高度和枝节点数

不同基值的列所建的索引高度和枝节点数

原创 Linux操作系统 作者:DataKW 时间:2013-07-10 18:20:36 0 删除 编辑
不同基值的列所建的索引高度和枝节点数
基值越低,索引高度越高,枝节点越多,叶子节点也多,而且叶子节点数多于联合索引(以该基值低字段开头,和索引简化存储的数据有关)




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博客 ” ,链接:http://blog.itpub.net/27573546/viewspace-765988/,如需转载,请注明出处,否则将追究法律责任。

下一篇: PGA结构
请登录后发表评论 登录
全部评论

注册时间:2012-08-12

  • 博文量
    132
  • 访问量
    298301