Re: Oracle Text Indexes

From: Vladimir M. Zakharychev <>
Date: Wed, 21 May 2008 06:07:44 -0700 (PDT)
Message-ID: <>

On May 20, 1:13 am, wrote:
> I am on Oracle 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:
> 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) Received on Wed May 21 2008 - 08:07:44 CDT

Original text of this message