Home » Server Options » Text & interMedia » Index fragmentation
Index fragmentation [message #312337] Tue, 08 April 2008 10:43 Go to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, i'd like to know if you could explain me why does everytime that I create a new Text index in my table and execute de ctx_report.index_stats, it tells me that the index is fragmentated(always more than 70/80%). Am I missing something?.

My table contains 5 columns(3 VARCHAR2, 1 NUMBER and a CLOB). The index uses a multicolumn datastore that contains 2 of the VARCHAR2 column's and the CLOB.

The performance when I query isn't as good as I expected also. Could this be the reason?.

Thanks in advance.

Report message to a moderator

Re: Index fragmentation [message #312347 is a reply to message #312337] Tue, 08 April 2008 11:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
In addition to synchronizing your index, you need to periodically either optimize or alter and rebuild or drop and recreate to get rid of the fragmentation. When it synchronizes, it adds rows to the domain tables associated with the index. when you optimize, it deletes the appropriate rows and merges some and eliminates the fragementation. Fragmentation does affect performance.

Report message to a moderator

Re: Index fragmentation [message #312354 is a reply to message #312347] Tue, 08 April 2008 11:31 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, thanks for replying so fast. But I don't understand why does the index becomes fragmented when I've just created it.


Could the table fragmentation be the reason?.
The table has like 120 extents, and I've setted the clob's chunk size to 32kb.

Report message to a moderator

Re: Index fragmentation [message #312384 is a reply to message #312354] Tue, 08 April 2008 13:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
If it is a new index, then you probably need to increase your index memory settings. Please see the following section of the online documentation:

http://download.oracle.com/docs/cd/B28359_01/text.111/b28303/aoptim.htm#CCAPP9274

Report message to a moderator

Re: Index fragmentation [message #312395 is a reply to message #312384] Tue, 08 April 2008 15:10 Go to previous message
Agus211
Messages: 39
Registered: September 2007
Member
I've modified the DEFAULT_INDEX_MEMORY from 12M to 100M(the indexes size is 55M) and it worked great!. Thank you very much Barbara.

Report message to a moderator

Previous Topic: Help in building a Ultra Search Query using Java API
Next Topic: Trying to create Index with Multi column Datastore parameter gives error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 06 23:23:25 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.