Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sdd.hp.com!zaphod.mps.ohio-state.edu!magnus.ircc.ohio-state.edu!csn!milkbones.colorado.edu!rickcole
From: rickcole@milkbones.colorado.edu (Rick Cole)
Newsgroups: comp.databases
Subject: Re: how bad is oracle? (really: query optimizers)
Message-ID: <1991Feb28.025027.7928@csn.org>
Date: 28 Feb 91 02:50:27 GMT
References: <1991Feb3.182558.14411@oracle.com> <70683@microsoft.UUCP> <13512@blia.sharebase.com> <1991Feb19.204354.16211@informix.com>
Sender: news@csn.org (news)
Reply-To: rickcole@milkbones.colorado.edu (Rick Cole)
Organization: Colorado SuperNet Inc.
Lines: 37
Nntp-Posting-Host: milkbones.colorado.edu

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?

Late bound variables can also create nasty problems for compile time
optimizers. 

Perhaps a bit of run time code could detect when the state of the database had
changed sufficiently to warrent run time reoptimization.  Many database systems
already reoptimize at run time when indexes necessary to a compile time
plan have
been dropped.  Of course, detecting the existence of new interesting
indexes, 
significant changes to data, unexpected late bound variables, and
whatever else
might degrade the performance of a compile time plan would also effect
run time
performance.  Maybe a database daemon could reoptimize compile time
plans before
they might be needed for execution?