oracle 执行计划索引,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>