Oracle vs Sybase

I am posting this in response to a number of recent requests for such evaluations. The following made its way to me about 6 months ago, via email, when I made a similar request. I hope this stimulates some discussion. I will state up front that this is a biased set of recs since it came from someone who had just made a major Oracle purchase and wanted to document their reasoning to management. - Dan

Technical Recommendations

Oracle V6/7 vs Sybase 4.8

Oracle Strengths

Oracle offers an integrated set of tools along with an industrial quality database server. The integration extends across application development tools, CASE tools, end user tools, the database engine, networking, and even financial and manufacturing applications.

The Oracle RDBMS and associated tools run on the widest range of hardware platforms including MS-Dos, Macintosh, OS2, Netware, Banyan, unix, VMS, IBM mainframes and many more. Oracle also runs on a number of proprietary platforms, including HP MPE, Wang, DG, and others. In total, Oracle runs on over 100 hardware platforms and supports more than 20 networking protocols.

Oracle is by far the largest RDBMS Vendor, with revenues exceeding $1.2 Billion in 1992. Oracle spends more in Research and Development than the majority of its competitors earn in total revenue. Oracle has the largest independant RDBMS market share in the VMS, unix and OS2 server arenas, with 1/3 to 1/2 of their corporate revenue being derived from unix.

Oracle's RDBMS Supports cursors. Cursors are memory structures that keep track of the state of a query and enable results to be passed from the database server to the client upon demand. Multiple cursors can be open at any one time thereby allowing multiple queries to share the same database connection. Cursors simplify application programming, reduce the number of connections the server must manage, and are required as part of the ANSI database standards.

Oracle embraces the Declarative Model for enforcing entity and referential integrity. The Declarative Model is an easy to use, ANSI standard way of defining database objects, and managing the relational constraints between multiple objects. Oracle's Tools take advantage of the Declarative Rules stored in the data dictionary and automatically enforce database integrity in all applications, without programming.

Oracle provides robust support for database backup and recovery. Oracle supports on-line backup, on-line recovery, and point in time recovery. Oracle provides software fault tolerance in that a disk failure does not cause the entire database to fail. On-line backup in Oracle is fast and efficient, and backups can span multiple tape devices. Oracle has production references with databases over 100 Gigabytes in size.

Oracle supports ANSI-Standard pre-compilers for C, COBOL, FORTRAN, Ada, Pascal, and other 3GL languages. Smalltalk and C++ are also supported via C. Pre-Compilers allow developers to embed SQL statements inside 3GL programs. The pre-compiler translates these high level declarations into low level function calls. Pre-  are required for ease of applications maintenance, portability of code, and adherence to open Systems. For example, a Pre-  program written for Oracle needs minimal changes to run against DB2.

Oracle has superior distributed database capabilities. Oracle provides robust support for distributed queries, even among heterogeneous platforms and database managers. Oracle also supports site autonomy and location transparency. In Oracle, tables can be moved from server to server, and even from DB2 to Oracle with no modifications to any application. Oracle also provides low cost, low maintenance turnkey connections to non-Oracle data through its SQL*Connect product line. Oracle7 adds capability for server managed transparent two-phase commit. Transparent two-phase commit is important because no special programming is required by the application developer. Twophase  commit is required for applications which must update more than one database in a single transaction, for example Oracle on unix, and DB2 on MVS.

Oracle has row-level locking for updates, and never requires locks for database queries. With Oracle, application developers do not need to be database theorists and manage locking internally in their applications. In multi-user environments, Oracle provides the highest level of concurrency, with the least amount of overhead. Oracle's locking model also simplifies application maintenance, since additional users or applications will not break hand coded locking schemes as is probable with Sybase.

Oracle SQL*Loader Version 1.1 provides a direct path database loading option for directly loading large amounts of data into database tables. Loader 1.1 builds indexes in parallel while loading data directly into database files. SQL*Loader Version 1.1 loads the database at near disk access speed, and offers excellent performance for large database loads. Sybase also has a database loading utility, but using it requires that the database is shutdown before and after the load or the database is left corrupted. Sybase's data loader cannot build indexes in parallel while loading table data.

