验证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操作,离散型的索引的开销比有序的索引开销要大得多。