Adding an Index to a large table while in use

Last post 07-11-2008 3:17 AM by Arvin. 4 replies.

Page 1 of 1 (5 items)
	Sort Posts: Previous Next

07-10-2008 3:00 AM

Arvin
Joined on 04-08-2008
Posts 4

Adding an Index to a large table while in use

Reply Contact

Hi,

I was wondering if anyone can help me.

One of our customer has a very large table that takes more than one day to REBUILD. We need to add a new index to this table, but the customer would like to continue to use the application that accesses the table while the index is being added.

Is there a way to do this? Or do we have to shut down the application until the index is added?

You suggestion or help would be appreciated.

Arvin.

07-10-2008 3:00 PM In reply to

Mirtheil
Joined on 07-09-2007
Posts 2,997

Re: Adding an Index to a large table while in use

Reply Contact

It might work but from a performance and data integrity perspective, it's better to get everyone out of the system when adding (or deleting) an index.

Mirtheil
I do not answer question via email. Please post questions in the forum.

07-10-2008 3:10 PM In reply to

Arvin
Joined on 04-08-2008
Posts 4

Re: Adding an Index to a large table while in use

Reply Contact

Thanks, but how to do this.

Use the maintenance utility? Or a btieve function call? Will the create the index in the table and also populate the values in the index as well?

ThaNKA.

07-10-2008 7:48 PM In reply to

Mirtheil
Joined on 07-09-2007
Posts 2,997

Re: Adding an Index to a large table while in use

Reply Contact

Adding an index through the Btrieve API (or Maintenance Utility) or through SQL will populate the data in the index.

How you add it depends on what you are trying to accomplish with adding the index. What are you trying to do with the index? FOr example, if it's for a report that uses SQL, using Btrieve API won't help. You'd have to use SQL.

Mirtheil
I do not answer question via email. Please post questions in the forum.

07-11-2008 3:17 AM In reply to

Arvin
Joined on 04-08-2008
Posts 4

Re: Adding an Index to a large table while in use

Reply Contact

Thanks Mirtheil.

I understand this part now about how to add the index.

The other questions are :

1) suppose I do use the Btieve Api or Sql, will the table be locked while the new index is being built?

Or can the users continue to do searches, inserts and updates to the table as long as they do not use the new index that is being built.?

2) When the index is finshed building, will it reflect all the changes to the table that was done while it was being built ?

Does anyone have experience with this? I know this works with MS SQL but I am not sure about PSQL.

Thanks.

Page 1 of 1 (5 items)