0

As mentioned in a recent question of mine, I'm new to Oracle:

Running Oracle's sqlldr on VirtualBox VM from my MacBook (client) machine

I'm back, this time with a question about the best way to select some rows of a VIEW and then process each of these rows in parallel.

First, my setup:

  1. I'm running SQL Developer on my MacBook.
  2. My database is in a Linux VM installed using VirtualBox.
  3. I'm using Python to query a VIEW that I've created in the database.
  4. My VIEW is named 'myview'.

Next, here's what I've been doing in Python to process rows of 'myview':

The Python query of 'myview' goes something like this:

import cx_Oracle
conn = cx_Oracle.connect("JUSTIN", "oracle", "localhost:1521/orcl", encoding="UTF-8")
conn.version
cursor = conn.cursor()
cursor.execute("select * from myview order by case_id")

I want to run some function (or series of functions) on each row of the returned 'cursor' object. In Python, I can process each row at a time using something like this:

# Serial version

for row in cursor:
    row = cursor.fetchone()
    # Run desired functions on 'row' object:
    f1_out = function1(row) # <-- This function computes some values
    f2_out = function2(f1_out) # <-- This function writes a file
    f3_out = function3(f1_out) # <-- This function writes another file
    # ... etc.
# Parallel version (example taken from link below this code block):

import asyncio
import time

def background(f):
    def wrapped(*args, **kwargs):
        return asyncio.get_event_loop().run_in_executor(None, f, *args, **kwargs)
    return wrapped

@background
def process_a_row(row):
    # Run desired functions on 'row' object:
    f1_out = function1(row) # <-- This function computes some values
    f2_out = function2(f1_out) # <-- This function writes a file
    f3_out = function3(f1_out) # <-- This function writes another file
    # ... etc.

for row in cursor:
    process_a_row(row)

https://stackoverflow.com/questions/9786102/how-do-i-parallelize-a-simple-python-loop

Finally, the question...

I feel like there ought to be some way to do this server-side. For instance:

  1. Are there PL/SQL functions that can be parallelized on all the nodes/cores that Oracle has access to?
  2. Are these functions expressive enough to do things that Python can do, such as calling water property libraries, performing calculations with them, and reading and writing files to disk?

The reason I ask is because any user's client machine will have only 1 node and therefore much fewer cores across which to parallelize the row operations. But, perhaps, by running server-side, I can get a hold of multiple nodes of cores? In practice, does any one user have carte blanche access to multiple server nodes? Does Oracle natively span multiple nodes on a server, or is that a special DBA configuration option?

Thoughts? How would (do?) you do this?

Thanks! Justin

1
  • The Dbms_parallel_execute package is perfect for parallelising pl/sql, it splits up a table into chunks and runs a pl/sql block against each chunk. The chunks get picked up by job processes which safely mark the chunk as processed when complete. You can mostly write your own pl/sql to replicate any library, if there’s a Java one available you can do load it directly into pl/sql, if you upgrade to 21c (not available yet on premises) then you get the Multi Language Engine, allowing you to embed JavaScript in pl/sql. Mar 13 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.