Oracle 索引和执行计划
建了个测试的数据表，想测试建了索引和不建立索引的区别。建立表的数据量为108631962行。每次插入9999999行，每次大概半个小时。
建了个测试的数据表，想测试建了索引和不建立索引的区别。建立表的数据量为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 level000000;
已创建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>