Re: Sybase vs Oracle - which is better?

Can any tell which is better from the price/performance point of view:

Sybase vs Oracle vs MS-SQL on NT

Narayanan G (narayanang_at_earthlink.net) wrote:
: Hi Guys,
:
: I've been working with both Oracle & Sybase over several years in
: various positions. I've worked on Sybase from version 4.x thru 11.9 and
: Oracle since 5.x thru 8.1 and have a fair understanding of both without
: bias for either. Currently, I am working as a DBA supporting both oracle
: & sybase databases at a software company developing products to run on
: both.
:
: As a database designer/dba I've come across numerous limitatoins in
: sybase in comparison to oracle and would like to present a detailed list
: of those. Since we work on oracle and sybase side by side, the
: differences are quite evident and the views are shared by developers,
: dbas and designers. My intention is not to downplay sybase, but to
: share my perspective. I'll greatly appreciate any feedback/suggestions
: if it can make life easier.
:
: Here we go ...
:
: Sequences
: ---------
:
: Oracle has a sequence generator to generate sequential numbers, you can
: specify starting,increment and max values with an option to recycle.
: sequences are not bound to a table and hence can be used to populate
: multiple tables (useful in arc relations when a child table has two
: mutually exclusive parents)
:
: Identity will clearly not suffice, and every sybase experts advices to
: stay away from this.
:
: I am left with maxvalue table option, but clearly this will be a
: hotspot. I need row-level locking here and this table is not my choice
: but the only option I have using sybase.
:
: So much for the cry about "only badly written applications need RLL" !!!
:
: Triggers
: --------
:
: Oracle has before & after triggers at row level and commit level. I
: would like to populate a few columns before insert using some business
: rules.
:
: Sybase has no before trigger, I have to insert and update the same table
: thru the trigger and we all know the problems with that!
:
: RI Constraints
: --------------
:
: Oralce has cascade deletes, even supports deferred constraints in 8.1,
: can enable and disbale constraints as needed.
:
: I have to code cascade deletes in triggers, not a big deal but oracle
: does it for me.
:
:
: Page Size
: ---------
:
: I can use 2K to 16K page size for oracle databases based on DSS/DW etc..
: Oracle also supports "row chaining" so a row doesn't have to fit in a
: page.
:
: I am stuck with a 2K page with sybase, max row size 1952 bytes ? can use
: vertically segment into 2 tables, but sybase can only join 16 tables,
: optimizer will only look at 8 tables by default!
:
: Varchar2
: --------
:
: Oracle varchar2 can go upto 2000 characters, can use all string
: functions, very useful for comments etc.
:
: Sybase varchar is upto 255, anything more use 'text', no string
: functions (like works though!)
: I think sybase is planning something similar to varchar2.
:
: Space allocation
: ----------------
:
: Oracle lets me control extent sizes for tables/indexes using
: initial,next,maxextents ...
:
: Sybase extents are 8K, not a byte more not a byte less, every other 8K
: chunk in my database can belong to a different object. Fragmentation ...
:
: Partitioning
: ------------
:
: Oralce partitioning is based on data in the table, you can have a
: partition for each month and the optimizer is aware of where your data
: is. You can also offline, drop individual partitions without messing up
: anyone. High Availabilty!!!
:
: Sybase partitioning is to get multiple page chains to avoid competing
: for the last page in the chain during inserts. Simply not comparable to
: oracle partitioning.
:
:
: Statistics
: ----------
:
: Pre 11.9, sybase had a 2K distribution page to store statistics, as the
: database grew optimizer had less and less info.
:
: Consistency Model
: -----------------
:
: Oracle supports multi-version consistency model, writers don't block
: readers, readers get a previous version of the data consistent at that
: point of time.
:
: Sybase writers block readers, no timeouts, indefinite waits ...
:
:
: Demand Locks
: ------------
:
: Oracle has "select for update" to acquire a persistent lock.
:
: Sybase "select for update" will work only in a cursor within a stored
: procedure. Have to user "fake updates" to get persistent locks.
:
: Outer Joins
: -----------
:
: Oracle supports ansi standard outerjoins.
:
: Sybase does not, you can't join an outer table to another table and move
: on. Major pain for developers to break the queries into multiple steps
: using temp tables (tempdb hotspot!) How can we do this for dynamically
: generated queries ?
:
: Unions in Views
: ---------------
:
: Very handy in oracle, not available in sybase.
:
: Shared Pool
: -----------
:
: Oracle has a shared sql pool to store currently running sql queries and
: the execution plans, very easy to access and identify poorly performing
: sql queries. Also, subsequent executions of the same sql can reuse the
: execution plan. The sql has to match case sensitive though, if I have
: 200 users banging an application, they are going to generate the same
: sql!
:
: DB Writers
: ----------
:
: Oracle lets me confiugre multiple database writers for a heavy write
: database. Scales well! Any equivalent in sybase ?
:
: Temp Tables
: -----------
:
: Very useful in Sybase.
:
: Has arrived with Oracle 8.1 !
:
: SQL*Plus vs isql
: ----------------
: simply no comparison!
: BTW,
: I can use "whenever sql error ..." in sqlplus, I have to save and check
: @@error after each sql in isql!
:
: Object Relational / Java in the database
: ----------------------------------------
:
: Sybase has promised, Oracle has delivered.
:
: There is still one thing that Sybase has but Oracle doesn't, returning
: result sets from stored procedures!
:
: I can probably go on and on! Again, I am not trying to downplay sybase,
: but these are the problems we have to deal with everyday.
:
: Your suggestions/recommendations are welcome!
:
Received on Sun Nov 22 1998 - 00:00:00 CST