Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sun-barr!apple!voder!blia!nobody
From: nobody@blia.sharebase.com (Nobody at all)
Newsgroups: comp.databases
Subject: Re: how bad is oracle? (really: query optimizers)
Message-ID: <13594@blia.sharebase.com>
Date: 28 Feb 91 16:52:40 GMT
References: <1991Feb3.182558.14411@oracle.com> <70683@microsoft.UUCP> <13512@blia.sharebase.com> <1991Feb19.204354.16211@informix.com>
Reply-To: mike@sharebase.com (Mike Ubell)
Organization: ShareBase
Lines: 30

In article <1991Feb19.204354.16211@informix.com> barrym@informix.com (Barry Mednick) writes:
>In article <13512@blia.sharebase.com> mike@woodstock.UUCP (Mike Ubell) writes:
>>
>>True enough, but: A good DBMS should beable to optimize at compile time
>>and find the best query plan without help from the user and then store
>>the plan so that time critical applications have acceptible performance.
>Without help from the user, yes.  At compile time, perhaps not.
>Consider what could change between compile time and run time:
>  Indexes can be dropped and created
>  The sizes of tables can change
>All the factors used to determine a good query plan may be different
>when the program is run.  Wouldn't it make more sense to optimize
>at run time?

(Gee I hate to keep plugging our features...)
ShareBase III automaticly marks a query for recompile when indices
are changed on any table in the query and the query is recompiled
the next time it is run.  (In fact you can drop and recreate a
table in the query and the same thing happens.)  We do not, but
it would not be too hard, to detect when the sizes of the tables
change "significantly" and recompile the query.  I don't believe that
the fact that the plan may get out of date is a reason not to
compile it.  We have a customer with a query that runs in about 1/3 of
the time it takes to compile it.  (Our older optimizer which did
not do complete searches could not find an acceptible plan and they
had to hand optimize it, they are much happer with not having to
do that anymore.)

			Michael Ubell
			mike@sharebase.com