Home » Server Options » Text & interMedia » ctx indexes and performance problem (
icon5.gif  ctx indexes and performance problem [message #353174] Sun, 12 October 2008 04:13 Go to next message
Messages: 46
Registered: July 2008

I really have very bad performance problem with my ctx indexes.
after doing these steps I will face the problem:

1-I clean the database and remove all of my test data.
2-recreate(drop and create) the indexes (normal and domain) on my main table.
3-export the schema(user) using expdp tool.
4-import the dump file (schema) to production server this.
5-insert 50000 records to the main table.
6-execute CTX_DDL.SYNC_INDEX command to synchronize its 8 domain indexes.

the step 6 takes 3 hours on my production server just for the first time that I execute the CTX_DDL.SYNC_INDEX and it mainly uses CPU(it is really too slow) whereas performing step 6 in my development server just take 3 min, but if I make my dump file with 50000 records in the main table and continue the other steps and then executing CTX_DDL.SYNC_INDEX it works fine without taking too much time on production server.

I really don't know is there something wrong with my export process or is it normal,any idea about what is wrong?

Re: ctx indexes and performance problem [message #353190 is a reply to message #353174] Sun, 12 October 2008 12:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
When you insert that many new rows, it is quicker to drop and recreate the index than to synchronize. Also, unless you optimize after your synchronize, you will have a lot of fragmentation.
Re: ctx indexes and performance problem [message #353559 is a reply to message #353190] Tue, 14 October 2008 03:15 Go to previous message
Messages: 46
Registered: July 2008
thank you for reply, but my problem still exists. If I drop and recreate the indexes the problem exists for the next 50000 records.

I think there is something wrong with my export and import process, I exactly don't know how can I move my oracle database from one machine into another one. now I am using expdp tool to export the schema (user), maybe there are some other points to consider, any idea around this problem?

FYI: in my target machine if I drop the table(and its indexes) and create it again with its indexes everything work fine.

[Updated on: Tue, 14 October 2008 03:57]

Report message to a moderator

Previous Topic: oracle
Next Topic: Query expansion and FIRST_ROWS problem
Goto Forum:

Current Time: Thu Jan 21 12:06:08 CST 2021