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

On 21 Oct, 14:18, nis..._at_gmail.com wrote:
> 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;

I forgot to add that the start_background_job function uses dbms_scheduler.create_job to kick off the child sessions. Received on Tue Oct 21 2008 - 08:22:37 CDT