Insert给索引带来的开销问题

 

验证insert 给索引带来的开销问题
--1、构造一个表
--2、插入表500W的数据
--3、创建索引usernumber列的索引
--4、insert 1000数据，记录时间
--5、删除索引，创建inserttime列的索引
--6、insert 1000数据，记录时间，跟4的时间做对比
SQL> create table big_table as
   1 select  trunc(dbms_random.value(1,100000000)) usernumber,
   2        (sysdate+1/1000000) inserttime
   3  from dual connect by level <=5000000;
 
 
Table created
 
Executed in 30.704 seconds

SQL>  create index idx_usernumber on big_table(usernumber);

索引已创建。

已用时间:  00: 00: 14.75
SQL> Insert Into Big_Table
  2    Select Trunc(Dbms_Random.Value(1, 100000000)) Usernumber,
  3           (Sysdate + 1 / 1000000) Inserttime
  4      From Dual
  5    Connect By Level <= 1000;

已创建1000行。

已用时间:  00: 00: 05.18

执行计划
----------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|   1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

统计信息
----------------------------------------------------------
         34  recursive calls
       3049  db block gets
          9  consistent gets
       2315  physical reads
     268816  redo size
        678  bytes sent via SQL*Net to client
        706  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       1000  rows processed
      
SQL> drop index  idx_usernumber;

索引已删除。

已用时间:  00: 00: 02.17 
SQL> create index idx_inserttime on big_table(inserttime);

索引已创建。

已用时间:  00: 00: 11.89
SQL> Insert Into Big_Table
  2    Select Trunc(Dbms_Random.Value(1, 100000000)) Usernumber,
  3           (Sysdate + 1 / 1000000) Inserttime
  4      From Dual
  5    Connect By Level <= 1000;

已创建1000行。

已用时间:  00: 00: 00.03

执行计划
----------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|   1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

统计信息
----------------------------------------------------------
         34  recursive calls
        108  db block gets
         19  consistent gets
          8  physical reads
      99736  redo size
        677  bytes sent via SQL*Net to client
        706  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       1000  rows processed
  上述实验的结果对比，usernumber类型的索引插入1000数据的时间为

已用时间:  00: 00: 05.18

而inserttime类型的索引插入1000数据的时间未

已用时间:  00: 00: 00.03

很明显，结论就是在相同数量级的情况下，insert操作，离散型的索引的开销比有序的索引开销要大得多。