As mentioned in a recent question of mine, I'm new to Oracle:
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:
- I'm running SQL Developer on my MacBook.
- My database is in a Linux VM installed using VirtualBox.
- I'm using Python to query a VIEW that I've created in the database.
- 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)
Finally, the question...
I feel like there ought to be some way to do this server-side. For instance:
- Are there PL/SQL functions that can be parallelized on all the nodes/cores that Oracle has access to?
- 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?