For some reason a few of our text indexes have blown out to about 3Gb.
I'm still investigating why. Is it possible to reduce the physical size
of the index while keeping it online? Is it even possible to reduce it's
physical size without dropping it?

Snewber je napisal:
> For some reason a few of our text indexes have blown out to about 3Gb.
> I'm still investigating why. Is it possible to reduce the physical size
> of the index while keeping it online? Is it even possible to reduce it's
> physical size without dropping it?

Hi Snewber,

You can rebuild indexes online. Syntax:
alter index INDEX_NAME rebuild online.

Best regards,
Primoz
www.twisted-nail.com

www.twisted-nail.com wrote:
> Snewber je napisal:
>
>>For some reason a few of our text indexes have blown out to about 3Gb.
>>I'm still investigating why. Is it possible to reduce the physical size
>>of the index while keeping it online? Is it even possible to reduce it's
>>physical size without dropping it?
>
>
> Hi Snewber,
>
> You can rebuild indexes online. Syntax:
> alter index INDEX_NAME rebuild online.
>
> Best regards,
> Primoz
> www.twisted-nail.com

Addendum: For domain indexes you can only rebuild them if they are
not marked IN_PROGRESS.

also:
http://download-west.oracle.com/docs....htm#CCREF0101
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

1. May be you sync your index too often. In that case use should
optimize the index from time to time (See API for optimization). You
can do this online and this will reduce the size of the index by
eliminating the duplicate tokens in the $I table

2. Use compress 2 for $I indexes. Don't fire the alter on the $I table
directly. See the docs for how to do this for text indexes

3. See if your stoplist is intact. You may wish to introduce more noice
words in the stoplist by analyzing your data (You won't be able to
search using these words though)

Regards,
Pratap

Thanks for the information. Unfortunately I was consulted about the text
indexing and so I now have to review all the parameters to fix things
up. I have to review case insensitive options, no indexing of numbers,
the stemming stuff that includes words of 3 to 6 characters and also the
addition of extra stopwords.

Yes it is the syncrhonising that is causing the blow outs. I just
dropped and recreated the indexes and this resulted in their sized
dropping from gigabytes to a few hundred Mb. I have also implemented
index optimisation first to reduce index size.

What do you mean by compress? Guess I'll look into this.

Also, the doco states not to use the alter index ... stuff for context
indexes, but the alter index ... rebuild ... parameter ... seems to be
the only way to reduce the size of the indexes online, although I
couldn't get the parameter syntax to work correctly so I'm not sure if
this would reduce physical size?





Pratap wrote:
> 1. May be you sync your index too often. In that case use should
> optimize the index from time to time (See API for optimization). You
> can do this online and this will reduce the size of the index by
> eliminating the duplicate tokens in the $I table
>
> 2. Use compress 2 for $I indexes. Don't fire the alter on the $I table
> directly. See the docs for how to do this for text indexes
>
> 3. See if your stoplist is intact. You may wish to introduce more noice
> words in the stoplist by analyzing your data (You won't be able to
> search using these words though)
>
> Regards,
> Pratap
>