Re: Oracle Text Indexes

On May 27, 6:07 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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-Hidequoted 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- Hide quoted text -
>
> - Show quoted text -

Thanks a lot for clarifying.

Prem Received on Tue May 27 2008 - 05:11:47 CDT