Re: Index compression vs. table compression

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
>news:41d57a50$0$5112$afc38c87_at_news.optusnet.com.au...
>>>
>>> Not if they're being used for doing lookups, I hope.
>>
>> Why?
>> A small table is always likely to be read via a FTS using CBO. Even for a
>> single key lookup...
>>
>
>It's typically more expensive on CPU and latching to use an FTS
>for a simple lookup than it is to use a primary key indexed access.

One more myth disproved?

I must admit that I have also read quite often that a FTS of a small enough table is preferable to index access. I mention this because I suspect that this conviction was at least implicitely promoted by the former versions of the CBO, which did not take the CPU cost into account. So one thing that I have been missing is a way to correlate calculated costs against factual run times, both of which are available from the statspack collections (if using statistics level=6 at least).

At times, I have found that cost calculations tend to be completely unreliable in trying to tune a query beforehand. Just change the value of OPTIMIZER_INDEX_COST_ADJ and the total cost changes dramatically, but the execution plan not necessarily and so the execution time either. (Don't know if Oracle 10g stills allows you to decide how expensive indexes are - that Oracle should know itself!).

Such correlation would allow for corrections of the different cost factors involved in total cost calculations by the CBO (for example: "Are single block I/Os more expensive than Oracle assumes?"). Of course, many more factors are considered by the CBO now and their right weighing must be identified. Having enough data available, this weighing could be done in a straightforward manner using a pure mathematical model.

Just a stimulation for your book - may be Volume 4?

Regards
Rick Denoire

>>> And that is what the *CACHE* clause
>>> does: if you specify it as an attribute of a small lookup table, its
>>> blocks will indeed be read into the hot half of the LRU list, *even
>>> though they were read by a FTS*.
>
> Apart from the detail about the 'hot half' - the conclusion is
> invalid, because a SMALL lookup table will be read into the
> middle of the LRU whether you specify CACHE or NOCACHE.
>
> I think another of your conclusions was this:
>>> All of which boils down to: CACHE/NOCACHE and KEEP/RECYCLE are trying to
>>> do precisely the same thing.
> I think you are the primary complainant about 'sloppy descriptions'.
> This is a very sloppy comment. One of the features of the KEEP
> pool is that it can do a better job for SOME of the stuff that CACHE
> does.
>
>
>In passing - there is a bug that is only fixed in 10g that makes
>the KEEP cache a more appropriate target than the default
>cache if you do really need to do frequent __small__ tablescans
>i.e. tablescans that you would normally expect to 'survive' in the
>default cache.
>
>
Received on Sat Jan 01 2005 - 20:23:17 CST