Oracle->> Random function

Generating random data under SQL Server is much more dry, but I'm stupid under Oracle. Not used Oracle, do not know what to do? SQL Server relies on tablesample or checksum (NEWID ()) for random sampling, and Oracle has its own random sampling method.

Find an article here that speaks very well: http://www.cnblogs.com/Athrun/archive/2009/04/25/1443619.html

The following methods are related to random sampling under Oracle:

1) SAMPLE (percent)

2) SAMPLE BLOCK (percent)

3) Dbms_random package, including Dbms_random.random,dbms_random.value

4) Sys_guid ()

It's almost like the above.

In fact, I feel that each has its own characteristics, if the performance of the single theory is certainly the best performance, Oracle's sampling technology to reduce the number of blocks required to scan the process, and sample block (percent) and sample (percent) Better than who performance is not clear, do not know when the Oracle scan is a block to take a row or the entire block to take out the row. I think the performance of the sample block should be better than the performance of sample. However, the discrete distribution rate is much lower.

In fact, as with SQL Server tablesample, the problem with sample is the discrete rate. If we have a high requirement for the discrete rate of a sampled data, it is clear that Dbms_random and Sys_guid () should do better than sample.

Here's a comparison of the differences between sample, Dbms_random.random and Dbms_random.value.

Select *  fromNUMBERS SAMPLE (0.1)Order  byIdASC;Select *  from ( Select *  fromNUMBERSOrder  bydbms_random.random)whereRowNum<=  -Order  byIdASC; Select *  from ( Select *  fromNUMBERSOrder  bydbms_random.value)whereRowNum<=  -Order  byIdASC;

Sample results Output 112 rows, the table row number is 100,000 rows, percent is 0.1, according to reason should output 100 line. proves that sample is still indeterminate for the number of rows. Here again the first 100 lines of separation are extracted. But I believe its performance should be the best and time-consuming to prove it. Moreover, the discretization of the distribution of the two comparative data is almost the same, and I feared that the same block would appear to be more or less the same, the data distribution is similar to the other two. As for the performance of the other two seems similar, basically the same. But here's a blog post that seems to say that random is better than value performance, http://www.blogjava.net/pengpenglin/archive/2009/03/19/206796.html

Results of Dbms_random.random

Results of Dbms_random.value

There are a few things to be aware of

1) sample is valid only for single table, remote table and connection query is invalid

2) sample causes SQL to automatically use the CBO

Here's a comment about the Sys.guid () function in someone else's article

Note:
When the 1 method is used, that is, when using the Sys_guid () method, the same record is sometimes obtained, that is, the result set of the previous query is the same, I looked up the relevant data, some say it is related to the operating system, under the Windows platform normal, the obtained data is random, And in Linux and other platforms are always the same constant data set, some say because the sys_guid () function itself, that is, Sys_guid () will generate a 16-byte globally unique identifier on the query, This identifier consists of a host identifier and the thread identifier of the process or process on most platforms, which means that it is likely to be random, but it does not necessarily mean that it is absolutely absolute.

The general method of obtaining random numbers in Oracle is:

Select Trunc (dbms_random.value (0)) from dual; (An integer of 0-1000)
Select Dbms_random.value (0, +) from dual; (0-1000 of floating-point numbers)

Oracle->> Random function