RE: full-scan vs index for "small" tables

On 06/28/2006 12:00:17 PM, Cary Millsap wrote:

> > ...need stable sql plans.

> 

> But the whole point of the CBO is that execution plan stability is

> inferior to execution plan adaptation to changing circumstances. As

> Jonathan Lewis points out very well, all it takes is the insertion of
a

> single row to make the True Best Plan change from one execution to

> another.

 

Cary, while True Best Plan(TM) can change from one execution to the

next, there are also some problems that are introduced by this:

 

1) One cannot develop on a smaller machine and expect to have the same 

   execution path on another machine. That makes explain plan statement

   practically useless. The same applies to performance testing. This,
in

   turn, complicates development process immensely.

 

[Cary Millsap] Ah, but you should (in the "must" sense) have exactly the
same plans in test as you have in production. It is through the manual
manipulation of statistics in your test system that you ensure this.
(And we wrote Laredo so that we could Ensure it.) The CBO doesn't care
how big your tables are and how their data distributions work out. It
cares only what you TELL it about your tables, distributions, etc.

 

2) Gathering statistics introduces an element of randomness into
production

   environment. Some data is loaded into a big table in a disorderly
fashion,

   it pushes the clustering factor over the limit and the index is no
longer

   used. Tuning is complex, requires a cooperation from development and
is 

   generally regarded as an exercise in futility, as some future data
change

   will send us all into an overdrive yet again.

 

[Cary Millsap] But if such randomnesses are actually present in your
system, shouldn't you be thankful that CBO is smart enough to adapt to
them? The tacit assumption behind your point seems to be that WE know
the best plan, and that plan is always the same. It's not. The best plan
for a query on a big table that is loaded in a disorderly fashion may
actually be different than the best plan for the same big table loaded a
different way. RBO is too dumb to notice. CBO notices, but then we yell
at it for being smart. What I think people really complain about is that
the plans change sometimes without their being warned about it. Again,
this is why we wrote Laredo. Why guess? ...When You Can Know.

Other related posts: