從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
孤飛燕 閱讀(262)
評論(0) 編輯 收藏 所屬分類:
數據庫