Re: Oracle Text Indexes

On May 21, 9:07 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On May 20, 1:13 am, zigzag..._at_yahoo.com wrote:
>
>
>
>
>
> > I am on Oracle 10.2.0.3 and using Oracle text (and not Oracle
> > intermedia text.). This a third party package so I have no control.
> > There several text indexes of  index_type domain.
>
> > 1. When I look in user_indexes stable it shows me two indexes for a
> > text index X_TEXT_OBJECT_VALUE:
>
> > X_TEXT_OBJECT_VALUE   DOMAIN
> > DR$X_TEXT_OBJECT_VALUE$X  NORMAL
>
> > I do not understand what is this DR$ index.
>
> > 2. When I run command:
> > alter index X_TEXT_OBJECT_VALUE  rebuild;
>
> > Command runs successfully, but when I look at last_analyzed
> > select index_name, index_type, last_analyzed from user_indexes where
> > index_name like '%X_TEXT_OBJECT_VALUE%';
>
> > It does not show me any value in last_analyzed, why? I tried to
> > explicitly analyze:
> > DR$X_TEXT_OBJECT_VALUE$X , that also did not change the last_analyzed.
> > Why?
>
> > I tried some other indexes, there last_analyzed is updated, so not
> > sure when last_analyzed is updated and when it is not updated.
>
> > Thanks in advance, any pointers will be appreciated.
>
> DR$ objects are created internally by Oracle Text to support the
> domain index. Their naming is like this: DR$<domain_index_name>
> $<suffix>. Suffixes denote particular object's purpose and are listed
> in Text Reference. $X index is the index on $I table, which is index
> data table.
>
> You need to collect stats on the Text index itself, not on its
> underlying objects. This should collect stats required for optimal
> Text index performance and more or less precise selectivity
> calculations. For the best results, use DBMS_STATS package rather than
> deprecated ANALYZE command to collect the stats. Also, avoid
> rebuilding the Text index all too often, especially if the indexed
> data is not plain text - this may be time-consuming operation as Text
> will convert and re-parse all indexed documents again. See Text docs
> (CTX_DDL.OPTIMIZE_INDEX in particular,) for details on how to
> housekeep Text indexes for maximum efficiency.
>
> Regards,
>    Vladimir M. Zakharychev
>    N-Networks, makers of Dynamic PSP(tm)
>    http://www.dynamicpsp.com- Hide quoted text -
>
> - Show quoted text -

Waht is the difference between ctx_ddl.sync_index() and alter index ... rebuild;

Does index rebuild includes ctx_ddl.sync_index. I have read Oracle's documents,
still not clear.

Thanks, Received on Sun May 25 2008 - 22:47:10 CDT