Re: Sybase vs Oracle - which is better?

From: P. Larsen <plarsen_at_dc.dynares.com>
Date: 1998/12/01
Message-ID: <740ue7$7ng11@news.uscg.mil>

Hi Paul,
Thanks for your post ..

Paul Mapstone wrote in message <3663D57B.98D5080A_at_t-mi.com>...
>"P. Larsen" wrote:
 

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

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

As far as my knowledge of SQL-92 goes, the first is NOT supported. It seems to me that we are talking about different syntax to solve the same matter. The EXISIT is a co-related subquery. So the original statement is wrong. And to answer your question, yes Oracle only supports the later statement. A DELETE (FROM) can only be targeted ONE table at a time.

However, using views and "instead of" triggers, you can delete in any obscure way you want. Even just using normal triggers will allow you to cascade your delete onto several individual tables from ONE statement. And using constraints, it's possible to do this even without triggers (not recommended on VLDB).

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

As a cursor declare ...
CREATE OR REPLACE proc (p_i IN INTEGER) AS   CURSOR c1 is SELECT ...... FROM A,B,C WHERE A.i = p_i; BEGIN
  FOR w1 in C1 LOOP

     if valid(w1) then
        PROCESS (w1);
     end if;

  END LOOP;
END proc;

Simple ... like that (valid returns boolean if the row is to be processed and returns the cursor record values too which might have been modified by the valid function). No need to store the temporary result. Oracle takes care of that for me. This really shows to me, as an non Sybase programmer, the difference in "religion" and approach. To me, thinking of temporary tables still "smells like" thinking of files. And THAT is a relational database misconception I've seen in many Oracle programmers ... resulting in many program problems and strange designs.

>> >>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 don't work with JDBC so I'm sorry I can't help you there. I do work with Oracle Objects for OLE which has this ability (returned as a Dynaset).

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

In Oracle I'll use hints - instructing which index to use on a particular table - or even how to use the index in a particular query (or which index NOT to use). Hints are an important part of the general design of SELECTs in OLTP applications - of the excat reason you show above: Controlling the performence tuning process.

So I won't have to reindex my table - the exiting queries will not be touched by an additional index. If I keep my table/index statistics up-to-date Oracle should even refer from using "strange" indexes itself. Of course SHOULD is always the problem - sometimes the optimizer of Oracle makes VERY strange choises.

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

To a certain degree I agree with your last statement. Hence the reuse of code. Embedding everything in stored procedures/objects is just not the way forward. To me that is taking everything back to before Relational databases. The idea that I have to join several times to just fetch from the base table, because only one package is available for fetching which insists of taking all foreign keys values back, is a dead beat solution. And you'll end up with procedures with so many flag-controls that just making the SELECT in the first place would have been much faster and easier to maintain/understand later. It's a ballance act.

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

Correct - but the statement I replied on did not specify what kind of application we were talking about. Just that choosing the "inconsistent" copy was preferable to the "old". And you are giving the answer above - it all depends! Which is the conclussion of this whole talk of Oracle vs. Sybase ... IT ALL DEPENDS. Both have their pros and cons. My beleive is that Oracle is better suited for Web based applications due to the direction the Oracle database is taking and the tools that are following it. Oracle to me is easier expanded if required. But Oracle requires a higher skilled force of labour than most other databases. To program in Oracle Developer you'll need at least a years experience in order to make at least deasent designs, and even more if you're using Designer. And that's the problem with Oracle - it requires a high learning curve - which Sybase and M$ SQL Server doesn't.

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

Again - thanks for your reply. I appreachiate any information about "other" vendors I can get.

Peter Larsen
Senior Oracle Consultant Received on Tue Dec 01 1998 - 00:00:00 CST