Re: Sybase vs Oracle - which is better?

P. Larsen wrote in message <7318fh$4uk1_at_news.uscg.mil>...
>
>>> + Row level locking been for years in Oracle, only recently in
>>> Sybase.
>>True, but only badly written apprications (ie: cursors) need row level
 locking
>>anyway.
>
>Huh?? BADLY WRITTEN applications use cursors? According to my RDBMS
>teorectically knowledge, even C.J. Date refers to Cursors as the only
 access
>you have doing SQL. Internally every SQL you do (at least in Oracle) is
 done
>by a cursor. Using the cursor command only differenciates between implicit
>and explicit cursors. Using host languages as Cobol or C, I need to use
>cursors to access data too.

There is a difference. Using cursors to process data from the point of view of the user of the RDBMS is sidestepping the whole point of an RDBMS: the abstraction of the data within the database to sets, and the manipulation of that data as sets. It is certainly true that the RDBMS uses something like "cursors" to process data; in fact; that is a gross simplification, because the data is processed word by word, column by column, row by row by the machine language as it executes. Even that description is absurdly simplified, and it's much more than I care to think about in the development of a database. It is not valid to say that just because the RDBMS internals operate at a much lower level of abstraction, it is accepatable for us to do so as well. (The same idea applies to the abstraction of data by the application using the RDBMS.) I am sure that most of us have seen the type of code where a 'SQL' programmer used a cursor to process an entire set of data that could have been processed relationally, usually much faster, because the RDBMS is optimized to do it that way.

Is it acceptable to use a cursor to browse data? Maybe. Data that needs to be browsed sequentially almost by definition has some kind of candidate key that can be used to give it order. That key will allow the set to be browsed relationally as a series of sets, obviating (although not eliminating) the problems arising in cursors related to locks and result set currency. Only cursors that retrieve data relationally avoid these problems, because the result set is essentially refreshed with each retrieval operation, and the result set is independent of the state of the underlying table.

I have found that the strongest case for cursors occurs when it is necessary to perform an operation that is not supported relationally in current RDBMSs. An example would be a column whose value is based on a formula unique to each row. It would be nice if a select statement against such a table would result in the formula being solved for each row in the result set, but the reality is that you need something like a cursor to sequence through each row and solve each formula sequentially.

Regards,
-Mike Bilhartz Received on Fri Nov 20 1998 - 00:00:00 CST