How to pick different data from the DB while running same script in multiple machines-CodePudding

I am looking for a logic how can we get different data from the table where we are running same testcase around 5 machines.

For example, In my X table i have 50 rows data, so I have to pass one data to one machine when my script is running simultaneously.

In this, anytime I run the five jobs, my select query shouldn't pick the same data for Machine 1 and Machine 2.

CodePudding user response：

Presume that Scott's EMP table represents your table X. Then you could utilize row_number analytic function which sorts data randomly (using the dbms_random.value) and fetch row that ranks as the highest.

For example:

SQL> with temp as
  2    (select ename, job, sal,
  3       row_number() over (order by dbms_random.value) rn
  4     from emp
  5    )
  6  select *
  7  from temp
  8  where rn = 1;

ENAME      JOB              SAL         RN
---------- --------- ---------- ----------
MILLER     CLERK           1495          1

SQL> /

ENAME      JOB              SAL         RN
---------- --------- ---------- ----------
WARD       SALESMAN        1313          1

SQL> /

ENAME      JOB              SAL         RN
---------- --------- ---------- ----------
ALLEN      SALESMAN        1680          1

SQL>

If you implement it in your script, you'd - randomly - choose one of rows. It could happen that you'd actually get the same row twice (or up to 5 times, as there are 5 machines), but - is it probable? Not much.

CodePudding user response：

The easy way to select random data from DB you can use order by random().

select *
from some_table
order by random()

CodePudding user response：

You should use the SELECT ... FOR UPDATE SKIP LOCKED feature for this use case. Only fetched records will be locked by the session and already locked records will not block this select. Oracle uses this behind the scenes for Oracle advanced queuing. Other DB vendors provide similar features. We use FOR UPDATE SKIP LOCKED for increasing throughput by spawning multiple treads/processes executing this same query.