Re: index width
Thanks. That does seem like it'll do the trick. I wrote a shell script last night to basically do the same thing, using awk to add up the column lengths in the unload file.
I don't understand the bit about changing the collength for coltype 5, 8, 10 and the ones between 256 and 272.
Also, why do you change the ones with collength of 0 to a -1 ?
Thanks.
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: Colin Dawson <cjd_1955@hotmail.com>
To: informix-list@iiug.org
Sent: Thursday, August 10, 2006 5:28:34 AM
Subject: RE: index width
I coded this bit of SQL, it's a bit crude, but it did what I wanted
create temp table t_idx
(tabname char(18),
idxname char(18),
idx_col smallint,
idx_len integer,
idx_coltype smallint
);
insert into t_idx
select tabname, idxname, part1, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part1 = c.colno;
insert into t_idx
select tabname, idxname, part2, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part2 = c.colno ;
insert into t_idx
select tabname, idxname, part3, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part3 = c.colno ;
insert into t_idx
select tabname, idxname, part4, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part4 = c.colno ;
insert into t_idx
select tabname, idxname, part5, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part5 = c.colno ;
insert into t_idx
select tabname, idxname, part6, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part6 = c.colno ;
insert into t_idx
select tabname, idxname, part7, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part7 = c.colno ;
insert into t_idx
select tabname, idxname, part8, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part8 = c.colno ;
insert into t_idx
select tabname, idxname, part9, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part9 = c.colno ;
insert into t_idx
select tabname, idxname, part10, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part10 = c.colno ;
insert into t_idx
select tabname, idxname, part11, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part11 = c.colno ;
insert into t_idx
select tabname, idxname, part12, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part12 = c.colno ;
insert into t_idx
select tabname, idxname, part13, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part13 = c.colno ;
insert into t_idx
select tabname, idxname, part14, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part14 = c.colno ;
insert into t_idx
select tabname, idxname, part15, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part15 = c.colno ;
insert into t_idx
select tabname, idxname, part16, collength, coltype
from sysindexes i, syscolumns c, systables t
where i.tabid > 99
and i.tabid = c.tabid
and i.tabid = t.tabid
and i.part16 = c.colno ;
update t_idx
set idx_col = idx_col * -1
where idx_col < 0;
update t_idx
set idx_coltype = idx_coltype - 256
where idx_coltype between (256+0) and (256+16);
update t_idx
set idx_len = TRUNC(idx_len/256)
where idx_coltype in (5,8,10);
unload to /tmp/idx_len.csvdelimiter ','
select tabname, idxname, sum(idx_len)
from t_idx
group by 1,2
order by 1,2
Regards
Colin
There are 10 types of people in the world, those that understand binary and
those that don't
>From: Floyd Wellershaus <fwellers@yahoo.com>
>Reply-To: Floyd Wellershaus <fwellers@yahoo.com>
>To: informix-list@iiug.org
>Subject: index width
>Date: Wed, 9 Aug 2006 10:11:23 -0700 (PDT)
>
>I'm reading that B-tree indexes don't perform well on wide columns,
>specifically those above 32bytes.
>
>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>
>Thanks for any help.
>
>
>
>
>
>
>
>
>
>
>========================
>-<<Floyd Wellershaus>>-
>Database Administrator
>Unix Administrator
>
>
>
>email: fwellers@yahoo.com
>
>
>Home: 703-430-0805
>
>
>Cell: 703-477-6045
>========================
>
>
>http://www.one.org/
>_______________________________________________
>Informix-list mailing list
>Informix-list@iiug.org
>http://www.iiug.org/mailman/listinfo/informix-list
_________________________________________________________________
Be the first to hear what's new at MSN - sign up to our free newsletters!
http://www.msn.co.uk/newsletters
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list