Re: Iteration-Problem

Alex wrote:
> Hello NG,
>

[]

> But even now, there are far too many datasets left, to do process the
> operation within reasonable time.
> At the moment, I've to live with that...
>
> BUT:
> In some cases, I'd realy like to speed up the process by just inserting
> a given few and re-calculate just those, who depend on these.
>
> Question:
> Can I use the following:
> MERGE INTO TAB_B
> USING ( SELECT * FROM TAB_A TA
> WHERE NOT EXISTS ( SELECT 'x' FROM TAB_B TB
> WHERE TA.KEY = TB.KEY )
>
> I'm not sure if I can actually use such statement.

If you only want the INSERTS, then only do the inserts: INSERT INTO TAB_B ( SELECT * FROM TAB_A TA

         WHERE NOT EXISTS ( SELECT 'x' FROM TAB_B TB
                            WHERE TA.KEY = TB.KEY )

> It might be permitted (cannot check now, due to running transaction) or
> produce something bad...
>
> Can anybody tell me, if this is allowed or - even better - if there is
> a better way to accomplish the restriction?
>
> Any help will be appreciated
>
> Alex Sauer

Is the data being merged batch or continuous? If it is a big batch, are there logical groups of records in the batch (even if the "groups" consist of one row each)?

What I am wondering is, can you use a trigger on the staging table (TAB_A) that handles the INSERT/UPDATE into the target table TAB_B? That would do it for each ROW. This would mainly be quicker that the MERGE in the cases where the load into the staging table is fairly continuous. Rather than waiting for the load to complete before you start the MERGE, the trigger fires for each row loaded in the staging table.

  Ed Received on Tue Oct 24 2006 - 11:21:42 CDT