有、无索引的简单性能对比(添加和查询）

1.索引对增加数据的影响：

drop table t purge;
drop table t1 purge;
drop table t2 purge;

创建主表t：
create table t as select * from dba_objects;
insert into t select * from dba_object;
insert into t select * from dba_object;

这是t表当前的数据：
SQL> select count(*) from t;

  COUNT(*)
----------
    206598

SQL> create table t1 as select * from dba_objects where 1=2;

表已创建。


SQL> create table t2 as select * from dba_objects where 1=2;

表已创建。


SQL> select count(*) from t1;


  COUNT(*)
----------
         0

SQL> select count(*) from t2;

  COUNT(*)
----------
         0


在t1上创建索引：
SQL> create index indx_t1 on t1(object_id);


索引已创建。




向t1表中插入数据：


SQL> insert into t1 select * from t;


已创建206598行。


已用时间:  00: 00: 07.75


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


---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |   209K|    41M|   827   (1)| 00:00:10 |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | T    |   209K|    41M|   827   (1)| 00:00:10 |
---------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement




统计信息
----------------------------------------------------------
       1383  recursive calls
      43026  db block gets
      15931  consistent gets
          0  physical reads
   55625828  redo size
        682  bytes sent via SQL*Net to client
        599  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     206598  rows processed




再向t2中插入数据：


SQL> insert into t2 select * from t;


已创建206598行。


已用时间:  00: 00: 01.98


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


---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |   209K|    41M|   827   (1)| 00:00:10 |
|   1 |  LOAD TABLE CONVENTIONAL | T2   |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | T    |   209K|    41M|   827   (1)| 00:00:10 |
---------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement




统计信息
----------------------------------------------------------
        524  recursive calls
      15939  db block gets
      10536  consistent gets
          0  physical reads
   24594728  redo size
        682  bytes sent via SQL*Net to client
        599  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     206598  rows processed




通过执行计划可以看到t1和t2主要的不同：
-----------------------------------------------------------
| table  | 时间                | db block gets| redo size |
----------------------------------------------------------
|  t1  | 00: 00: 07.75        |   1383     |   55625828  |
|   
|  t2|   00: 00: 01.98        |     15939  |   24594728  |
---------------------------------------------------------


两者不同的主要原因还是在大批量的插入数据的时候，t1表需要维护索引，所以在时间上比t2慢很多，
而数据库所做的所有的操作都会以日志记录，因为t1的操作比t2多，所以产生的日志文件理所当然的比t2多。


2.select操作
从t1表中查询数据id为2000的数据信息：


SQL> select object_name,object_id from t1 where object_id='2000';


OBJECT_NAME
                          OBJECT_ID
----------------------------------------------------------------------------------------------------
---------------------------- ----------
GV_$THREAD
                               2000
GV_$THREAD
                               2000
GV_$THREAD
                               2000


已用时间:  00: 00: 00.01


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


---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     3 |   237 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     3 |   237 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_T1 |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("OBJECT_ID"=2000)


Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         76  consistent gets
          0  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed




从t2表中查询数据id为2000的数据信息：




SQL> select object_name,object_id from t2 where object_id='2000';


OBJECT_NAME
                          OBJECT_ID
----------------------------------------------------------------------------------------------------
---------------------------- ----------
GV_$THREAD
                               2000
GV_$THREAD
                               2000
GV_$THREAD
                               2000


已用时间:  00: 00: 00.04


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


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    34 |  2686 |   835   (1)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| T2   |    34 |  2686 |   835   (1)| 00:00:11 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("OBJECT_ID"=2000)


Note
-----
   - dynamic sampling used for this statement




统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       3161  consistent gets
          0  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed