CODEWITHSUNDEEP
sqlshell
dicaprio
dicaprio

Reputation: 733

Execute shell script that use sql query in parallel -

Database : Oracle 11g Server : GNU/Linux Bash Shell.

I have developed a shell script that use sqlplus to connect to the database and select each row from a table and update a column with a value.

I designed this because I had very little data on that table,but now the data have grown to 500K rows. Select and update each record will obviously take long time to update 500K rows.

Is there a way I can execute the script in parallel but each script picks up unique record and update the row ? avoid updating same row by the scripts running parallel?

Upvotes: 1

Views: 3919

Answers (2)

Charlie Martin
Charlie Martin

Reputation: 8406

One of the nice things about Oracle databases is you can use PLSQL (Procedural SQL), which was created precisely for migrations like this. I'm not positive that I completely understand your example, but I think your script would look something like this...

spool name-of-log.log

SET SERVEROUTPUT ON
SET DEFINE OFF
SET SCAN OFF

-- Output the current schema and execution time for logging purposes
SELECT USER

  ||' @ '

  ||GLOBAL_NAME

  || '    '

  || TO_CHAR(SYSDATE,'dd-MON-yy hh24:MI:ss') AS ENVIRONMENT

from global_name;

-- now your procedure..
DECLARE
  -- declare any necessary variables (none needed in this example)
BEGIN
  FOR i IN
  (SELECT dd_no, seq_num
  FROM stagin_table)
  LOOP
  -- do something on i.dd_no, then..
     EXECUTE IMMEDIATE 'update staging_table set dd_no = ' || i.dd_no || ' where seq_num = ' || i.seq_num;
  END LOOP;
END;
/

spool off;

Then just execute your script with sqlplus in your shell script or run it from the command line..

sqlplus>@my-script-name.sql

In theory, this will be faster than calling multiple shell scripts

Upvotes: 0

mousumis
mousumis

Reputation: 383

You could have one script that takes in one or more parameters and updates one row. You could then have another script that calls the first script iteratively in the background. For instance:

updateRow.sh

!#/bin/bash
firstParameter=$1
secondParameter=$2
# ...and so on

# Update table based on input

updateTable.sh

!#/bin/bash
for i in 1 .. N
do
    $WORKING_DIR/updateRow.sh <param1> <param2> & > /path/to/log/file
done

You could of course come up with different logic to do the same thing. Be careful that the script instances running in parallel do not attempt to update the same row.

Upvotes: 2

Related Questions