0

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.

enter image description here

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

Pavan
  • 17
  • 4
  • At the driver level you could use SELECT statements with WHERE clauses using different conditions. Make sure to use bind variables, see https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html How you pass the different values on each machine will depend on what you want to pass, and how the set up is configured - you didn't give enough detail for us to give a good answer. – Christopher Jones Aug 11 '22 at 05:52

3 Answers3

0

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.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

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

select *
from some_table
order by random()
CR_HONG
  • 13
  • 7
0

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.

nfrmtkr
  • 21
  • 4