How to read parent session data - without forcing a commit in the parent

From: <nisfar_at_gmail.com>
Date: Tue, 21 Oct 2008 06:26:08 -0700 (PDT)
Message-ID: <4f30e653-b3e7-46c3-82b8-6cca5bc2e084@d45g2000hsc.googlegroups.com>


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; Received on Tue Oct 21 2008 - 08:26:08 CDT

Original text of this message