Re: Sybase vs Oracle - which is better?

Mark Powell wrote in message
<01be1493$73f2e9c0$a12c6394_at_J00679271.ddc.eds.com>...
>Michael's post below is a nice example of academic knowledge, but I would
>like to take issue with a couple of points.

Thank you. My knowledge is not merely academic, though. I recognize that scrolling through data is a very frequent requirement for database applications. I'm just not completely satisfied with the way cursors are implemented to use them for that purpose, although you can often get away with it.

> >> 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. <<

I would add that a single row, or a screen's worth of rows is still a relation.

>Nonsense. The reason my customers selects sets of data from the database
>is normally to get their hands (so to speak) on the individual rows that
>meet their selection requirements.

Obviously this is true (except for the nonsense part, IMHO).

>Any rdbms that does not provide a means
>of dealing with the individual members of a result set is sadly lacking in
>real world usability.

As is this.

>It is pretty common for a GUI front end to select a set of rows for display
>then the user scrolls through the result set and modifies one or two rows.

Of course.

>With PL/SQL explicit cursors I can do this totally in the database
>environment.

But is it the best way to do it? Usually it is not, although it is the easiest. Cursors usually magnify problems with data currency or concurrency. As I said, it is possible to construct a cursor that is relational, meaning that the scrolling is done through a sequence of sql selects. Otherwise cursors can begin to loose their currency as soon as they are opened. I am only vaguely familiar with Oracle cursors; I think they probably work better than Sybase because the locking problems are not as onerous and I believe they scroll backwards, but what happens if a row is inserted inside the scope of the cursor after it is opened? If it becomes part of the cursor, and locks are not held on any data that is not currently being processed, then I feel that the Oracle implementation is functionally the same as a relational cursor; otherwise not. Furthermore, cursors are usually cached in the frontend in a way that makes it impossible for them to scroll through inserts or deletes that are within the scope of the cursor but executed outside it.

>>> 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. <<
>
>How about using:
> 1) select ((column1 + column2) * 1.5) "Calculated_Value" from ....
> or for a complex formula a user written function could be selected
> 2) define a database trigger to do the calculation and store it
> 3) write a coordinated sub-query

What if the formula is different for each row? You would be storing a different UDF for this value. I think an Oracle select trigger could do what you're talking about, but the trigger would have to step back to procedural code, i.e., a cursor to process it, which was my point.

Does Oracle allow you to evaluate char data as a UDF, like the following:

select evaluate_as_udf(column_1) from ...

where column_1 is a char data type that holds a unique UDF? If so, I'm impressed.

Regards,
-Mike Bilhartz Received on Sun Nov 29 1998 - 00:00:00 CST