Checking indexes after large deletes

Is there a way to determine how "messed up" an index is after a large number of deletes have been done to a table? I searched here and didn't see anything. Is there an oncheck command or sysmaster table to look at? If so what values should I look for? I have to make a case for rebuilding indexes and having hard data would make my case stronger. Thanks.

bozon wrote: > Is there a way to determine how "messed up" an index is after a large > number of deletes have been done to a table? I searched here and didn't > see anything. Is there an oncheck command or sysmaster table to look > at? If so what values should I look for? I have to make a case for > rebuilding indexes and having hard data would make my case stronger. > > Thanks. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Why rebuild the index after you have gone through the effort of marking all those keys as deleted? drop the index first. Caveat: if you are using the index to drive the delete, that might not be a good idea... -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

On Tue, 2006-06-06 at 08:26 -0700, bozon wrote: > Is there a way to determine how "messed up" an index is after a large > number of deletes have been done to a table? I searched here and didn't > see anything. Is there an oncheck command or sysmaster table to look > at? If so what values should I look for? I have to make a case for > rebuilding indexes and having hard data would make my case stronger. I don't know if it is what you are looking for but I use these three queries to look at index effectiveness: Index key uniqueness: select tabname, idxname, nrows, nunique from systables t, sysindexes i where t.tabid = i.tabid and t.tabid > 99 and nrows > 0 and nunique >0; Index depth: select idxname, levels from sysindexes order by 2 desc; How many times the engine has resorted to a sequential scan: select tabname, sum(seqscans) tot_scans from sysmaster:sysptprof whereseqscans > 0 and dbsname not like "sys%" group by 1;

