如果表包含非唯一索引，在对表进行批量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