Re: index width

On 8/9/06, Floyd Wellershaus <fwellers@yahoo.com> wrote: > I'm reading that B-tree indexes don't perform well on wide columns, > specifically those above 32bytes. That's funny. Where are you reading that? In IDS 10, we increased the maximum index key size to about 3 KB on 16 KB pages. > I'm trying to find out the width of all our indexes. I can't come up with a > query to do this, any help ? > I know I have to join systables, syscolumns and sysindexes, and join on > sysindexes.part1 etc to syscolumns.colno and then get the collength. > I can pretty much get there with this, but it doesn't group them properly to > be able to add up the columns for a multi-column index. > > select si.idxname,st.tabname,sc.colname,sc.collength > from sysindexes si,systables st,syscolumns sc > where st.tabid>99 and st.tabname not matches '*[0-9]*' > and st.tabid=si.tabid > and si.tabid=sc.tabid > and (si.part1=sc.colno or si.part2=sc.colno or si.part3=sc.colno) > and st.tabname='proc_stats' > order by sc.collength desc Apart from repeating for each of the 16 possible index parts, you need to dcode the collength field for many types. For example, DECIMAL columns are encoded with the scale and precision combined with shift and OR operators. You need to extract the scale and precision from the recorded values to determine that actual data space used by the column. DATETIME and INTERVAL are more complex (and, while similar, also subtly different from each other). And so on. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/