Path: utzoo!attcan!uunet!snorkelwacker!apple!voder!pyramid!eric
From: eric@pyramid.pyramid.com (Eric Bergan)
Newsgroups: comp.databases
Subject: Re: Oracle vs. Sybase on Sun 4:  That much difference!?
Message-ID: <106035@pyramid.pyramid.com>
Date: 19 Mar 90 16:09:37 GMT
References: <1990Mar14.205620.11636@lia>
Reply-To: eric@pyramid.pyramid.com (Eric Bergan)
Distribution: na
Organization: Pyramid Technology Corp., Mountain View, CA
Lines: 46

In article <1990Mar14.205620.11636@lia> thomas@lia.UUCP (Chris Thomas) writes:
>We are evaluating a number of database products for inclusion in a
>distributed network of a few hundred Sun 4s and SPARCs, and I've
>noticed some amazing differences in performance between Oracle 
>(version 6) and Sybase.
>
>In one of our tests, we perform a join between two tables which,
>together, contain 150k rows of 46 bytes each -- about 600 of the rows
>filter through the join.  In Sybase, this completes in anywhere from
>3 to 7 minutes (depending on other load), but Oracle seems to take
>several -hours- to do the same thing.
>
>In running some further tests with Oracle, we've used smaller
>databases (10% of full size, 20%, etc.), and there appears to be
>a sharply exponential relationship between table size and time.

	Almost certainly, Sybase and Oracle are picking different
query execution plans for the join. Sybase has a cost-based optimizer,
which means it tries to calculate the costs of each of the plans, and
then picks the "best". Oracle uses some heuristics based on what the
query "looks like", and goes from there.

	Typically, you can effect the query plan Oracle chooses by varying
the order the tables are listed in the FROM clause, and I also believe
varying the order of the conditions in the WHERE clause will also effect it.
Whether you consider this to be "cheating" or not depends on what your
environment will look like. If it will be lots of ad hoc queries, it is
obviously too much to expect your users to do this. If you are doing
an application with "fixed" transactions, then the re-ordering of 
query predicates may not be a significant deal.

	The exponential relationship just indicates that Oracle is
using an O(n**2) algorithm for the join.  Probably means Oracle is
doing the join first, then the restriction, while Sybase is restricting
first, then joining.

	By the way, I have seen various join-intensive benchmarks where
any given database system beats the others. (I.E. Oracle over Sybase,
Sybase over INGRES, INGRES over Oracle, Informix over whomever, etc.) No
optimizer implementation is 100% effective, and there is always a query
that can trip it up.

-- 

					eric
					...!pyramid!eric