Re: Overriding the optimizer

On Sat, Dec 17, 2005 at 07:31:40AM -0500, Jaime Casanova wrote:
> > > Yeah it would - an implementation I have seen that I like is where the
> > > developer can supply the *entire* execution plan with a query. This is
> > > complex enough to make casual use unlikely :-), but provides the ability
> > > to try out other plans, and also fix that vital query that must run
> > > today.....
> >
> > Being able to specify an exact plan would also provide for query plan
> > stability; something that is critically important in certain
> > applications. If you have to meet a specific response time requirement
> > for a query, you can't afford to have the optimizer suddenly decide that
> > some other plan might be faster when in fact it's much slower.
>
> Plan stability doesn't mean time response stability...
> The plan that today is almost instantaneous tomorrow can take hours...

Sure, if your underlying data changes that much, but that's often not
going to happen in production systems (especially OLTP where this is
most important).

Of course if you have a proposal for ensuring that a query always
finishes in X amount of time, rather than always using the same plan,
I'd love to hear it. ;)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461