| 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
|
|
|
|
|
|
| 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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