Oracle Weaknesses

Oracle has not implemented stored procedures or triggers in its Version 6 release of the RDBMS. Stored procedures are groups of SQL and procedural statements that are compiled and stored in the data dictionary. Stored Procedures provide benefits in reducing network traffic, and increasing performance due to their pre-compiled status. Oracle is adding robust Stored Procedures in the upcoming Oracle7 release of its server.

Oracle has not implemented database triggers in its Version 6 release. Triggers are a method of automatically executing stored procedures and other code and are used to implement complex business rules inside the database server. Oracle is adding very flexible, well implemented support for triggers in the Oracle7 release.

Oracle version 6 uses a multi-server architectures which is either an advantage or a disadvantage depending on the environment. On single processor systems with small amounts of memory, Oracle may be limited in the number of users it can support on the system. In Oracle V6, each user requires about 250k of memory on the database server. On OS2 1.0, for example, memory limitations restrict Oracle to about 32 users due to OS2's 16 Megabyte memory maximum. Oracle's multi-server architecture is a major advantage on unix platforms, and on Symmetric Multiprocessors. The Multi-Server architecture is also an advantage in non-client server environments, where the singleprocess  multi-threaded server is inappropriate. Oracle7 includes a multi-threaded multi-server architecture with all the benefits of both models.

Next Release: Oracle7 is expected to be released in the late summer of 1992. It corrects all of the above listed deficiencies and includes many additional features. A partial listing of new Oracle features includes: a Statistics based Query optimizer, Declarative Integrity, Stored Procedures, Database Triggers, Multi-Threaded Multi-Server, Transparent two-phase commit, Resource Limiter, Event Alerters, Database Pipes, Open Gateway and many others.

Sybase Strengths

Sybase provides stored database procedures. This is a feature of which the advantages are mentioned in a previous section. Sybase stored procedures are hampered by the limitations of Transact SQL, which is much less robust than Oracle PL/SQL. For example, in Transact SQL the programmer must explicitly address all possible error conditions in all possible locations. In PL/SQL, as in Ada and Object Oriented languages, the programmer need only specify exception handlers once within a program. It should also be noted that Sybase remote procedure calls (stored procedures invoked on a remote server) are not protected by two-phase commit and are only appropriate for queries. Oracle remote procedure calls are fully protected by automatic two-phase commit and can be used for both queries and updates.

Sybase supports triggers in the database. The advantages of database triggers are mentioned in a previous section. However, Sybase stored procedures and triggers have several serious limitations. Sybase triggers only fire AFTER Insert, Update, or Delete statements, and only once per statement. This means that with Sybase triggers, an update occurs, then the trigger is fired, and if the trigger fails the entire operation must be rolled back. Oracle Triggers can fire either before or after SQL statements, whichever is appropriate.

Similarly Sybase triggers only fire once per statement, and not for each row affected by the statement. For example, a Sybase trigger cannot keep track of updates to rows in the employee salary table, if more than one employee is affected by an update statement. This is easily accomplished with Oracle triggers which can fire per statement or for each row affected.

Sybase has a single-process, multi-threaded architecture. This is an advantage under OS2 Version 1 as it allows many concurrent connections to the server using a limited amount of memory, approximately 50k per connection. However, the multi-threaded architecture is inappropriate for non client-server applications and does not take advantage of Symmetric Multiprocessors. Sybase Version 4.8, VSA, provides support for SMP machines, but was released early and has been found by customers to be extremely buggy. Version 4.8 was initially released in August of 1991, but has only recently stabilized.

Sybase Weaknesses

