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.

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 where
seqscans > 0 and dbsname not like "sys%" group by 1;

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

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

"Doug McAllister@Fidelity Investments" <dougmc001@gmail.com> wrote in
message news:1149703655.779384.151690@u72g2000cwu.googlegr oups.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
.....