Home » Server Options » Text & interMedia » Text index fragmented
Text index fragmented [message #152977] Wed, 28 December 2005 04:20 Go to next message
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




Re: Text index fragmented [message #152989 is a reply to message #152977] Wed, 28 December 2005 05:31 Go to previous messageGo to next message
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?
Re: Text index fragmented [message #152996 is a reply to message #152989] Wed, 28 December 2005 05:43 Go to previous messageGo to next message
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.


Re: Text index fragmented [message #153001 is a reply to message #152996] Wed, 28 December 2005 05:54 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Sorry, that is beyond me ...
Re: Text index fragmented [message #153067 is a reply to message #152996] Wed, 28 December 2005 19:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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?


Re: Text index fragmented [message #153090 is a reply to message #153067] Thu, 29 December 2005 00:16 Go to previous message
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
Previous Topic: ctxsrv.exe...10g equivalent for Unix?
Next Topic: Size of Oracle's default thesaurus?
Goto Forum:
  


Current Time: Thu Mar 28 06:24:41 CDT 2024