Re: Iteration-Problem

Ed Prochak schrieb:

> 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

Hello Ed,

thanks for your answer.
I use the MERGE inside a PL/SQL-procedure, so I suppose, the merging is what you call a "batch"-job.

The "line of action" in this case:
I'm importing data from flatfiles into a temporary staging table.
>From there, it is transformed und loaded into a persistent table,
that's TAB_A.
For the purpose of speeding up the querying of the data (the calculation on-the-fly is really a pain in the ...); I'm using an other table as storage for the computed values. That's TAB_B

So far so bad...
Meanwhile, I discovered that my whole approach was rather ill-conceived.

Now it's back to where I started from.
The idea of using Triggers to insert and/or update TAB_B never occured to me, thanks for that one!
But that raises a question:
I'm using MERGE to INSERT / UPDATE TAB_A with the values from the temp. import-table.
Not being inclined to set autocommit on, I commit AFTER the whole MERGE-statment.
Does the TRIGGER still fire for each row? (Dunno how the lines are inserted: en-bloc or line-by-line)

THX so far
Alex

btw.: Are you sure, TRIGGERS for INSERT / UPDATE are faster than MERGE? No strings attached? Special circumstances? Received on Wed Oct 25 2006 - 10:15:25 CDT