Re: Sybase vs Oracle - which is better?

From: <denny_vk_at_my-dejanews.com>
Date: 1998/12/04
Message-ID: <747iel$50s$1@nnrp1.dejanews.com>

In article <740ue7$7ng11_at_news.uscg.mil>,   "P. Larsen" <plarsen_at_dc.dynares.com> wrote:
> 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.

In Sybase this would have held a share lock on the table while the cursor was active. What would happen to the benchmark results ?

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

--
Denny Varghese Koovakattu
E-Mail : denny_vk_at_hotmail.com

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    

Received on Fri Dec 04 1998 - 00:00:00 CST