前言
在測試oracle索引性能時(shí)大意了，沒有仔細(xì)分析數(shù)據(jù)特點(diǎn)，將情況特此記錄下來。
需求: 對一張100w記錄的表的 stuname列進(jìn)行查詢，測試在建立索引與不建立索引的區(qū)別. 以下是開始用的創(chuàng)建代碼及執(zhí)行效果.
1. 隨機(jī)數(shù)據(jù)生成代碼分析
--為測試索引而準(zhǔn)備的隨機(jī)數(shù)據(jù)生成代碼，先分析一下
select rownum as id,
'smith'||trunc(dbms_random.value(0, 100)) as stu_name,
dbms_random.string('x', 20) stu_pwd,
to_char(add_months(sysdate,-DBMS_RANDOM.VALUE(100,200)) + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') as birthday ,
decode( TRUNC(DBMS_RANDOM.VALUE(1,5)),1,'湖南省',2,'湖北省',3,'江西省','北京市') as address
from dual
connect by level = 100;
--先分析以下上面的代碼
-- 偽列: rownum
-- dual : 測試表
-- || 字符串聯(lián)接
--1. 測試生成100條記錄 connect by level=100 :
--a、利用Oracle特有的“connect by”樹形連接語法生成測試記錄，“l(fā)evel = 100”表示要生成100記錄；
--b、利用rownum虛擬列生成遞增的整數(shù)數(shù)據(jù)；
--c、利用sysdate函數(shù)加一些簡單運(yùn)算來生成日期數(shù)據(jù)，本例中是每條記錄的時(shí)間加1秒；
-- add_months(sysdate,-DBMS_RANDOM.VALUE(100,200)) 用當(dāng)前時(shí)間 減去 至少100個(gè)月，最多200個(gè)月，來生成生日
--d、利用dbms_random.value函數(shù)生成隨機(jī)的數(shù)值型數(shù)據(jù)，都是double型，所以都加了 trunc( )以截?cái)嘈?shù)位，本例中是生成0到100之間的隨機(jī)整數(shù)；
--e、利用dbms_random.string函數(shù)生成隨機(jī)的字符型數(shù)據(jù)，本例中是生成長度為20的隨機(jī)字符串，字符串中可以包括字符或數(shù)字。
2. 生成測試表及數(shù)據(jù)
--2. 正式生成100W
drop table stu_test_100w; --如果原來有，則先刪除原來的表
--創(chuàng)建表及數(shù)據(jù)
create table stu_test_100w
as
select rownum as id,
'smith'||trunc(dbms_random.value(0, 99)) as stu_name,
dbms_random.string('x', 20) stu_pwd,
to_char(add_months(sysdate,-DBMS_RANDOM.VALUE(100,200)) + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') as birthday ,
decode( TRUNC(DBMS_RANDOM.VALUE(1,5)),1,'湖南省',2,'湖北省',3,'江西省','北京市') as address
from dual
connect by level = 1000000; -- 生成 100w測試數(shù)據(jù)
-- 查看當(dāng)前用戶模式下所有的表
select * from tab where tname='STU_TEST_100W';
--先執(zhí)行一次查詢, 注意查詢所用的時(shí)間,此時(shí)并沒有加入索引
select * from stu_test_100w where stu_name='smith13';
執(zhí)行結(jié)果:
以上是沒有用到索引時(shí)的執(zhí)行用時(shí) 6.781秒.
下面創(chuàng)建索引后，再用同一查詢來測試.
--********生成索引后，再執(zhí)行一次查詢
drop index index_student_test;
create index index_student_test
on stu_test_100w(stu_name); --索引是針對某個(gè)表的某個(gè)列
--先執(zhí)行一次查詢, 注意查詢的時(shí)間,此時(shí)加入了索引
select * from stu_test_100w where stu_name='smith13';
為什么用了索引后時(shí)間查詢能還下降了呢????
分析如下:
1. 索引生成的字段的值分存得太密集了，查看上面的代碼會發(fā)現(xiàn)我們stu_name只生成在了 smith0-99之間，即只有100種可能性, 對于100w數(shù)據(jù)則言，即每個(gè)名字都有約1w個(gè).
2。 因?yàn)閿?shù)據(jù)太密集了，所以以上查詢的花的時(shí)間主要在1w條數(shù)據(jù)的顯示上， 所以我們可以觀察到不管是否用到了索引，都要共到6-7秒來顯示結(jié)果.
3. 那為什么用了索引還慢一些呢? 這就與索引的存儲結(jié)構(gòu)有關(guān)系了.oracle默認(rèn)使用的是B樹索引, 當(dāng)使用索引列查詢時(shí)，查詢必須先查看索引，通過索引去定位數(shù)據(jù)，而咱們的數(shù)據(jù)分布又比較密集，所以使用索引所導(dǎo)致的時(shí)間損耗要大于直接磁盤搜索的時(shí)間.
那么如何解決呢?
隨機(jī)生成的姓名分布廣一些(這與真實(shí)的數(shù)據(jù)也一樣). 即將隨機(jī)生成代碼修改為 'smith'||trunc(dbms_random.value(0, 9999999)) as stu_name,
drop table stu_test_100w; --如果原來有，則先刪除原來的表
--重新生成表及隨機(jī)數(shù)據(jù)，注意 stu_name列的取值范圍加大
create table stu_test_100w
as
select rownum as id,
'smith'||trunc(dbms_random.value(0, 9999999)) as stu_name,
dbms_random.string('x', 20) stu_pwd,
to_char(add_months(sysdate,-DBMS_RANDOM.VALUE(100,200)) + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') as birthday ,
decode( TRUNC(DBMS_RANDOM.VALUE(1,5)),1,'湖南省',2,'湖北省',3,'江西省','北京市') as address
from dual
connect by level = 1000000;
--先執(zhí)行一次查詢, 注意查詢的時(shí)間,此時(shí)并沒有加入索引
select * from stu_test_100w where stu_name='smith8821228';
執(zhí)行結(jié)果如下:
用時(shí) 0.312秒.
接著創(chuàng)建索引后，再測試同一個(gè)查詢
--********生成索引后，再執(zhí)行一次查詢
drop index index_student_test;
create index index_student_test
on stu_test_100w(stu_name); --索引是針對某個(gè)表的某個(gè)列
--先執(zhí)行一次查詢, 注意查詢的時(shí)間,此時(shí)加入了索引
select * from stu_test_100w where stu_name='smith8821228';
使用索引后，同一個(gè)查詢只需0.015秒，在原來用時(shí)0.312的基礎(chǔ)下，下降了n倍.
總結(jié)
到此這篇關(guān)于oracle索引測試的文章就介紹到這了,更多相關(guān)oracle索引測試內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- oracle數(shù)據(jù)庫關(guān)于索引建立及使用的詳細(xì)介紹
- Oracle Index索引無效的原因與解決方法
- oracle使用索引與不使用索引的性能詳析
- ORACLE檢查找出損壞索引(Corrupt Indexes)的方法詳解
- Oracle復(fù)合索引與空值的索引使用問題小結(jié)
- oracle分區(qū)索引的失效和重建代碼示例
- Oracle關(guān)于重建索引爭論的總結(jié)
- Oracle 分區(qū)索引介紹和實(shí)例演示
- Oracle CBO優(yōu)化模式中的5種索引訪問方法淺析
- oracle索引總結(jié)
