CODEWITHSUNDEEP
javaoracle-databaseparallel-processingprocedure
Levent Tokmak
Levent Tokmak

Reputation: 439

How to execute oracle java stored procedure in parallel

I have a query like this:

select samplePackage.prepareMessage(t.message) as text 
from 
sampleSchema.sampleTable t;

sampleTable has large data (number of rows 30M) prepareMessage is a java stored procedure. 

private static String prepareMessage(String message) {
   //do some things...
   return preparedMessage;
}

I'm trying to execute this query in parallel. How can i do it?

Thanks.

Upvotes: 4

Views: 2295

Answers (3)

Codo
Codo

Reputation: 78795

I've never tried it with Java function myself. But the approach should be as followos:

Run your query with the PARALLEL hint:

select /*+ PARALLEL(t) */ samplePackage.prepareMessage(t.message) as text 
from sampleSchema.sampleTable t;

To successfully execute the SELECT in parallel, Oracle needs to know that your Java function is safe for that. So you have to declare it either as PARALLEL_ENABLE or provide RESTRICT_REFERENCES.

CREATE OR REPLACE FUNCTION PREPARE_MESSAGE(message IN VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE
AS LANGUAGE JAVA
    NAME 'sampleSchema.samplePackage.prepareMessage(Java.lang.String) return Java.lang.String';

The query then becomes:

select /*+ PARALLEL(t) */ PREPARE_MESSAGE(t.message) as text 
from sampleSchema.sampleTable t;

There are further restrictions that apply to the function, e.g. it may not execute DML statements. Otherwise, a parallel execution isn't possible.

As I said: I haven't tried it with Java. But that's the direction to go.

Update: I've changed the code from using a package to using a function. It should be simpler like this.

Upvotes: 4

Michał Niklas
Michał Niklas

Reputation: 54292

Why do you want to do it in parallel? What are you going to do with results?

The only think I think of running it in parallel is to execute it in one thread, then database server will return ResultSet. This ResultSet can be argument for threads that will retrieve part of database response (for example each thread will read 1M rows).

Upvotes: 0

Valchev
Valchev

Reputation: 1530

Use one of Oracle's prepackaged APIs, the DBMS_JOB package, to submit multiple jobs to a job queue and then run them in parallel.

Upvotes: 2

Related Questions