> From: Jim Starkey
> The whole concept of plans is a crock -- a wart on a wart,
> an abomination, a crime against software, an feable excuse
> of a feature to mask a bug in the optimizer.
>

> How would anyone feel about a C compiler that required the
> programmer to tell it how to optimize loops?

Jim, you must be joking when you mention C here!
The most distinctive feature of C is its pointer arithmetic,
which is nothing more than the means of hand-optimizing
(how much C programmers choose accessing "arrays" by index
instead of direct pointer to its elements ?).
Similarly, what is the purpose of "register" or "inline" in C
other than to allow hand-optimization ?
Why programmer can pick from three real types, should not long double
be good enough in all situations ? Etc...

Regardless of how bad (or good :-) plans are, their use is optional.
IB does not _require_ you to use plans to get correct result.

> Or an automobile
> that required a trip profile before it could adjust the
> ignition timing?

Automobiles are much better parallel.
What is the purpose of gear lever !! ?
Should not automobile pick (optimize) proper gear (plan) itself ?
You can have car with automatic gears (=optimizer),
which choose proper gear in most situations, but not always,
so even such cars have gear limiter (plan clause :-) whose purpose
is to override wrongly chosen gear (plan).

> Software can and should optimize itself. It knows more about
> its innards that application programmers. It can estimate
> costs, cardialities, and alternatives. It doesn't need
> a human to tell it what to do. Firebird was designed to
> make the optimizer cheap, simple, and effective. And it
> was until Borland broke it.

IMHO, optimizer can make only as good decisions, as good are initial
informations it work with. Back to our "automobiles model":

salesman in car shop = optimizer,
customer = application,

Customer says
"I need to go from town A to town B, give me the best car, Mr. Optimizer";
at this point, salesman can pick truck or bus or buggy or pick-up or ...
All possibilities are good enough because all are able to get you
from A to B. Now what ?
If the customer is more specific and say he is moving whole house,
salesman will offer him truck.
If he specify he is teacher with 30 children salesman will offer him
school bus. If he specify he is lighthouse keeper on islad B
optimizer will offer amphibian, etc...
Customer can have more important informations that are essential;
If Mr.Optimizer is not aware that part of highway from A to B
is under reconstruction, he will miss to offer off-road car ...

Now back to databases.
What it the task of SQL SELECT statement ?
To retrieve required data.
What it the task of real db application ?
To retrieve required data in acceptable time.

What is acceptable time ?
For report application it can be either shortest total time,
or arbitrary long time provided that other applications will not be slowed down.
For interactive application it can be either shortest time to get
first record, or guaranteed maximal time for the worst query. Etc...

While I do not like InterBase's plans in its current form,
it is clear that combination "SQL + perfect-optimizer" is _NOT_ enough
to always make good decisions, simply because plain SQL is not able
to give optimizer enough informations about application requirements.

> Fix the optimizer. Then dump the entire concept of plans
> forever.

Hmmmm, do you really mean "dump plans", or "dump plans and introduce
different hint system" ?
Somehow I feel "dump plans" is right answer; in this case,
should not indexes be dumped too ? :-)


Ivan
http://www.volny.cz/iprenosil/interbase