Home » Server Options » Text & interMedia » oracle (10.1.0.3)
oracle [message #352780] Thu, 09 October 2008 09:31 Go to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member

The DR$ index size is growing at a very rapid rate.
we are using the command
ctx_ddl.optimize_index() on daily basis. However it takes a long time and size is too large . While the operation on the system are normal.
We searched for it and find the solution to use below command for the table
alter table <tablename> shrink space cascade.
But the metalink note says that its tested on 10gR2
Is there any alternative available for 10gR1.

Regards
Ankush Chawla
Re: oracle [message #352782 is a reply to message #352780] Thu, 09 October 2008 09:54 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
Its an Oracle Context Index and as per metalink shrink space is not tested on LOB segments for 10gR1.
Kindly help
Re: oracle [message #352827 is a reply to message #352782] Thu, 09 October 2008 15:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
Are you synchronizing before optimizing? If not, then that would explain the slowness. Optimizing without synchronizing first doesn't really accomplish anything. You could always resort to a one-time drop and recreate of the context index, which should drop and recreate the domain index tables, resetting the high water mark. However, once you insert/update/delete more rows in the data table, then the high water mark will rise again in the domain index tables. Is your issue really with the size or with the slowness and you are assuming that the slowness is due to the size?
Re: oracle [message #352896 is a reply to message #352827] Fri, 10 October 2008 02:23 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
We are synchroning the indexes very often every hour but optimization is done everyday.
I am assuming that slowness is due to size .
Concerns are both . Size is consuming disk space and we are getting short of it,and time keep the resources busy for a very long time. I/O performance is very bad in that time period.
System perform slow.
Re: oracle [message #353051 is a reply to message #352896] Fri, 10 October 2008 14:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
It sounds like you ultimately need to acquire more disk space and upgrade to 10gR2 or 11g. Just the lack of disk space could keep it from optimizing quickly. In the meanwhile see if anything in the following link helps you. Also, are you optimizing in fast or full or rebuild mode?

http://download.oracle.com/docs/cd/B19306_01/text.102/b14217/aoptim.htm#sthref779
Re: oracle [message #353059 is a reply to message #352896] Fri, 10 October 2008 15:40 Go to previous message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
Roger Ford gives a nice explanation about the size of the dr$...$r domain index table here:

http://forums.oracle.com/forums/message.jspa?messageID=2809188#2809188
Previous Topic: Domain (Text) Index for Table join
Next Topic: ctx indexes and performance problem
Goto Forum:
  


Current Time: Fri Sep 19 15:29:54 CDT 2014

Total time taken to generate the page: 0.16233 seconds