ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 非唯一索引不为人所知的一个细微特征

非唯一索引不为人所知的一个细微特征

原创 Linux操作系统 作者:yezhibin 时间:2009-06-09 15:45:04 0 删除 编辑
        如果表包含非唯一索引,在对表进行批量delete+insert的时候,在delete完成后,必须先commit,再insert,或者是session在完成非唯一索引创建后,进行commit,否则非唯一索引需要重建。唯一索引没有以上特性。
     
       实验一:非唯一索引创建后未commit (delete+insert)
        SQL>create table test1 (id number, name varchar2(20));
       
        SQL>insert into test1 select rownum, 'TEST1'
                  from dual connect by level <=1000000;

        SQL>commit;

        SQL>create index test1_idx on test1(id);

        SQL>analyze index test1_idx validate structure;

        SQL>select blocks, lf_rows, lf_blks, del_lf_rows from index_stats;  
                  BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS
                   -------------- ---------------- ------------------ -------------------
                        2304      1000000          2226               0

         将表数据删除,然后重新insert新的数据
       
         SQL>truncate table test1 (或者 delete test1)

         SQL>insert into test1 select rownum, 'LongTop'
                   from dual connect by level <= 1000000;

         SQL>commit;

         SQL>analyze index test1_idx validate structure;

         SQL>select blocks, lf_blks, del_lf_rows from index_stats;
                  BLOCKS    LF_BLKS DEL_LF_ROWS
                  -------------------- ---------- -----------------
                      4736       4520     1000000
         
        索引的存储空间增加一倍,索引删除空间没有被重用,需要对索引进行重建。

       实验二:非唯一索引 创建后未commit(delete+commit+insert)

       SQL>create table test1 (id number, name varchar2(20));
       
        SQL>insert into test1 select rownum, 'TEST1'
                  from dual connect by level <=1000000;

        SQL>commit;

        SQL>create index test1_idx on test1(id);

        SQL>analyze index test1_idx validate structure;

        SQL>select blocks, lf_rows, lf_blks, del_lf_rows from index_stats;  
                  BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS
                   -------------- ---------------- ------------------ -------------------
                        2304      1000000          2226               0

         将表数据删除,然后commit,再重新insert新的数据
       
         SQL> truncate table test1
  
          SQL>commit;

         SQL>insert into test1 select rownum, 'LongTop'
                   from dual connect by level <= 1000000;

         SQL>commit;

         SQL>analyze index test1_idx validate structure;

         SQL>select blocks, lf_blks, del_lf_rows from index_stats;
                  BLOCKS    LF_BLKS DEL_LF_ROWS
              -------------- ---------------- ------------------ -------------------
                       2048              1999           0

          发现blocks的数量比原先的更少。

      实验三、非唯一索引创建后commit (delete+insert)

       SQL>create table test1 (id number, name varchar2(20));
       
        SQL>insert into test1 select rownum, 'TEST1'
                  from dual connect by level <=1000000;

        SQL>commit;

        SQL>create index test1_idx on test1(id);

        SQL>commit;

        SQL>analyze index test1_idx validate structure;

        SQL>select blocks, lf_rows, lf_blks, del_lf_rows from index_stats;  
                  BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS
                   -------------- ---------------- ------------------ -------------------
                        2304      1000000          2226               0

         将表数据删除,然后重新insert新的数据
       
         SQL>truncate table test1 (或者 delete test1)

         SQL>insert into test1 select rownum, 'LongTop'
                   from dual connect by level <= 1000000;

         SQL>commit;

         SQL>analyze index test1_idx validate structure;

         SQL>select blocks, lf_blks, del_lf_rows from index_stats;
                  BLOCKS    LF_BLKS DEL_LF_ROWS
                  -------------------- ---------- -----------------
                          2048       1999           0

    实验四、唯一索引(delete+insert)

        SQL>create table test2 (id number, name varchar2(20));

         SQL>insert into test2 select rownum, 'TEST2'
                    from dual connect by level <=1000000;       

         SQL>commit;
 
        SQL>create unique index test2_idx on test2(id);

         SQL>analyze index test2_idx validate structure;

         SQL>select blocks, lf_rows, lf_blks, del_lf_rows from index_stats;  
                  BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS
                   -------------- ---------------- ------------------ -------------------
                        2176    1000000           2088             0
        
           将表数据删除,然后重新insert新的数据
        
           SQL> delete test2;

           SQL>insert into test2 select rownum, 'LongTop'
                     from dual connect by level <= 1000000;

            SQL>commit;

            SQL>analyze index test2_idx validate structure;

            SQL>select blocks, lf_blks, del_lf_rows from index_stats;
                  BLOCKS    LF_BLKS DEL_LF_ROWS
                  -------------------- ---------- -----------------
                       2176              2088            0
       


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/354732/viewspace-605655/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-18

  • 博文量
    159
  • 访问量
    509837