Hi Folks,

I have an interesting problem. Basically, I have an oracle job that I
need to speed up as we are exceeding our allocated time window. The
oracle job consists of a large number of small changes across multiple
'source' tables and then one of my functions is called to try and
process this source data in the fastest possible time - in order to
create a single huge table from the uncommitted data. If there is an
error in my processing, I simply raise an exception that causes a
rollback of the source data. So far so good.

Now here's the problem. The 'huge' table that I am building consists
of 2 distinct sets of SELECT queries on the uncomitted source data
tables. There is no problem in doing this serially - other than it
takes us 4 hours which is too long. To speed things up, I wanted to
run the 2 distinct SELECT queries in parallel - and therein lies the
problem. I cannot get parallel SELECT functions to run without them
forcing the source data to become committed. Is there any way to get
around this?? Below is a cut down version of the top level function
that I used to kick off the parallel functions that build the huge
table from the source tables. It works - but we cannot rollback
because the parallel functions force the source data to become
comitted!!! is there no way for a child session to have access to its
parents uncomitted data?

Thanks folks..
Nis.

PROCEDURE testParallel
IS

p_ba varchar2(100) :='CSR';
p_no number :=100;

v_command1 varchar2(200):= 'nis_test_parallel.func1(' || '''' ||
'''' || p_ba || '''' || '''' || ',' || p_no || ')';
v_command2 varchar2(200):= 'nis_test_parallel.func2(' || '''' ||
'''' || p_ba || '''' || '''' || ',' || p_no || ')';

BEGIN

-- When this function is called, then at this point we have got
uncomitted data in tables. These tables are
-- need to be read by

-- The start_background_job command uses the
dbms_scheduler.create_job command to kick off the thread
start_background_job( 'BUILD_TABLE_PART1',
v_command1,
'Background Job to build part1 of the
results table');

start_background_job( 'BUILD_TABLE_PART2',
v_command2,
'Background Job to build part2 of the
results table');



END testParallel;