从100万数据量的表中随机抽取一条数据 要达到0.02S以内
刚好我有一个120w数据的测试表，在一个很老的PC上，northwood 2.4的p4，sis芯片组。
复制内容到剪贴板
代码:
SQL> set timing on;
SQL> SELECT COUNT(1) FROM t1;
COUNT(1)
----------
1219948
Executed in 0.015 seconds
SQL> alter table T1 add rn number;
Table altered
Executed in 0.36 seconds
SQL> update t1
2 set rn =rownum;
1219948 rows updated
Executed in 129.75 seconds
SQL> create index index_t1_rn on T1 (rn);
Index created
Executed in 51.234 seconds
SQL>
SQL> WITH tab AS(SELECT trunc(dbms_random.value(1,100000)) a FROM dual)
2 SELECT * FROM t1 WHERE rn =( SELECT a FROM tab)
3 /
NORMAL CLASS RN
---------- --------------------------------------- ----------
3198 5 7384
Executed in 0.031 seconds
SQL>
SQL> WITH tab AS(SELECT trunc(dbms_random.value(1,100000)) a FROM dual)
2 SELECT * FROM t1 WHERE rn =( SELECT a FROM tab)
3 /
NORMAL CLASS RN
---------- --------------------------------------- ----------
4760 12 72082
Executed in 0.047 seconds
SQL> /
NORMAL CLASS RN
---------- --------------------------------------- ----------
6922 7 30862
Executed in 0.033 seconds
SQL> /
NORMAL CLASS RN
---------- --------------------------------------- ----------
1727 5 81038
Executed in 0.019 seconds
SQL> /
NORMAL CLASS RN
---------- --------------------------------------- ----------
11890 3 65704
Executed in 0.016 seconds
100w不是一个恐怖的数据量，创建字段索引都比较快。这类问题关注的焦点，就是在如何减少IO上，诸如get random value的问题，实际只消耗一次CPU时间，而CPU都几百M HZ。
最后，我用了一个with 语法来取数，其实是我在测试过程发现一个现象，暂时不表。
引自：http://www.oracle.com.cn/viewthread.php?tid=130433&extra=page%3D1
posted on 2010-12-17 16:06
孤飞燕 阅读(258)
评论(0) 编辑 收藏 所属分类:
数据库