Oracle random number and random sampling

1 Take random number

OralceAll operations on random numbers are encapsulated in thePL/SQLpackageDBMS_RANDOMIt is very convenient for us to use.It has the follow function s：

Among them, initialize, random, terminateFunction inOracle11gIt is not recommended in, but chiefly used for back compatibility. The stick to is an exemplar of each function。

1.1 INITIALIZE

Initialize with a seed valueDBMS_RANDOMBag .
By default, DBMS_RANDOMThe software is initialized according to the drug user, time and school term. In this way, tied for the lapp argument, the values generated each prison term will be different. however, this will cause a trouble. In the test environment, if I want to generate the same random sequence each time, what should I do ? INITIALIZE work By setting the same seed prize, the random sequence generated each fourth dimension will be the lapp .
grammar :

DBMS_RANDOM.INITIALIZE (val IN BINARY_INTEGER);

give an exemplar :

BEGIN
  DBMS_RANDOM.INITIALIZE(100);
  FOR I IN 1 .. 10 LOOP
    DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.RANDOM);
  END LOOP;
END;

View Code ————————–
163284779
751599369
659804475
1131809137
-865013504
-407075626
2128226600
-448154892
-1371178596
472933400
even in different sessions, different users, randomly generated10All the values are the same .

1.2 NORMAL

NORMALFunction returns a set of numbers that are normally distributed. The standard deviation of the normal distribution is1The expected rate is0. The prize returned by this routine contains68 % Is between-1And+1between,95 % be situated between-2And+2between,99 % be situated between-3And+3between .
grammar :

DBMS_RANDOM.NORMAL RETURN NUMBER;

give an exemplar :

SELECT DBMS_RANDOM.NORMAL FROM DUAL;

————–
0.321082787751054

1.3 RANDOM

The range of return values is : [ -2^31, 2^31 ), returns an integer .
grammar :

DBMS_RANDOM.RANDOM RETURN binary_integer;

give an case :

SELECT DBMS_RANDOM.RANDOM FROM DUAL;

————-
1632572475

SELECT ABS(MOD(DBMS_ RANDOM.RANDOM, 100)) from dual; -- get random integers from 0 to 100

————–
51

1.4 SEED

Functions andInitiate processSimilar, in fact, Initiate processElimination, recommended substitutionprocessThat isSeed store procedure, AndInitiate processThe difference is thatSeed processBoth numeric and fictional character values are supported as seed values, whileInitiate processOnly numeric values are supported .
grammar :

DBMS_RANDOM.SEED (val IN BINARY_INTEGER);

DBMS_ RANDOM.SEED  (Val in VARCHAR2); -- the maximum range of VARCHAR2 is 2000

give an model :

BEGIN
  DBMS_RANDOM.SEED('hello');
  FOR I IN 1 .. 10 LOOP
    DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.VALUE);
  END LOOP;
END;

View Code

——————-
58
71
33
4
39
53
93
37
20
5

5. STRING

randomly generated string，Grammar :

DBMS_RANDOM.STRING(
      Format of opt in char, - string
      Len in number -- length of string
) RETURN VARCHAR2;

–opt The format of the string：
– ‘ uranium ’ or ‘ U ’ : a string of capital letters
– ‘ lambert ’ or ‘ L ’ : string of small letter letters
– ‘ a ’ or ‘ A ’ : any subject insensitive string
– ‘ x ’ or ‘ X ’ : a string of letters or numbers in any case
– ‘ p ’ or ‘ P ’ : any array of output characters
give an exercise :

SELECT DBMS_RANDOM.STRING('u', 10) VALUE FROM DUAL;
--------------------
PSXFAKZZTR

SELECT DBMS_RANDOM.STRING('l', 10) VALUE FROM DUAL;
--------------------
elnircffly

SELECT DBMS_RANDOM.STRING('a', 10) VALUE FROM DUAL;
-------------------
vGuYnPoZNk

SELECT DBMS_RANDOM.STRING('x', 10) VALUE FROM DUAL;
--------------------
LH7Q36NLPR

SELECT DBMS_RANDOM.STRING('p', 10) VALUE FROM DUAL;
--------------------
IuX4B8lQ9p

View Code

6. TERMINATE

After useDBMS_RANDOMAfter packing material, use theprocessTo end. Theprocessstay11gR1It is not recommended to use .
grammar :

DBMS_RANDOM.TERMINATE;--For compatibility with 8.1

7. VALUE

This serve is most normally used, and its grammar by：

DBMS_RANDOM.VALUE
   Return number; -- [0,1], with 38 decimal places
 
DBMS_RANDOM.VALUE(
       Low in number, - Minimum
       High in number -- Maximum
) RETURN NUMBER;

give an example :

SELECT DBMS_RANDOM.VALUE FROM DUAL;
----------------------
0.452943599091639 

SELECT DBMS_RANDOM.VALUE(10, 20) FROM DUAL;
------------------------
18.4659055244849

2 Random data

OracleGenerally, there are two methods of random data collection

2.1 Fast random data acquisition

useoracleOfsample ( [ sample_percent ] ) orsample bloc ( sample_percent ) Methods ( sampling table scanning ) ( sample board scan ) ），Fast random data acquisition，Recommended：

SELECT * FROM EBILL_ ELECTRONIC_ Bill sample (1) where rownum < = 10; -- the parameter indicates the sampling percentage. By default, the first 10 items are selected

 
similarly, sample_block：

SELECT * FROM EBILL_ ELECTRONIC_ Bill sample block (1) where rownum < = 10; -- Sampling percentage of data storage interval of parameter table, required

 
We should pay attention to the follow points :
1.sample_percentIs a number that defines the percentage of records in the consequence set to the total number of records. The value should be in [ 0.000001,99.999999 ] between .
2.sampleIt is only effective for single table and can not be used for table connection and remote control postpone
3.sampleWill makeSQLAutomatic useCBO（Cost based optimizer )

2.2 Random data

Using DBMS_ random.valueTake data randomly and scan the whole table（Full board Scan）, slow, not recommended :

SELECT *
  FROM (SELECT * FROM EBILL_ELECTRONIC_BILL ORDER BY DBMS_RANDOM.VALUE)
 WHERE ROWNUM <= 10

generator : https://coinselected.com
Category : crypto topics