Order By dbms_random.value ,为结果集的每一行计算一个随机数,dbms_random.value 是结果集的一个列(虽然这个列并不在select list 中),然后根据该列排序,得到的顺序自然就是随机的啦。
select user_id,user_name,dbms_random.value a from users
order by 3;
USER_ID USER_NAME A
---------- -------------------- -----------------------
7 Work 0.19171380001609610498
2 Home 0.21382718239249496610
9 School 0.33115963659609025059
2 0.67036429352234003346
2 Home 0.68759901458783005938
3 SQL 0.74564969283968042913
2 School 0.79980010030735903469
3 Work 0.82760633549635323014
5 Work 0.83645537657875791226
数据量大的情况下,因为要产生随机数,效率并不是很理想
select user_id,user_name,dbms_random.value a from users
order by 3;
USER_ID USER_NAME A
---------- -------------------- -----------------------
7 Work 0.19171380001609610498
2 Home 0.21382718239249496610
9 School 0.33115963659609025059
2 0.67036429352234003346
2 Home 0.68759901458783005938
3 SQL 0.74564969283968042913
2 School 0.79980010030735903469
3 Work 0.82760633549635323014
5 Work 0.83645537657875791226
数据量大的情况下,因为要产生随机数,效率并不是很理想