Re: Sybase vs Oracle - which is better?

"P. Larsen" wrote:
>
> Sorry, but I can't find the original post. Guess it wasn't posted in
> comp.databases.oracle.server :)
>
> >>Paul Mapstone wrote in message <365997B8.6B605638_at_t-mi.com>...
> >>After a gap of 8 years I recently had the misfortune to need to use
> >>Oracle for a while. It has come a long way with its performance, stored
> >>procs, triggers, PL/SQL in the server, etc. However, I noticed the
> >>following important facilities were still missing:
> >>
> >>1. Cannot do a DELETE with a join or correlated sub-query.
> That is an invalid statement.
> Correlated sub-queries are no problems and hasn't been since version 5 where
> I started using Oracle. The join has some restrictions but using a view
> would allow you to delete from the "master" table.

Lets say you have the following tables:
A(i, j, k, ...)
B(i,j)

And you want to delete all rows from A where you have matching rows in B. In TSQL, you can do either of these:

delete A
from A, B
where A.i = B.i and A.j = B.j

delete from A
where exists (

    select *
    from B
    where A.i = B.i and A.j = B.j
)

I am sure you cannot do the former in ORACLE, but can you do the latter? I was told not, but would be happy to hear otherwise.

>
> >>2. No Sybase style temporary tables.
> Correct - but why use them when SQL can do it for you without problems?
> With internal tables in PL/SQL the need I've had in the past to temporarily
> save a few hundred or thousand rows can now be done internally in memory. I
> guess it all comes down to how your technology defines your way to work. In
> Oracle you seldom have the need to create temporary resultsets in a table.
>
> >>3. Cannot easily create and use a table in a stored procedure, temporary
> >>or not.
> Huh? Easily is a way of interpretting and is relying on a subjective
> evaluation.
> Can you give some examples of this?

create proc AAA

    @i int
as

• get some initial results into a temporary table select ... into #snapshot from A, B, C where A.i = @i ...
• other processing e.g. insert/update/deletes from #snapshot ...
• return the results select * from #snapshot

How would you do the above processing in a PL/SQL stored procedure? i.e. create a named worktable, insert some rows into it, do some processing on those rows, then return any remaining rows? Which is simpler the TSQL or PL/SQL?

>
> >>4. you cannot write a stored proc to return result rows (yes, I know you
> >>can return a REFCURSOR, but that is nowhere near as useful).
> And you can return PL/SQL tables (internal tables). This is actually
> interpretted by ODBC as a dataset. A "row of records".

I don's know about ODBC, but it doesn't work with JDBC. You have to use ORACLE specific extensions to handle ResultSet objects from ORACLE stored procedures - the standard JDBC calls do not work.

>
> >>I believe that 1-3 above are the reason why so much Oracle code requires
> >>cursors. Problem 4 results in a lot more SQL being embedded in
> >>applications, rather than in stored procedures, which makes tuning
> >>harder.
> Again, a matter of interpritation. Tuning does not become harder because you
> use SQL instead of stored procedures.

Lets say you have a critical query that would benefit from a change in the indexing of a table. You cannot simply change the indexing without considering the other queries going off against that table. The other queries are far simpler to find and consider if they are all (or mostly all) in stored procedures or triggers, rather than embedded in application programs. Furthermore, it is frequently less work to tune the SQL in a stored procedure rather than change and roll out an application program.

> I would argue, that you have a wasted
> amount of processing because you try to share data access path in a stored
> procedure, fetching and doing calucations that are not really necessary.

True, but I consider the performance advantages a bonus, rather than a raison d'etre. Ease of management is usually more important.

> Working on datasets selected 1 or 10 seconds ago is not an option in OLTP
> systems.

Who says? It all depends what you are doing! There are many situations in OLTP systems where a consistent view that is 10 seconds, 1 hour or even 1 day old is of use.

>
> I have no interest in participating in the rowlocking vs. block based access
> that keeps appearing every couple of months in these groups. I think the
> matter has been argued to death - and all the conclussions end up with: Both
> have their advantages and disadvantages - depending on the situation. DUH!

I agree. Received on Tue Dec 01 1998 - 00:00:00 CST