Re: why index is not used in query execution plan ?

Mon, 16 Sep 2002 15:06:38 +0400, Vladimir M. Zakharychev said (and I quote):

> joins over nested loops and indexed access. After all, total running time is what
> matters in this mode - if we can avoid index access and get results in reasonable
> time through FTS - then why not? Besides, note that CBO assumes that indexes

I do have a major problem with this "FTS is good" approach. Nothing to do with your reply, but as a general principle.

Indeed, if the system is not used by anyone else and there is no other activity going on, a FTS and a hash join is probably a good way of getting the results of an operation that has to read a large portion of a large table.

But in conditions of normal pressure, humidity and temperature, ie, with a stressed buffer cache and a load of users on the system, the VERY LAST thing you wanna do is a FTS of a large table. It will be a virtual green light for all sorts of problems. And if that table happens to also be updated here and there during such a period, you have the conditions for a 1555.

Yet, if you set ALL_ROWS this is EXACTLY what the optimiser will do: it will blissfully ignore that the system is under heavy use, that the table may be receiving updates, that it is too large a table to FTS at a time when the buffer cache is under heavy demand, and so on. Ie, the optimizer doesn't give a hoot about the current conditions of use, it just assumes that you have ideal lab conditions. Bad karma.

And one of the reasons I don't like hash joins and their "FTS attack" under any conditions other than blue moon, wind from the back, light rain, etc.

You get my drift. ALL_ROWS may be nice for overnight batch reports. During the day and in a reasonably active system, it's asking for trouble...

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam