Re: Sybase vs Oracle - which is better?

Hi,

  If anybody wants to move this discussion to Sybase group please inform me. I am not finding discussions of this nature in that group.

  I have been trying to figure out the way Sybase works.

In article <3663D57B.98D5080A_at_t-mi.com>,   Paul Mapstone <paul.mapstone_at_t-mi.com> wrote:
> "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.

Yes, you can.

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

Can you create a temp table within a transaction ? If you want to share it across two stores procedures ?

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

You need temp tables more often because features like inline views, functions are not there.

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

Taking about stored procedures, in Sybase you must use parameters instead of local variables if they are used in queries in the stored procedure. Can you pass all the variables all the time ?

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

1. How do you tune IO ? Can you map a table or index to the disk ? Can you determine reads and writes for the database or server ( in Sybase terms ) ? Is it possible to move datafiles across disks to balance IO ? ( If a raw device is not used. )
2. Shared locks on tables during select is going to cause more waits .
3. Object sizing. The way extents are allocated you will get a fragmented database, unless it defrags it internally, which it does not to my knowledge.
4. How do you configure your database reads, ie, synchronise the database reads with your OS reads ?
5. Partitioning. It only helps in preventing contention for the last page and for parallel queries.
6. User defined functions
7. Backup / restore of individual objects

There are more doubts I would like to clear. Will group would you like to discuss this ?

--
Denny Koovakattu

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