创建索引影响插入的速度

 

SQL> set autot on;

SQL> create table testnoindex(a1 number);

Table created.

Elapsed: 00:00:00.09
SQL> insert into testnoindex select level from dual connect by level<=30000;

30000 rows created.

Elapsed: 00:00:00.15
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.04
SQL> insert into testnoindex select dbms_random.random from dual connect by level<=30000;

30000 rows created.

Elapsed: 00:00:00.62
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.03
SQL> create table testindex(a1 number primary key);

Table created.

Elapsed: 00:00:00.20
SQL> insert into testindex select level from dual connect by level<=30000;

30000 rows created.

Elapsed: 00:00:00.87

SQL> insert into testindex select dbms_random.random from dual connect by level<=30000;

30000 rows created.

Elapsed: 00:00:06.96

 

SQL> select * from testindex where rownum<10;

        A1
----------
       579
       580
       581
       582
       583
       584
       585
       586
       587

9 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=9 Bytes=117
          )

   1    0   COUNT (STOPKEY)
   2    1     INDEX (FAST FULL SCAN) OF 'SYS_C005273' (INDEX (UNIQUE))
           (Cost=2 Card=30000 Bytes=390000)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        487  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

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

转载于:http://blog.itpub.net/13165828/viewspace-610783/