Parallelism in PL/SQL

Question

hi

how can i run one query in pl-sql- in parallel?

i need all the flow...

Answer 1

You can create JOBs in order to run the same query with parallelism.

EXAMPLE

CREATE OR REPLACE PROCEDURE target_deletion
IS
   number_of_the_job   NUMBER;
BEGIN
   DBMS_JOB.submit (number_of_the_job, 'begin stored_procedure_for_deletion; end;', SYSDATE);
END;
/

EXPLAINATION

Please suppose you have, in your Oracle DataBase, a stored procedure called exactly as follows:

stored_procedure_for_deletion

If you with to execute that stored procedure many times with PARALLELISM, you have to create a stored procedure called for example "TARGET_DELETION" (written above), with creates a new job that invokes, with the PL/SQL block:

begin stored_procedure_for_deletion; end;

... the execution of your procedure called "stored_procedure_for_deletion".

The job starts immediately, so you can run the stored procedure target_deletion many consecutive times, in order to run the same procedure with parallelism.

Answer 2

If enabled at instance level, Oracle itself has parallel query features:

http://www.orafaq.com/wiki/Parallel_Query_FAQ

edit: it's not clear what you are trying to do. Maybe you want asynchronous query execution, so the Oracle job suggestion is correct (see the other answer).

Oracle parallel feature is not asynchronous, it just says the optimizer to use a certain number of CPUs in query execution, to speed up the result. For example:

select /*+ PARALLEL(DOGS,4) */ * from DOGS where A>20

executes your query with parallelism at degree 4.