请教，ORACLE里如何随机读取表中的纪录？

ZALBB

原帖由 joyw 于 2008-4-8 09:30 发表
用sample 行不?

select *  from test sample(10);

但返回纪录数好像无法确定，时多时少．

cow977

请问ＬＺ，这是啥需求？

carcase

SQL> select * from ( select * from tab order by dbms_random.value ) where rownum <= 3;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
ALL_PART_COL_STATISTICS                                      VIEW
BOOTSTRAP$                                                   TABLE
V_$DATAFILE                                                  VIEW

SQL> select * from ( select * from tab order by dbms_random.value ) where rownum <= 3;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
USER_UNUSED_COL_TABS                                         VIEW
EXU9TABC                                                     VIEW
PRVT_EPGCTAB_DAD                                             TABLE

SQL> select * from ( select * from tab order by dbms_random.value ) where rownum <= 3;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
DBA_ROLLBACK_SEGS                                            VIEW
V_$LOGMNR_REGION                                             VIEW
EXU9TABSU                                                    VIEW

ZALBB

原帖由 cow977 于 2008-4-8 09:40 发表
请问ＬＺ，这是啥需求？

同事请求帮忙，我们只负责提供技术支持，不问需求．

不过随机功能，应该是很有用的．

ZALBB

原帖由 joyw 于 2008-4-8 09:29 发表
用sample 行不?

select *  from test sample(10);

ORACLE 的官方文档里，哪个地方介绍了此方法？　sample 里的种子，与产生随机数存在什么关系？

mihawk

原帖由 ZALBB 于 2008-4-8 09:48 发表


ORACLE 的官方文档里，哪个地方介绍了此方法？　sample 里的种子，与产生随机数存在什么关系？

在select语句的sample子句里面

sample_clause

SAMPLE [BLOCK] ( sample_percent )

sample_clause
The sample_clause lets you instruct Oracle to select from a random sample of rows from the table, rather than from the entire table.

See Also:
"Selecting a Sample: Examples"


BLOCK
BLOCK instructs Oracle to perform random block sampling instead of random row sampling.

See Also:
Oracle9i Database Concepts for a discussion of the difference


sample_percent
sample_percent is a number specifying the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to (but not including) 100.

Restrictions on Sampling During Queries
You can specify SAMPLE only in a query that selects from a single table. Joins are not supported. However, you can achieve the same results by using a CREATE TABLE ... AS SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. If you wish, you can write additional queries to materialize samples for other tables.

See Also:
"Selecting a Sample: Examples"


When you specify SAMPLE, Oracle automatically uses cost-based optimization. Rule-based optimization is not supported with this clause.


--------------------------------------------------------------------------------
Caution:
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.



Selecting a Sample: Examples
The following query estimates the number of orders in the oe.orders table:

SELECT COUNT(*) * 100 FROM orders SAMPLE (1);


The following example creates a sampled subset of the sample table hr.employees table and then joins the resulting sampled table with departments. This operation circumvents the restriction that you cannot specify the sample_clause in join queries:

CREATE TABLE sample_emp AS
   SELECT employee_id, department_id FROM employees SAMPLE(10);

SELECT e.employee_id FROM sample_emp e, departments d
   WHERE e.department_id = d.department_id
   AND d.department_name = 'Sales';

ZALBB

收到，多谢！

joyw

原帖由 ZALBB 于 2008-4-8 09:48 发表


ORACLE 的官方文档里，哪个地方介绍了此方法？　sample 里的种子，与产生随机数存在什么关系？

表中数据采样!  但并不精确取多少条, 只能个大概百分比

kelsoncong

哈哈,有点意思

烟囱

sample,学习下了.