| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Sybase vs Oracle - which is better?
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
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 Sat Nov 21 1998 - 00:00:00 CST
 |
 |