更新时间:2022-09-13 18:52:22
根据博客:某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法,我们得到了一个含有600多万条用户数据的oracle数据库。本文就是根据这个来验证数据库索引的特性。
数据导入方法参考某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法。
上述数据库包含主键索引,但是没有为主键命名,因此搜索该索引的等级BLEVEL,可以通过以下查询语句求出:
select index_name, blevel, num_rows from user_indexes where table_name = 'CSDNUSER';
查询结果如下图所示:
从上述查询结果中我们发现没有BLEVEL和NUM_ROWS。
接下来我们查询ID>700万数据,之所以是700万是因为我们总共数据时600多万,这样可以更加明显的看出来有没有索引的查询效率。查询语句如下:
SET AUTOTRACE ON SELECT * FROM CSDNUSER2 WHERE ID>7000000;
查询执行计划如下:
从统计信息中我们看出一共有“92 consistent gets”,相当于有92次IO。
然后我们删除上述主键SYS_COO38672,删除语句如下:
--删除主键 alter table csdnuser2 drop constraint SYS_C0038672;
再次执行上述查询语句,查询执行计划如下:
从统计信息中我们看出一共有“45129 consistent gets”,相当于有45129次IO。
添加主键语句如下:
--添加主键 alter table csdnuser add constraint pk_csdnuser primary key(ID);
查询该索引的等级BLEVEL,可以通过以下查询语句求出:
select index_name, blevel, num_rows from user_indexes where table_name = 'CSDNUSER';
查询结果如下图所示:
从上述查询结果中我们发现BLEVEL:2和NUM_ROWS=6428632,为表中记录数。
再次执行上述查询语句,查询执行计划如下:
发现是“ 3 consistent gets”,表明添加命名索引以后,只需要3次IO就可以结束查询。
上述的命名主键与非命名主键的说法是错误的,第二次consistent gets子所以很大是因为删除了主键索引,这是没有错的。而第三次的consistent gets为3,而第一次consistent gets为92,并不说明自定义命名的索引效率比系统命名的索引效率高。之所以第三次只需要3次consistent gets是因为执行完第一次以后有缓存存在。假设在第一次查询以后再一次查询,那么统计结果跟第三次一模一样。
http://www.itpub.net/thread-1313899-1-1.html
查询NO=5000,查询语句如下:
第一次查询得到的统计信息:
第二次查询得到的统计信息:
第三次查询得到的统计信息:
第四次查询得到的统计信息:
第五次查询得到的统计信息:
第六次查询得到的统计信息:
第七次查询得到的统计信息:
从第一次到第三次查询,都是无索引状态下的查询,我们可以发现:
从第四次到第五次查询,都是有索引状态下的插叙,我们可以发现:
从六次到第七次查询,是将原来索引换成了主键,我们可以发现:
主键也是索引的一种,只要加了索引,那么逻辑读(consistent gets)就明显降低,查询效率大大提高。这也是索引的作用。
假设我们运行如下命令清空缓存:
alter system flush buffer_cache; alter system flush shared_pool;
然后再次执行查询语句,得到的统计信息如下:
可以看到physical reads=308。
再次执行查询语句,,得到的统计信息如下:
本文转自xwdreamer博客园博客,原文链接:http://www.cnblogs.com/xwdreamer/archive/2012/06/11/2545689.html,如需转载请自行联系原作者