Answered Deactivate Indexes

[BigSlick]

Hi all,

OE10.2 Windows 7 64bit.

Just a quick question; Is there a way to deactivate indexes quickly (by command) or in bulk?

I'm moving tables and some wont move due to the inactive index, so i've activated them through the proutil command and i'd like them deactivated again.

I know they should probably be removed if not used, but just to 'leave things as I found them' I thought i'd try this method before going to my manager and then to CAB and so forth...

Thanks in advance.

 

[Rob Fitzpatrick]

How to do it:
In the Data Dictionary there is an Index Deactivation option in the Utilities menu. You can use that to select indexes to deactivate. You can also do this programmatically by setting _Index._Active = no for the appropriate _Index record(s). Note that in either case the schema for the table must not be frozen. If it is, first set _File._Frozen = no for the appropriate _File record(s). Remember to set it back to frozen if you change it.

If you should do it:
In a word, no. You shouldn't have any inactive application indexes in your database. Whoever decided that indexes should be deactivated probably thought they were helping application performance by avoiding index create and delete activity. In fact it can have the opposite effect. When the compiler wants to select an index to resolve a query and it is inactive, this could result in a table scan, causing many more index keys and records to be read than should be necessary.

If this is happening then you have a performance problem that is being caused by inactive indexes. And if you are absolutely sure it isn't happening then the indexes aren't required and should be removed before someone writes code that mistakenly assumes those indexes are active.

I have learned from experience that the way you find things isn't always the way they should be. Sometimes rules, written or otherwise, are based on the way a platform or application used to behave years ago and no longer does. And sometimes they're based on myths that were never true. Making changes can cause friction but it's the right thing to do.

 

[Rob Fitzpatrick]

OE10.2 Windows 7 64bit.

It's not clear if you mean 64-bit Windows or 64-bit OpenEdge. It's a good idea to tell people the platform (32/64) for both the OS and OE.

Also, 10.2 isn't a release. Based on one of your other threads you're on 10.2B. It's also a good idea to tell people the service pack you have installed, e.g. 10.2B07, 10.2B08, etc.

 

[BigSlick]

Sorry Rob, I missed the 'B' off. 10.2B i think its SP05 and I was saying windows was 64-bit.

Although OE is 64bit also.

 

[BigSlick]

Thanks for the explanation, Rob.

That should save me time in the short term. As for the long term, I will look at getting these removed. I'll use your response to back up my argument

 

[RealHeavyDude]

There is another, IMHO, compelling reasons not to have any inactive indexes in a database:

If you need, for whatever reason, an index rebuild, then you might accidentally activate such an index with all kinds of negative side effects on your database/application. Either an index is useful or not. If it is not you should better removed.

Heavy Regards, RealHeavyDude.