Re: Comparing Oracle with Sybase

From: Mike Krolewski <>
Date: Tue, 26 Dec 2000 20:55:17 GMT
Message-ID: <92b0jh$ud1$>

In article <92aafk$d9g$>,   Jining Han <> wrote:
> In article <91odd1$cm5$>,
> Mike Krolewski <> wrote:
> > SQLNet: allows native, fast reliable connections across dozens of
> > platforms. Much better than ODBC or JDBC.
> Hmmm, what do you know about Netlib or OpenClient? Besides, you are
> really mixing things up here between different layers of a database
> system, just as you tried to equal Oracle with AS/400.

Oracle does support DCE ( Distributed Computing Environment ) standards given by OSF (OpenStandardFoundation) with SQLNet8 and Oracle8i. When using this, you are limited to how you can configure/attach to the Oracle database.

I still like Oracle SQLNet. Is it universal to other databases, no. Is it fast and reliable for Oracle, yes it is.

Similarly, Oracle has it own versions of Transparent Gateway. Basically, a one way process. Oracle can read and write to other type of database via the gateway. However the other database cannot access Oracle. Does it address universal connectivity. No. But again, Oracle's model is that you are an Oracle shop interfacing to other systems.

As to comparing AS400 to Oracle, I guess I was shorthanding the AS400 version of DB2. (Rather different from the mainframe version of DB2) And the AS400 people do try to compare the two.

> Oh by the way, have you ever counted the number of unanswered
> and unsolved cases in Oracle Metalink concerning DCD, zombie
> processes?

Are there lots of unanswered question in Sybase's equivalent resource? I am not in control of or trying to defend Oracle's expenditure of resources.

> > PL/SQL : an excellent extension of SQL. Really powerful language for
> > developing stored procedures/functions/packages.
> Heard of t-sql? Talking about developing stored procedures, you can
> almost wrap any sql statements between create proc and go in Sybase.
> wonder if you can say the same thing with Oracle. Now if you need the
> procedure to return a result set...have you done it before in both
> worlds anyway?

Never worked in Sybase. Never said I did. I was stating some of the obvious strong points of Oracle.

On procedures -- yes it is a simple as wrapping the statement into a create procedure. The syntax is different, but as simple.

Resultsets are not very easy in Oracle. Cursors on the other hand are. I am not sure if any of the other databases use cursors. IMHO cursors are as good if not better way to go. Do they transfer well to databases that do not have a cursor or cursor support? No they do not. But then again, how often does one write support across database types.

Recently Oracle has added 'ref cursor' which allows passing a cursor between procedures/functions/packages. Much like pointers in C, it provides benefits and dangers. You can draw your own conclusions.

Again, IMHO PL/SQL is extremely powerful. I would suggest that the fact others often compare themselves to its features indicates it may be the superior product.

> > I am not sure about what is supported in the way of SQL. For
> > SQLServer which was derived from Sybase:
> > does not have MINUS or INTERSECT
> > outer joins cannot have additional conditions assigned to them
> > does it allow more than one arguement in an IN clause
> > update does not allow setting multiple variable at the same time
> > update <table>
> > set ( <field1>,<field2>,<field3>) = (select a,b,c
> > insert lacks control over the order or presence of variables
> > often requires a single field primary key to complete work
> >
> > It would be informative to see what in Sybase complements these
> > features.
> >

> Granted, Oracle has a whole bunch of features, many of them
> undocumented because they are "subject to changes". So you want to
> avoid these "features" as much as you can, just as you are adviced to
> use varchar2 but not varchar, or you'll run into a situation where all
> of a sudden your application stops working correctly.

The above list are not undocumented. Several are part of ANSI standard.

> I am not saying Sybase is strong in every aspect, but think about the
> following:
> 1) Since when did Oracle start supporting hot backup? Please be very
> specific about version and conditions when you need to do incremental
> back:) What if you don't use a catalog?

As far as I know, hot backup was alway available. I recall using hot backup with version 6.0.33 possibly 6.0.27. Does this date me or what? Logging and archiving have been around a long time. Was it every easy to do a restore via a hot backup? I think it still is hard to do.

I am not sure what it means to do a backup without a catalog. If you mean something like moving tables/tablespaces around without the entire database instance, that is a new feature -- 8.1.x. It could have been in 8.0. Definitely not in 7.3.4.

> 2) Do you want to put multiple applications in one database server or
> do you want to have a host of database engines in one box? For
> example, I have a Sybase server instance that hosts 36 applications.
> Do I want to configure 36 Oracle servers? If not, think about the
> complications such as how do you set default schema?

I am not sure what you mean by multiple applications in one database server instance.

Oracle is organized into instances. Each instance has a common SYS area for catalog, security, etc. Each instance has pooled resources such as memory (SGA, PGA, etc ), rollback segments (selectable by application if needed). An instance can have one or more schemas besides SYS. Each schema can be as isolated or integrated with the other schemas as one wishes to build it. By creating a new schema, one is automatically separated from other schema. One could use grants and roles to create separation.

Obviously, one can set up a given piece of hardware to support one or more instances of Oracle.

With MTS, it is possible for multiple processing boxes to access the same instance's data to allow scaling and distribution of processing and fall over capabilities. This means that the total amount of pooled resources are increased. MTS has been around awhile. Obviously in 8i.

All access to any schema or instance is through an application like SQLPlus, Oracle Forms, Oracle Reports, an application server layer, or other client application ( in C or java or whatever). As far as I know, one can connect as many applications/users to the system until you run out of resourses. There are some practical limits.

> 3) Security: the best thing you can use to simulate group is through
> roles, and honestly, I went a lot of pains trying to get this done
> it's a piece of cake in Sybase.

If group is what I think it is, I would say that is a nice feature. However, roles can be used to create groups or any security arrangement. I am presuming that Sybase has grants down to the column level like Oracle. My observation is that the grants/roles works well until you need to destroy a table. Generally you should only be doing this because you designed a system badly. But it happens.

You could use one of the other security features like using LDAP to establish a user as part of a group and therefore logged in as a particular user. Some of the middle tier features may be what you are looking for.

> 4) Since when and what version does Oracle support table partitioning?

Table partitioning is available under 8i. It may have come into being with 8.0. Again definitely not in 7.3.4, the last version of 7. That would make it at least 1 year ago, maybe longer.

> So the question is almost like "who enjoys sex better, man or woman"?
> Jining Han
> Sybase and Oracle DBA
> Sent via

Michael Krolewski
Rosetta Inpharmatics
              Usual disclaimers

Sent via
Received on Tue Dec 26 2000 - 21:55:17 CET

Original text of this message