Re: Holdable cursors in Oracle?

Hillel Eilat wrote:

> Holdable cursors may contribute a lot for applications where massive
> reads are performed followed by successeive store/update operations.
>
> For example - preparing a "cube" for OLAP/ data mining may
> require massive reads before a single store/update is issued.
>
> While doing this read/update sequence repetaedly - it does make
> sense to commit the stored/updated data peridically.
>
> But upon commit - cursors would be closed - unless they are holdable.
> So - without holdable cursors one has to keep track on the cursor position
> and renew it after every commit.

Interesting. But can that kind of problem not be tackled using plain SQL and processing data sets instead?

One thing that does *not* work when dealing with VLTs is row-by-row processing. That, in Oracle terms, means no cursors and no PL/SQL loops processing cursors. A 1ms overhead caused by row processing on a VLT will result in 10's of hours of additional processing time. (been there, done that, have the scars to prove it ;-)

Thus, within the Oracle context, we deal with data sets instead. INTERSECTs, MINUSes, UNIONs, JOINs... that type of thing together with CREATE TABLE NOLOGGING. To get the final data set into the fact table, is just a matter of exchanging the contents of the fact table's partition with that of results table. On the data warehouse that I run, that means less than 2 seconds to move 15GB of data from the interim results data set into the fact table.

I think I do understand the reasoning for wanting to have global holdable cursors, but I disagree with the method that requires such cursors.. but then the closest I have been to Teradata was when a salesman tried to sell it to a client of mine some years ago and they decided to go with Oracle OPS instead. :-)

> Yes - data integrity and stability are not maintained - but this is not
> the issue here.

Yeah... personally I think it is always an issue. The most precious commidity is the data. That needs to be safeguarded and protected. So if you discard certain data integrity checks, you need to balance it somewhere else along the way to ensure that the data still represents the truth correctly and accurately.

Nothing worse than having a corrupted dimension and sitting with a table filled with useless facts as these can not be related to the correct dimension values.

--
Billy