oracle 如何从oracle数据库中随机获取记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9868409/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get records randomly from the oracle database?
提问by Bhadra
I need to select rows randomly from an Oracle DB.
我需要从 Oracle DB 中随机选择行。
Ex: Assume a table with 100 rows, how I can randomly return 20 of those records from the entire 100 rows.
例如：假设一个表有 100 行，我如何从整个 100 行中随机返回 20 条记录。
回答by cagcowboy
SELECT *
FROM (
SELECT *
FROM table
ORDER BY DBMS_RANDOM.RANDOM)
WHERE rownum < 21;
回答by Jeffrey Kemp
SAMPLE()is not guaranteedto give you exactly 20 rows, but might be suitable (and may perform significantly better than a full query + sort-by-random for large tables):
SAMPLE()不能保证给你正好 20 行，但可能是合适的（并且可能比大表的完整查询 + 随机排序执行得更好）：
SELECT *
FROM table SAMPLE(20);
Note: the 20here is an approximate percentage, not the number of rows desired. In this case, since you have 100 rows, to get approximately 20 rows you ask for a 20% sample.
注意：20这里是一个近似百分比，而不是所需的行数。在这种情况下，由于您有 100 行，要获得大约 20 行，您需要 20% 的样本。
回答by grokster
SELECT * FROM table SAMPLE(10) WHERE ROWNUM <= 20;
This is more efficient as it doesn't need to sort the Table.
这更有效，因为它不需要对表进行排序。
回答by Bishan
SELECT column FROM
( SELECT column, dbms_random.value FROM table ORDER BY 2 )
where rownum <= 20;
回答by Nishant Sharma
To randomly select 20 rows I think you'd be better off selecting the lot of them randomly ordered and selecting the first 20 of that set.
要随机选择 20 行，我认为您最好选择随机排序的许多行并选择该组的前 20 行。
Something like:
就像是：
Select *
from (select *
from table
order by dbms_random.value) -- you can also use DBMS_RANDOM.RANDOM
where rownum < 21;
Best used for small tables to avoid selecting large chunks of data only to discard most of it.
最好用于小表，以避免选择大块数据只是为了丢弃大部分数据。
回答by Jinwu Seo
In summary, two ways were introduced
综上，介绍了两种方式
1) using order by DBMS_RANDOM.VALUE clause
2) using sample([%]) function
The first way has advantage in 'CORRECTNESS' which means you will never fail get result if it actually exists, while in the second way you may get no result even though it has cases satisfying the query condition since information is reduced during sampling.
第一种方式在“正确性”方面具有优势，这意味着如果它确实存在，您将永远不会失败获取结果，而在第二种方式中，即使它有满足查询条件的案例，您也可能不会得到任何结果，因为在采样期间信息减少了。
The second way has advantage in 'EFFICIENT' which mean you will get result faster and give light load to your database. I was given an warning from DBA that my query using the first way gives loads to the database
第二种方式在“高效”方面具有优势，这意味着您将更快地获得结果并减轻数据库的负载。DBA 警告我，我使用第一种方式的查询会给数据库带来负载
You can choose one of two ways according to your interest!
您可以根据自己的兴趣选择两种方式之一！
回答by Waseem Khan
table
桌子
We have Teacher table
我们有老师桌
Oracle Syntax
甲骨文语法
SELECT * FROM
(
SELECT column_name FROM table_name
ORDER BY dbms_random.value
)
WHERE rownum = 1;
