How to find Index corruption and repair if any
Poster urgently asked how to detect and fix a corrupted index. Replies pointed to oncheck (-cd/-cD for data, -cI/-ci for indexes), noting oncheck can sometimes repair, but usually dropping and recreating the index is the fix, with caveats about exclusive access, lost query plans, rebuild cost, and the extra complexity of primary-key indexes (foreign keys dropped, no 'online' option). It then emerged there were no corruption messages at all, just slow saves from the application. Others argued corruption would be reported explicitly and that this is a normal performance issue (query plans, stale update statistics, locking, disk/network), with a suggestion to check engine version/pagesize and possibly 'index repack shrink' for BTree cleaner issues on 12.10. No resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Could any one please help me immediately on how to find whether index is corrupted or not and if so what is the way to repair. It will be more helpful when your response is fast.
You can use "oncheck" command to validate the integrity of data (-d/-D) and
indexes (-i/-I) with the "-c" option:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.adref.doc/
ids_adr_0375.htm
But why do you need it? Did the engine complained about any corruption?
oncheck may be able to repair some issues, but in most cases you can just
drop and recreate the indexes.
Note that it will probably have impact:
1- You may need exclusive access to the table(s)
2- After you drop the index, the queries won't be able to use it. That can
affect query plans
3- The creation of the indexes can consume significant resources and time
if the tables are big
Regards.
On Thu, Nov 30, 2017 at 8:31 AM, MUKESH TANUKU <mukeshbt1328@gmail.com>
wrote:
> Could any one please help me immediately on how to find whether index is
> corrupted or not and if so what is the way to repair.
>
> It will be more helpful when your response is fast.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Use oncheck -cI to check whether the index is damaged.
Sometimes oncheck will be able to repair, sometimes the index needs to be
recreated.
When recreating the index, you might make use of the "create index ....
online" functionality,
since this does not lock the table for the whole time while index is being
calculated.
This makes sense with very huge tables, which need to be accessible during
index creation.
The only drawback is that you cannot create multiple indexes in parallel with
the online keyword.
However, dropping an index almost always leads to a performance problem until
the index is re-created.
There might be situations where a primary key is damaged (mostly the
underlying index).
This is more complex, since you would need to re-create all the foreign key
constraints, which
are automatically dropped when the primary key is dropped.
There is no option to recreate a primary key with an "online" keyword.
Also, you might run into inconsistent data when the constraints are not in
place any more.
Hope this helps.
Marcus Haarmann
Von: "MUKESH TANUKU" <mukeshbt1328@gmail.com>
An: "ids" <ids@iiug.org>
Gesendet: Donnerstag, 30. November 2017 08:31:09
Betreff: How to find Index corruption and repair if any [40302]
Could any one please help me immediately on how to find whether index is
corrupted or not and if so what is the way to repair.
It will be more helpful when your response is fast.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks for your prompt response Fernando Nunes. DB Engine is up and running, no log was returned any corrupted index. Our application is taking long time to save the process done from front end. I feel that particular table, column index is corrupted or need to be repaired.
Update statistics outdated on the table ?
Marcus Haarmann
Von: "MUKESH TANUKU" <mukeshbt1328@gmail.com>
An: "ids" <ids@iiug.org>
Gesendet: Donnerstag, 30. November 2017 11:52:14
Betreff: Re: How to find Index corruption and repair if any [40307]
Thanks for your prompt response Fernando Nunes.
DB Engine is up and running, no log was returned any corrupted index.
Our application is taking long time to save the process done from front end.
I feel that particular table, column index is corrupted or need to be
repaired.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Can you see the application connected to the database and is it doing anything, or is it mostly idle? It's more likely to be a regular performance problem, rather than corruption. A query could be choosing a different query plan, locking, disk contention, network...could be many things. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MUKESH TANUKU Sent: Thursday, November 30, 2017 3:52 AM To: ids@iiug.org Subject: Re: How to find Index corruption and repair if any [40307] Thanks for your prompt response Fernando Nunes. DB Engine is up and running, no log was returned any corrupted index. Our application is taking long time to save the process done from front end. I feel that particular table, column index is corrupted or need to be repaired. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
it is a regular performance problem, but unable to trace the situation why the application is getting slow at the time of saving the record in the database.
Hi, It would be very useful if you could post the exact version of the engine you are running. What pagesize is being used for dbspace which the index in question resides? There are some BTree cleaner bugs you might hit even in the latest 12.10 releases that mean that your query might need to scan a lot of empty blocks to find data. If you are on 12.10 have you considered doing an "index repack shrink"? Ben.
Doesn't make sense. If Informix is using an index and it finds a corruption it will complain. Slowness is not a symptom of corruption... Check query plans etc. Regards On Thu, Nov 30, 2017 at 11:52 AM, MUKESH TANUKU <mukeshbt1328@gmail.com> wrote: > Thanks for your prompt response Fernando Nunes. > DB Engine is up and running, no log was returned any corrupted index. > > Our application is taking long time to save the process done from front > end. > > I feel that particular table, column index is corrupted or need to be > repaired. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...