Re: Should one Analyze the Tables created because of intermedia Indexes or not

A section from From Metalink DOC 139979.1 ................

To Analyze or not to Analyze the Text index tables

Should the interMedia Text domain index tables (DR$ prefixed) and associated indexes be analyzed?

The answer:

No, these DR$...$ tables do not need to be analyzed.

The reason:

The queries we issue on the DR$ tables are very simple. It is usually a select by index key prefix, we do not do joins or anything fancy on them. There is no benefit to having cost-based-optimization of those queries because the rule-based query plan is ALWAYS optimal. The Cost Based Optimizer can only slow them down.

As for the Text (Domain) index, in our example the BOOKS_INDEX index.

A context index that has been ANALYZED will participate in cost-based optimization. A context index that has not been ANALYZED cannot provide selectivity and cost estimates, so it will do rule-based optimization. Thus, ANALYZING the domain index can change the behavior and performance of the context index. Whether or not it is "required" is up to your application.

So:

ANALYZE the domain index if you want to. Do NOT ANALYZE the underlying DR$ tables.

"Ronnie Yours" <ronnie_yours_at_yahoo.com> wrote in message news:<aie6rs$clb$2_at_nntp-m01.news.aol.com>...
> Hi,
> I have some tables which have intermedia indexes on them. These indexes
> create tables such as
>
> DR$HEADLINE_SEARCH$I
> DR$HEADLINE_SEARCH$K
> DR$HEADLINE_SEARCH$N
> DR$HEADLINE_SEARCH$R
>
> I enabled monitoring on them and they show up in the user_tab_modifications
> table when data is added/updated or deleted in the master table(the table on
> which intermedia indexes are created).
>
> My question is that do these tables need to be analyzed also or not.
>
> Thanks
> Ronnie
Received on Fri Aug 02 2002 - 15:22:21 CDT