ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【索引】oracle查询使用索引和不使用索引的比较

【索引】oracle查询使用索引和不使用索引的比较

原创 Linux操作系统 作者:散叶涔 时间:2012-04-25 11:16:30 0 删除 编辑

前言:关于查询走不走索引里面有很多的东西,也分为很多种情况。这里只是简单的一个比较,后续会进一步分析。

1、创建表:SQL> create table test1 (id number,c1 varchar2(50),c2 varchar2(50),c3 varchar2(5
0),c4 varchar2(50));

2、模拟插入10000条记录:

SQL> begin
for i in 1..10000 loop
insert into test1 values(i,'阿斯顿飞就快了','及卡拉加水电费了健康','就爱看lsd减肥辣椒水代理费','阿斯顿飞卡死就到了费劲啊lsd就分开了');
end loop;
end;
/

3、打开执行计划,并查询:

  SQL> set autotrace on;

  SQL> select * from scott.test1 where id=500;

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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 242 | 42 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST1 | 2 | 242 | 42 (3)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("ID"=500)

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


统计信息
----------------------------------------------------------
171 recursive calls
0 db block gets
283 consistent gets
0 physical reads
0 redo size
708 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
4、创建在id上的索引:

SQL> create index i_test1 on test1(id);

5、再次查询,并查看执行计划:

SQL> select * from scott.test1 where id=500;

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

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

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

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

| 0 | SELECT STATEMENT | | 1 | 121 | 2 (0)| 00:
00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 121 | 2 (0)| 00:
00:01 |

|* 2 | INDEX RANGE SCAN | I_TEST1 | 1 | | 1 (0)| 00:
00:01 |

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


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

2 - access("ID"=500)

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


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

6、可以看到,创建索引后效率有很大提高。

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

请登录后发表评论 登录
全部评论

注册时间:2012-04-17

  • 博文量
    44
  • 访问量
    118697