不应该是家,窝更适合一点
oracle索引插入数据性能 + 数据量大小查询效率
上一篇 /
下一篇 2010-06-24 09:29:26
/ 个人分类:sql
1,建表
CREATE TABLE "test"
(
"id" INTEGER NOT NULL,
"order_no" NUMBER(10),
"order_date" DATE,
"ordership" VARCHAR2(500 BYTE),
"order_comment" VARCHAR2(100 BYTE)
)
2,插入数据脚本
declare
BEGIN
for i in 1 .. X
loop
--syexecute immediate
insert into "test" values(i,24+i,sysdate(),'ship' || i,'coment' || i);
end loop;
commit;
END;
3,有无索引对比
| 50000条 |
500000条 |
100000条 |
无索引 | 2s | 20s | 40s |
主键索引 | 2s | 25s | 50s |
全部索引 | 5s | 96s | 215s |
4,查询数据:
| 001sql | 002sql | 003sql | 004sql | 005sql | 006sql | 007sql |
10000 | 16ms | 16ms | 16ms | 16ms | 16ms | 16ms | 待补充 |
1000000 | 18ms | 204ms | 266ms | 31ms | 141ms | 109ms | 待补充 |
说明:
1),10000 ---表中10000条数据;1000000----表中1000000条数据;
2),001sql--select "order_date" from "test";
3),002sql--select "order_date" from "test" group by "order_date";
4),003sql--select "order_date" from "test" order by DESC;
5),004sql--select max("id") from "test";
6),004sql--select sum("order_no") from "test";
7),004sql--select "order_date" from "test" where {加区间}group by "order_date"
5,结论:
1),表中索引越多,插入数据效率越差
2),表查询默认是顺序排序的,查询倒序排序比顺序效率差
3),...
收藏
举报
TAG:
性能
数据库
索引
Oracle
oracle