從100萬(wàn)數(shù)據(jù)量的表中隨機(jī)抽取一條數(shù)據(jù) 要達(dá)到0.02S以內(nèi)
剛好我有一個(gè)120w數(shù)據(jù)的測(cè)試表，在一個(gè)很老的PC上，northwood 2.4的p4，sis芯片組。
復(fù)制內(nèi)容到剪貼板
代碼:
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不是一個(gè)恐怖的數(shù)據(jù)量，創(chuàng)建字段索引都比較快。這類問題關(guān)注的焦點(diǎn)，就是在如何減少IO上，諸如get random value的問題，實(shí)際只消耗一次CPU時(shí)間，而CPU都幾百M(fèi) HZ。
最后，我用了一個(gè)with 語(yǔ)法來(lái)取數(shù)，其實(shí)是我在測(cè)試過程發(fā)現(xiàn)一個(gè)現(xiàn)象，暫時(shí)不表。
引自：http://www.oracle.com.cn/viewthread.php?tid=130433&extra=page%3D1
posted on 2010-12-17 16:06
孤飛燕 閱讀(267)
評(píng)論(0) 編輯 收藏 所屬分類:
數(shù)據(jù)庫(kù)