Datastage commit per 1000 rows icw materialized views

rverharen · Post by **rverharen** » Fri Jun 08, 2007 3:14 am

I am about to use materialized views in Oracle. A property is to refresh the view when a commit is given on the master table of the view (or on demand). If i have a datastage job which gives a commit after each 1000 rows on this master table, is this going to give me problems with the view?
If so i have to make a job which uses the command to refresh the view manually.
At the moment i am working on a impact analysis for a request so i hope someone already knows what will happen.

chulett · Post by **chulett** » Fri Jun 08, 2007 6:02 am

If your MV is set to refresh on every commit, then don't issue intermediate commits. Leave the Transaction Size at its default of zero, which means only commit once at the end.

Doesn't sound like making it a 'manual' step is an option for you. You must commit the changes in your job which will then force a refresh.

rverharen · Post by **rverharen** » Fri Jun 08, 2007 6:48 am

chulett wrote:If your MV is set to refresh on every commit, then don't issue intermediate commits. Leave the Transaction Size at its default of zero, which means only commit once at the end.

Doesn't sound like making it a 'manual' step is an option for you. You must commit the changes in your job which will then force a refresh.

Hi Graig,
Transaction size can't be set to 0 because that will really cause the performance to decrease because of the large recordsize (and an collapsing rollback segment which can't be extended).
At the moment I define which sort of MV i make (fast on demand or fast on commit).
Because fast on commit is no option because of the many commits in the job it has to be fast on demand.
So the manual step (fast on demand) is the best option.
Thainks for your answer Graig, it helped me getting to the best solution.