OraFAQ Forum: Text & interMedia » Text index fragmented

Home » Server Options » Text & interMedia » Text index fragmented

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Text index fragmented [message #152977]

Wed, 28 December 2005 04:20

ankurgodambe
Messages: 45
Registered: March 2005

Member

Hi,
My database version is 8.1.6 and use text index on a huge table. We have a process of recreating the index whenever the fragentation ratio goes above 8. Here is the query used:
SELECT AVG(COUNT(*)) FROM DR$index_name$I GROUP BY TOKEN_TEXT HAVING COUNT(*) > 1;
Whenever we used to re-create the index fragmentation ratio would come down to 3-4.

Recently when the fragmentation ratio reached 8, i recreated the index and instead of fragmentation ratio comming down, it went to 14. I have tried this a couple of times now and the fragmentation ration still shoots up to 14.
Does anyone have clue as to what might have gone wrong. The script for index creation is the same i used to have prevoiosly. There is no change done.
Can anyone help on this?

Thanks
-Ankur

Report message to a moderator

Re: Text index fragmented [message #152989 is a reply to message #152977]

Wed, 28 December 2005 05:31

orausern
Messages: 826
Registered: December 2005

Senior Member

Can u post the table structure and the script to create the index , and the query to determine its fragmentation?

Report message to a moderator

Re: Text index fragmented [message #152996 is a reply to message #152989]

Wed, 28 December 2005 05:43

ankurgodambe
Messages: 45
Registered: March 2005

Member

I have attached the index creation script and table description.
Here is the query to find index fragmentation.
SELECT AVG(COUNT(*)) FROM DR$ITEM_CTXDESC$I GROUP BY TOKEN_TEXT HAVING COUNT(*) > 1;

PS: The extention is sh since it didnt allow to upload sql or txt files.

Attachment: intermedia_index.sql.sh
(Size: 5.42KB, Downloaded 2546 times)

Report message to a moderator

Re: Text index fragmented [message #153001 is a reply to message #152996]

Wed, 28 December 2005 05:54

orausern
Messages: 826
Registered: December 2005

Senior Member

Sorry, that is beyond me ...

Report message to a moderator

Re: Text index fragmented [message #153067 is a reply to message #152996]

Wed, 28 December 2005 19:26

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

What is the exact code that you are using to "recreate" the index? Are you dropping and recreating or altering and rebuilding or synchronizing or what?

Report message to a moderator

Re: Text index fragmented [message #153090 is a reply to message #153067]

Thu, 29 December 2005 00:16

ankurgodambe
Messages: 45
Registered: March 2005

Member

I drop the index and then create it. The script is attached in the previous post.

Thanks
-Ankur

Report message to a moderator

Previous Topic:	ctxsrv.exe...10g equivalent for Unix?
Next Topic:	Size of Oracle's default thesaurus?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 28 13:30:34 CDT 2025