Re: Select Distinct over Database Link

> >> 1) Prevents rollback segment from growing enormously. With inserts, as
> >> in this case, that is not the biggest problem.
> >
> >In my books, committing inside a loop over cursor in Oracle (and I should
> >emphasize this, in Oracle), is a very straightforward way of getting
> >ORA-1555. By committing in a loop you allow rollback blocks needed for
> >maintaining read-consistent view for the cursor to be overwritten. This
> >alone makes such intermediate commits a bad practice.
>
> Nope. When batch updating/inserting millions of rows in a table, a
> read-consistent view will very often (though not always) do more harm
> than good, as that view is completely out of time....
> Anyway, you brought in another (irrelevant) issue here. In the actual
> case, it was a whole new table, so no one needed a read-constistent
> view, did they ?!

Well, Oracle doesn't care if you want read-consistent view or not - whenever you select something, it creates read-consistent view for that select and maintains it. So when you open a cursor, Oracle maintains read consistency for that cursor while it can, and gives you 1555 if it can't. Committing inside a loop over that cursor allows rollback blocks needed to maintain consistency to be overwritten while they are still needed. Don't think this will necessarily affect inserts, as they generate little to no undo, but in general committing in a loop over cursor is still bad practice. Not sure how it will affect distributed transaction, but my gut feeling is that it will be no better than local case.

> >There is v$session_longops view and dbms_application_info.set_session_longops()
> >for this purpose. Your transaction sets its target, whatever it may be, row count or
> >anything, and then periodically updates status using this procedure, and another
> >session can query the view to see your transaction progress.
>
> There is always another way to do things....The one you mention is
> surely sufficient but not as straightforward as 'select count(*) from
> mytable.....

It's as straightforward as
 select decode(sofar, 0, 0, totalwork/sofar)*100 "Percent Complete"   from v$session_longops where opname='MY_LONG_OP'

and you still have to estimate completion time yourself, as with count from mytable :) Besides, it's MUCH faster as select count(*) will full-scan your table, which will take longer and longer as rows will pour in.

> >"Small systems" (do these refer to systems with very limited disk space?)
> >shouldn't be running large transactions - they are just not sized appropriately.
>
> Welcome to the real world...many systems are "under-sized" in the
> sense that *sometimes* you need to run jobs that will take a long time
> to run and must be split into smaller pieces.. You just can't buy new
> hardware every time you need to run a single,large batch job, when the
> system runs fine the other 99,5 % of the time.

In real world disks are getting cheaper each day and I don't think any company running software as expensive as Oracle can't find $100 for a 40G+ IDE drive to allocate a temporary rollback segment for occasional large and long running transaction in a "small system". And large systems shouldn't have any problems with free space. :)

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.