Oracle 索引和執行計劃

        建了個測試的資料表，想測試建了索引和不建立索引的區別。建立表的資料量為108631962行。每次插入9999999行，每次大概半個小時。在id上建立索引，花時間為37秒，不建立索引花時間為：1分58秒。演示如下所示：
SQL> insert into studyindex1 select  rownum id,'db'dbms_random.value(
  2  1,100) name,dbms_random.string('X',20) remark from dual connect by level<10
000000;

已建立9999999行。

SQL> commit;

提交完成。

SQL> select count(*) from studyindex1;

  COUNT()
----------
 108631962

12:13:22 SQL> create index id_idx on studyindex1(id);

索引已建立。

14:19:32 SQL> commit;

提交完成。

14:22:51 SQL> select id,name,remark from studyindex1 where id=203;

已選擇38行。


執行計劃
----------------------------------------------------------
Plan hash value: 2350744396

--------------------------------------------------------------------------------

-----------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

 Time     |

--------------------------------------------------------------------------------

-----------

|   0 | SELECT STATEMENT            |             |    38 | 77444 |    43   (0)|

 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| STUDYINDEX1 |    38 | 77444 |    43   (0)|

 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | ID_IDX      |    38 |       |     3   (0)|

 00:00:01 |

--------------------------------------------------------------------------------

-----------


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

   2 - access("ID"=203)

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


統計資訊
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        154  consistent gets
        312  physical reads
          0  redo size
       3663  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         38  rows processed

14:23:28 SQL> drop index id_idx;

索引已刪除。

14:24:15 SQL> commit;

提交完成。

14:24:21 SQL> select id,name,remark from studyindex1 where id=203;

已選擇38行。


執行計劃
----------------------------------------------------------
Plan hash value: 469406081

--------------------------------------------------------------------------------

-

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------

-

|   0 | SELECT STATEMENT  |             | 12417 |    24M|   248K  (1)| 00:49:47
|

|*  1 |  TABLE ACCESS FULL| STUDYINDEX1 | 12417 |    24M|   248K  (1)| 00:49:47
|

--------------------------------------------------------------------------------

-


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

   1 - filter("ID"=203)

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


統計資訊
----------------------------------------------------------
        169  recursive calls
          0  db block gets
    1121670  consistent gets
    1053183  physical reads
          0  redo size
       3663  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         38  rows processed

14:26:19 SQL>