OraFAQ Forum: Text & interMedia

Home » Server Options » Text & interMedia » oracle (10.1.0.3)

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

oracle [message #352780]

Thu, 09 October 2008 09:31

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

Report message to a moderator

Re: oracle [message #352782 is a reply to message #352780]

Thu, 09 October 2008 09:54

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

Report message to a moderator

Re: oracle [message #352827 is a reply to message #352782]

Thu, 09 October 2008 15:47

Barbara Boehmer
Messages: 7683
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?

Report message to a moderator

Re: oracle [message #352896 is a reply to message #352827]

Fri, 10 October 2008 02:23

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.

Report message to a moderator

Re: oracle [message #353051 is a reply to message #352896]

Fri, 10 October 2008 14:28

Barbara Boehmer
Messages: 7683
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

Report message to a moderator

Re: oracle [message #353059 is a reply to message #352896]

Fri, 10 October 2008 15:40

Barbara Boehmer
Messages: 7683
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

Report message to a moderator

Previous Topic:	Domain (Text) Index for Table join
Next Topic:	ctx indexes and performance problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 19 07:13:48 CDT 2013

Total time taken to generate the page: 0.10728 seconds