Re: Index compression vs. table compression

Joel Garry wrote:

> Interesting point brought up here. I've long thought Oracle is kind
of
> stingy in it's LOV. For example, I think looking up a product code
you
> should be able to easily filter on various related fields, like "I'm
> entering an order and I need to know the exact part code in division
23
> for the part with a description like '%MARITAL%AID%'," as opposed to
> some form LOVs I've seen where you have to scroll through n-thousand
> parts to get to the right screen.

The last one I saw had 11 different LOV parameters to grab what the user really wanted. It can get out of hand. Of course, size of the tables means nothing in this context.

> So to me, there really is no such
> thing as a small lookup table anymore, I rarely see anything like
> Daniel's example (even a "parameter" table I'm looking at has
thousands
> of rows).

Exactly. My experience as well. There really is no such thing as a "dead horse" table anymore. Any table can be used in a lookup. For that matter, when is a lookup a lookup, instead of a join or sub-query? This is my objection to this kind of arguing and discussion: we are using terms that mean nothing to the CBO, mostly because they are not clearly definable to start with.

> I also liked Richard's recent simplified explanation of the low
> percentage of rows to trigger an FTS, I thought it kind of explained
> the default of the optimizer_index* parameters, although now I need
> another explanation why they need to be changed... :-O

Well, that is part of the problem. The subtleties of Oracle's behaviour at this level of analysis vary so much from release to release and even from patch level to patch level that I feel no one can claim the fundamental answer to all of them. I feel we are somewhat suffering from over-analysis here.

Sometimes it's better to step back, look again and say: "OK, this is getting too specific to this code level. What is the global fundamental applicable rule here and how can it better be understood and explained?"

Because sure as heck I've heard two or three different explanations of CBO behaviour in the last two years. Together with the same number of explanations of how to analyze for the CBO. And believe me: no one has the time to dwell into such detail in their day-to-day work. Real deadlines being what they are.

Just MHO, nyway. Received on Mon Jan 24 2005 - 19:13:13 CST