Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!uunet!apexepa!mark
From: mark@apexepa.UUCP (Mark Richter)
Newsgroups: comp.databases
Subject: Re: how bad is oracle? (really: query optimizers)
Message-ID: <107@apexepa.UUCP>
Date: 28 Feb 91 14:46:04 GMT
References: <70683@microsoft.UUCP> <13512@blia.sharebase.com> <1991Feb19.204354.16211@informix.com>
Organization: Apex Software Corp., Pittsburgh, Pa.
Lines: 28

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?

You are both right. In fact a good DBMS will privide flexibility here.
If I have a fairly non-volatile database, and want lightning fast
performance, I'll want my DBMS to compile and save the whole access
plan at compile time. But when my database changes I'll want to 
reoptimize. And, if my database is volatile I'll want some amount
of runtime optimization on every query. 

So, I continue to be amazed that there is a debate over when optimization
should be done. Ya gotta be flexible. It all depends on how the database
is used! A good DBMS must offer such flexibility to the customer.

----
Mark D Richter
----