Re: index width
Col types between 256-272 indicate they are NOT NULL so i get rid of the
that attribute to get the true coltype, I can't recall why i do the -1 bit.
Without looking in a manual I think col types 5,8,10 are decimal/float
types, so I have to do something slightly different for them. All the
information I used was in the SQL:Reference Manual coupled with some
intuition and experimentation
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: Colin Dawson <cjd_1955@hotmail.com>, informix-list@iiug.org
>Subject: Re: index width
>Date: Thu, 10 Aug 2006 02:39:14 -0700 (PDT)
>
>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.csv>delimiter ','
>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,s