Re: Oracle Text Indexes

On May 26, 7:47 am, zigzag..._at_yahoo.com wrote:
> 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,

SYNC_INDEX() does what its name says: synchronizes the index, that is, indexes any pending documents added or changed since last [manual] synchronization. If you chose SYNC ON COMMIT option when creating the index, SYNC_INDEX() is called as part of commit automatically. ALTER INDEX REBUILD effectively drops and recreates the index, parsing and indexing ALL documents, not just new or changed, unless you use some special parameters like REPLACE METADATA, OPTIMIZE or SYNC (which is obsolete in 11g.)

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue May 27 2008 - 05:07:40 CDT