Sybase relies almost entirely on C and third party tools for application developers. Sybase has no integrated tools, even the Sybase CASE toolset which was acquired when Sybase purchased DEFT uses third party tools for generation of applications. Relying on third party products leads to version skew problems, and finger pointing between vendors when support issues arise. It is expected that Sybase applications will require near total re-writes when Sybase Version 5 arrives, due to major internal structural changes in the database server. It is unknown how third party application developers will adapt to these changes.

SQL*Server is available from Microsoft only on OS2 in 16 bit mode, with announced intent to port to Windows NT. Sybase's principal platforms are DEC ultrix and VMS, HP, IBM RS-6000, NCR, Sequent, and Pyramid. Microsoft maintains exclusive marketing and sales rights to desktop server platforms for Sybase and is not expected to make a 32 bit version of SQL*Server for OS2 2.0 available in the near future, due to friction in the Microsoft/IBM relationship.

Lack of cursor support. Sybase implements a streams interface between the client and the server. Without cursors, the Sybase programmer must open and manage multiple connections to the Sybase to enable multiple queries to be active at the same time. A modern windows application typically has 5 or more windows open on the screen at the same time, which represents 5 connections or 5 users to the Sybase for each client workstation. This causes locking problems and overhead for the application designer, since each of these connections has the potential to conflict with each other, and with all of the other users of the server. This largely mitigates Sybase perceived memory use advantages - Sybase requires only 50k of memory per connection vs Oracle's 250k, but in Sybase 5 or more connections to the server may be required for a windowed application vs only one for Oracle.

Sybase supports on-line backup, but during backup performance of database transactions slows by 20 or more percent. Sybase does not support on-line recovery; to repair a damaged database the Sybase must be shut down. Sybase does not support point-in-time recovery. Sybase does not support multi-tape backup, which effective limits the size of the database to the size of a single tape cartridge. Sybase can only effectively support databases up to 2 Gigabytes in size, the largest Sybase databases in the world are in the 5-8 gigabyte range on large unix platforms.

Sybase uses proprietary DB-Lib low level function calls in C as the primary database interface. DB-Lib was recently renamed to Open Client by Sybase marketing, but the product was not changed. Sybase does not support ANSI standard pre-compilers, the benefits of which are described above. A program written in C using DB-Lib to access Sybase must be totally re-written to run against DB2, or any other ANSI standard database. This greatly increases maintenance costs and locks programmers into the Sybase proprietary trap.

Sybase has limited distributed database capabilities. With release 4.8, Remote procedure calls can be used to implement distributed queries. However, distributed joins must be implemented programatically by the application developer. This means that every remote query must be hand coded by the application developer, and AD-Hoc distributed SQL is not possible.

Sybase does not have location transparency; when tables or data moves applications must be re-coded. Sybase provides the capability to code two-phase commit protected transactions in C, though the application developer must write and maintain all commit and failure logic programmatically for every transaction. This is an error prone process, Sybase has no referenceable customers using two phase commit because it is so difficult to manage.

Sybase has page level locking which escalates to table level locking after some arbitrary number of page locks has been consumed. Sybase also requires read locks if accurate query results are required. In Sybase, writer lock out readers, and readers lock out writers. Sybase application developers must be cognizant of database locking, and must include deadlock protection algorithms in every application.

Next Release: Sybase System 10. Sybase S10 is a rearchitecture of the underlying database engine to improve Sybase's conformance with ANSI Standards. Sybase S10 will include support for ANSI Cursors, greater compatibility with ANSI SQL Standards, and improved backup  and recovery features. Sybase S10 is intended largely to correct deficiencies in the basic architecture of the previous releases, and is not an innovative release. New features in S10 are distributed replication services, and data navigator, which is designed to parallelize queries across multiple machines. Sybase S10 has been promised in Q1 1993, but there is some uncertainty about timely delivery. It should be noted that Sybase V4.8 on several platforms including Pyramid was announced production in August 1991, but took over a year to be delivered. Similar delays are expected for the S10 release. Received on Mon Mar 08 1993 - 01:53:40 CET