Good idea to drop the index first. In general this is what I do, but this process is part of a job that removes customer data for customers that no longer use our product. It is part of a monthly cleanup and it happens while the instance is on line. So is there a way to know if the cleaner is keeping up and restructuring the indexes (by removing the keys that are marked deleted and then reorganizing the nodes to rebalance the B-Tree? Marco Greco wrote: > bozon wrote: > > Is there a way to determine how "messed up" an index is after a large > > number of deletes have been done to a table? I searched here and didn't > > see anything. Is there an oncheck command or sysmaster table to look > > at? If so what values should I look for? I have to make a case for > > rebuilding indexes and having hard data would make my case stronger. > > > > Thanks. > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > > > Why rebuild the index after you have gone through the effort of marking all > those keys as deleted? drop the index first. > Caveat: if you are using the index to drive the delete, that might not be a > good idea... > > -- > Ciao, > Marco > ______________________________________________________________________________ > Marco Greco /UK /IBM Standard disclaimers apply! > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > 4glworks http://www.4glworks.com > Informix on Linux http://www.4glworks.com/ifmxlinux.htm

bozon wrote: > Good idea to drop the index first. In general this is what I do, but > this process is part of a job that removes customer data for customers > that no longer use our product. It is part of a monthly cleanup and it > happens while the instance is on line. > > So is there a way to know if the cleaner is keeping up and > restructuring the indexes (by removing the keys that are marked deleted > and then reorganizing the nodes to rebalance the B-Tree? onstat -g btc Will show you what index pages have been marked for cleaning. If you see a preponderence of pages for a single partnum (index or index fragment) that would be a candidate for a rebuild if you cannot wait for the Btree Cleaners to catch up. Art S. Kagel > Marco Greco wrote: > >>bozon wrote: >> >>>Is there a way to determine how "messed up" an index is after a large >>>number of deletes have been done to a table? I searched here and didn't >>>see anything. Is there an oncheck command or sysmaster table to look >>>at? If so what values should I look for? I have to make a case for >>>rebuilding indexes and having hard data would make my case stronger. >>> >>>Thanks. >>> >>>_______________________________________________ >>>Informix-list mailing list >>>Informix-list@iiug.org >>>http://www.iiug.org/mailman/listinfo/informix-list >>> >> >>Why rebuild the index after you have gone through the effort of marking all >>those keys as deleted? drop the index first. >>Caveat: if you are using the index to drive the delete, that might not be a >>good idea... >> >>-- >>Ciao, >>Marco >>______________________________________________________________________________ >>Marco Greco /UK /IBM Standard disclaimers apply! >> >>Structured Query Scripting Language http://www.4glworks.com/sqsl.htm >>4glworks http://www.4glworks.com >>Informix on Linux http://www.4glworks.com/ifmxlinux.htm > >

I can't seem to get "onstat -g btc" to return anything in 9.21 FC4 or 10.0 FC4. Art S. Kagel wrote: > bozon wrote: > > Good idea to drop the index first. In general this is what I do, but > > this process is part of a job that removes customer data for customers > > that no longer use our product. It is part of a monthly cleanup and it > > happens while the instance is on line. > > > > So is there a way to know if the cleaner is keeping up and > > restructuring the indexes (by removing the keys that are marked deleted > > and then reorganizing the nodes to rebalance the B-Tree? > > onstat -g btc> > Will show you what index pages have been marked for cleaning. If you see a > preponderence of pages for a single partnum (index or index fragment) that > would be a candidate for a rebuild if you cannot wait for the Btree Cleaners > to catch up. > > Art S. Kagel > > > Marco Greco wrote: > > > >>bozon wrote: > >> > >>>Is there a way to determine how "messed up" an index is after a large > >>>number of deletes have been done to a table? I searched here and didn't > >>>see anything. Is there an oncheck command or sysmaster table to look > >>>at? If so what values should I look for? I have to make a case for > >>>rebuilding indexes and having hard data would make my case stronger. > >>> > >>>Thanks. > >>> > >>>_______________________________________________ > >>>Informix-list mailing list > >>>Informix-list@iiug.org > >>>http://www.iiug.org/mailman/listinfo/informix-list > >>> > >> > >>Why rebuild the index after you have gone through the effort of marking all > >>those keys as deleted? drop the index first. > >>Caveat: if you are using the index to drive the delete, that might not be a > >>good idea... > >> > >>-- > >>Ciao, > >>Marco > >>______________________________________________________________________________ > >>Marco Greco /UK /IBM Standard disclaimers apply! > >> > >>Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > >>4glworks http://www.4glworks.com > >>Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > >

> I can't seem to get "onstat -g btc" to return anything in 9.21 FC4 or > 10.0 FC4. It doesn't do anything on my "10.00.UC4" box. There is "btree cleaner info", with "onstat -C". Is that the same thing?

hmmm, btc is not a valid option in my 10.00.FC4 instance and when I try it onstat gives me the help listing. When I try it on my 9.40 instances, I get nothing at all. Art S. Kagel wrote: > bozon wrote: > > Good idea to drop the index first. In general this is what I do, but > > this process is part of a job that removes customer data for customers > > that no longer use our product. It is part of a monthly cleanup and it > > happens while the instance is on line. > > > > So is there a way to know if the cleaner is keeping up and > > restructuring the indexes (by removing the keys that are marked deleted > > and then reorganizing the nodes to rebalance the B-Tree? > > onstat -g btc> > Will show you what index pages have been marked for cleaning. If you see a > preponderence of pages for a single partnum (index or index fragment) that > would be a candidate for a rebuild if you cannot wait for the Btree Cleaners > to catch up. > > Art S. Kagel > > > Marco Greco wrote: > > > >>bozon wrote: > >> > >>>Is there a way to determine how "messed up" an index is after a large > >>>number of deletes have been done to a table? I searched here and didn't > >>>see anything. Is there an oncheck command or sysmaster table to look > >>>at? If so what values should I look for? I have to make a case for > >>>rebuilding indexes and having hard data would make my case stronger. > >>> > >>>Thanks. > >>> > >>>_______________________________________________ > >>>Informix-list mailing list > >>>Informix-list@iiug.org > >>>http://www.iiug.org/mailman/listinfo/informix-list > >>> > >> > >>Why rebuild the index after you have gone through the effort of marking all > >>those keys as deleted? drop the index first. > >>Caveat: if you are using the index to drive the delete, that might not be a > >>good idea... > >> > >>-- > >>Ciao, > >>Marco > >>______________________________________________________________________________ > >>Marco Greco /UK /IBM Standard disclaimers apply! > >> > >>Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > >>4glworks http://www.4glworks.com > >>Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > >

Adam Tauno Williams wrote: >> I can't seem to get "onstat -g btc" to return anything in 9.21 FC4 or >> 10.0 FC4. > > It doesn't do anything on my "10.00.UC4" box. > > There is "btree cleaner info", with "onstat -C". Is that the same > thing? > Yes ... onstat -C Use the -C option to print the file information about the B-tree scanner subsystem and each B-tree scanner thread. The following options are available with the onstat -C command: prof Prints the profile information for the system and each B-tree scanner thread hot Prints the hot list index key in the order to be cleaned part Prints all partitions with index statistics clean Prints information about all the partitions that were cleaned or need to be cleaned. range Prints the savings in pages processes by using index range scanning all Prints all onstat -C options

I recall sometime back that someone was going to generate a paper on tuning the b-tree scanner. Anyone recall who it was? Jerry, weren't you interested in this too? Art, was it you by chance? peace TBP <TheBigPotato@Not Here.Co.Uk> To Sent by: informix-list@iiug.org informix-list-bou cc nces@iiug.org Subject Re: Checking indexes after large 06/07/2006 02:30 deletes PM Adam Tauno Williams wrote: >> I can't seem to get "onstat -g btc" to return anything in 9.21 FC4 or >> 10.0 FC4. > > It doesn't do anything on my "10.00.UC4" box. > > There is "btree cleaner info", with "onstat -C". Is that the same > thing? > Yes ... onstat -C Use the -C option to print the file information about the B-tree scanner subsystem and each B-tree scanner thread. The following options are available with the onstat -C command: prof Prints the profile information for the system and each B-tree scanner thread hot Prints the hot list index key in the order to be cleaned part Prints all partitions with index statistics clean Prints information about all the partitions that were cleaned or need to be cleaned. range Prints the savings in pages processes by using index range scanning all Prints all onstat -C options _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

"Doug McAllister@Fidelity Investments" <dougmc001@gmail.com> wrote in message news:1149703655.779384.151690@u72g2000cwu.googlegroups.com... > hmmm, btc is not a valid option in my 10.00.FC4 instance and when I try > it onstat gives me the help listing. When I try it on my 9.40 > instances, I get nothing at all. Nor on my 10.0FC5 one ... [informix@orbistest01 ~]$ onstat -g btc IBM Informix Dynamic Server Version 10.00.UC5 -- On-Line -- Up 04:56:48 -- 28048 Kbytes usage: onstat [ -abcdfghklmpstuxzBCDFRX ] [ -i ] [ -r [<seconds>] ] [ -o [<outfile>] ] [ <infile> ] -a Print all info -b Print buffers -c Print configuration file -d [update] Print spaces and chunks ....