Re: Oracle Text Indexes
Date: Wed, 21 May 2008 14:34:05 -0700 (PDT)
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.
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com- Hide quoted text -
> - Show quoted text -
Thanks a lot. Received on Wed May 21 2008 - 16:34:05 CDT