oracle 查询速度测试,测试orcale中索引对表的查询和操作效率 影响

--oracle创建索引前后测试

--说明：数据库表operlog没有建主键，也没有建索引。现在对其建索引，测试索引对查询和操作的效率影响情况。

--operlog的表结构----------

create table LAB1107.OPERLOG

(

LOGID     VARCHAR2(18) not null,

OPERTABLE VARCHAR2(100) not null,

OPERKEY   VARCHAR2(100) not null,

OPERTYPE  VARCHAR2(10) not null,

OPERBY    VARCHAR2(100) not null,

OPERDATE  DATE not null,

OPERDESC  VARCHAR2(4000) not null,

MEMO      VARCHAR2(100)

)

tablespace LAB1107

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

-------------------------对operlog创建索引----------------------------------

create index operlog_index on operlog (logid); --用时：12.328s

--删除索引

drop index operlog_index;

-- 查看索引个数和类别

select index_name,index_type,table_name from user_indexes order by table_name;

----------------------------------------------------测试语句：创建索引前后用时对比------------------------------------------------------

select count(*) from operlog   --结果：476500

--创建索引前用时：7.078s

--创建索引后用时：1.36s

select max(logid)+1 from operlog

--创建索引前用时：6.89s

--创建索引后用时：0.031s

select * from operlog where logid >'200904220000000001'  --21rows

--创建索引前用时：6.828s

--创建索引后用时：0.218s

select * from operlog where logid like '%20090422%'  --22rows

--创建索引前用时：7.708s

--创建索引后用时：7.172s

select * from operlog where logid = '200904240000000005'

--创建索引前用时：7.5s

--创建索引后用时：0.031s

------------------------------------测试插入数据：operlog-创建索引前后插入记录用时对比----------------------

--调用存储过程插入数据(writelog(....))

--test writelog(....)

-- 无索引时：用时：6.985s

-- 有索引时：用时：0.531s

-------------------------------------执行计划(explain plan)：--all rows---创建索引前后执行成本对比-------------------------

--cost 表示成本，通过数据库io访问和cpu性能计算得来

--cadinality 根据遍历索引或者全表扫描的记录计算得来

--bytes 表示访问的数据量

--一个优化后的sql查询，以上3个值应该是越小越好。

select count(*) from operlog   --结果：476500

-- 无索引时：cost 9102 cardinality 1 bytes  --全表扫描

-- 有索引时：cost 449 cardinality 1 bytes

--operlog_index cost 449 cardinality 358012 bytes

select max(logid)+1 from operlog

-- 无索引时：cost 9102 cardinality 1 bytes 11   --全表扫描

-- 有索引时：cost 3 cardinality 1 bytes 11

--operlog_index cost 3 cardinality 358012 bytes 3938132

select * from operlog where logid >'200904220000000001'  --21rows

-- 无索引时：cost 9105 cardinality 2632 bytes 5887784  --全表扫描

-- 有索引时：cost 1303 cardinality 2632 bytes 5887784

--operlog_index cost 18 cardinality 2632 bytes

select * from operlog where logid like '%20090422%'

-- 无索引时：cost 9106 cardinality 2632 bytes 5887784 --全表扫描

-- 有索引时：cost 9106 cardinality 2632 bytes 5887784 --全表扫描

select * from operlog where logid like '20090422'||'%%'

-- 无索引时：cost 9106 cardinality 2632 bytes 5887784 --全表扫描

-- 有索引时：cost 1303 cardinality 2632 bytes 5887784

--operlog_index cost 18 cardinality 2632 bytes

select * from operlog where logid = '200904240000000005'

-- 无索引时：cost 9105 cardinality 456 bytes 1020072 --全表扫描

-- 有索引时：cost 4 cardinality 1 bytes 2237

--operlog_index cost 3 cardinality 1 bytes

--------------总结-----------------------------------------------------------------------

索引可以很明显的提高查询的效率，对已有顺序的表的添加操作，索引不会影响操作效率。