之前看网上说数字的对比肯定比字符快,所以想做下测试。思路很简单,对一个表中的字符索引和数字索引进行500w次的查询,看看所需
的时间差距。HXZ_TEST 表有600w记录
create table HXZ_TEST
(
VAR_GUID VARCHAR2(20),
NUM_GUID NUMBER
)
两个字段上都有唯一索引。
news_main表也有600w记录。
数据的样子大概如下:
VAR_GUID NUM_GUID
1 000000001000139maf 52665
2 000000001000139mag 52666
3 00000000000000zr57 52914
...测试目的是想看VAR_GUID 上的索引快还是NUM_GUID 上的索引快。结果如下:
varchar index test start:2009-12-25 11:01:52
varchar index test end :2009-12-25 11:06:34
number index test start:2009-12-25 11:06:34
number index test end :2009-12-25 11:11:26
结果很奇怪,居然是字符的索引比数字的索引快。
欢迎大家讨论,这个测试方法,代码是否有漏洞。
结果是否准确?
----------------------------declare V_ct news_main.ctime%type; v_TEST_NUM number := 5000000; cursor cur_tmp(random_seed number) is
select m.guid from news_main m where rownum <= random_seed; type t_guid is table of news_main.guid%type;
v_guid t_guid; v_random_seed number := 10000;begin open cur_tmp(v_random_seed);
fetch cur_tmp bulk collect
into v_guid;
close cur_tmp; dbms_output.put_line(''); dbms_output.put_line('varchar index test start:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
for i in 1 .. v_TEST_NUM loop
select m.ctime
into V_ct
from HXZ_TEST t, news_main m
where m.guid = t.var_guid
and m.guid = v_guid(dbms_random.value(1, v_random_seed));
end loop; dbms_output.put_line('varchar index test end :' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); dbms_output.put_line('number index test start:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
for i in 1 .. v_TEST_NUM loop
select m.ctime
into V_ct
from HXZ_TEST t, news_main m
where m.seq = t.num_guid
and m.guid = v_guid(dbms_random.value(1, v_random_seed));
end loop;
dbms_output.put_line('number index test end :' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); dbms_output.put_line('');end;
/------------------------
的时间差距。HXZ_TEST 表有600w记录
create table HXZ_TEST
(
VAR_GUID VARCHAR2(20),
NUM_GUID NUMBER
)
两个字段上都有唯一索引。
news_main表也有600w记录。
数据的样子大概如下:
VAR_GUID NUM_GUID
1 000000001000139maf 52665
2 000000001000139mag 52666
3 00000000000000zr57 52914
...测试目的是想看VAR_GUID 上的索引快还是NUM_GUID 上的索引快。结果如下:
varchar index test start:2009-12-25 11:01:52
varchar index test end :2009-12-25 11:06:34
number index test start:2009-12-25 11:06:34
number index test end :2009-12-25 11:11:26
结果很奇怪,居然是字符的索引比数字的索引快。
欢迎大家讨论,这个测试方法,代码是否有漏洞。
结果是否准确?
----------------------------declare V_ct news_main.ctime%type; v_TEST_NUM number := 5000000; cursor cur_tmp(random_seed number) is
select m.guid from news_main m where rownum <= random_seed; type t_guid is table of news_main.guid%type;
v_guid t_guid; v_random_seed number := 10000;begin open cur_tmp(v_random_seed);
fetch cur_tmp bulk collect
into v_guid;
close cur_tmp; dbms_output.put_line(''); dbms_output.put_line('varchar index test start:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
for i in 1 .. v_TEST_NUM loop
select m.ctime
into V_ct
from HXZ_TEST t, news_main m
where m.guid = t.var_guid
and m.guid = v_guid(dbms_random.value(1, v_random_seed));
end loop; dbms_output.put_line('varchar index test end :' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); dbms_output.put_line('number index test start:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
for i in 1 .. v_TEST_NUM loop
select m.ctime
into V_ct
from HXZ_TEST t, news_main m
where m.seq = t.num_guid
and m.guid = v_guid(dbms_random.value(1, v_random_seed));
end loop;
dbms_output.put_line('number index test end :' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); dbms_output.put_line('');end;
/------------------------
扫描两个索引当然比扫描一个索引慢
在SQL*PLUS下执行
set timing on;接下来执行语句都会显示执行时间的
在SQL*PLUS下执行
set timing on;