Re: index width
You can also set SQEXPLAIN=1 in your env and run dbschema on the table
to see the sql statements.
FYI
Floyd Wellershaus wrote:
> 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/
> --0-1998975190-1155143483=:41650
> Content-Type: text/html
> X-Google-AttachSize: 1841
>
> <html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
> <DIV>I'm reading that B-tree indexes don't perform well on wide columns, specifically those above 32bytes.</DIV>
> <DIV> </DIV>
> <DIV>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 ?</DIV>
> <DIV>I know I have to join systables, syscolumns and sysindexes, and join on sysindexes.part1 etc to syscolumns.colno and then get the collength.</DIV>
> <DIV>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.</DIV>
> <DIV> </DIV>
> <DIV>select si.idxname,st.tabname,sc.colname,sc.collength<BR>from sysindexes si,systables st,syscolumns sc<BR>where st.tabid>99 and st.tabname not matches '*[0-9]*'<BR>and st.tabid=si.tabid<BR>and si.tabid=sc.tabid<BR>and (si.part1=sc.colno or si.part2=sc.colno or si.part3=sc.colno)<BR>and st.tabname='proc_stats'<BR>order by sc.collength desc</DIV>
> <DIV> </DIV>
> <DIV>Thanks for any help.</DIV>
> <DIV><BR> </DIV>
> <DIV><BR>
> <DIV><BR>
> <DIV><BR>
> <DIV><BR>
> <DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR>
> <DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR>
> <DIV>Home: 703-430-0805</DIV><BR>
> <DIV>Cell: 703-477-6045<BR>========================</DIV><BR>
> <DIV><A href="http://www.one.org/">http://www.one.org/</A></DIV></DIV></DIV></DIV></DIV>;
> <DIV></DIV></div></body></html>
> --0-1998975190-1155143483=:41650--
Nice, trick I'll try that one also.
Kernoal wrote:
> You can also set SQEXPLAIN=1 in your env and run dbschema on the table
> to see the sql statements.
>
> FYI
>
>
> Floyd Wellershaus wrote:
> > 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/
> > --0-1998975190-1155143483=:41650
> > Content-Type: text/html
> > X-Google-AttachSize: 1841
> >
> > <html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV></DIV>
> > <DIV>I'm reading that B-tree indexes don't perform well on wide columns, specifically those above 32bytes.</DIV>
> > <DIV> </DIV>
> > <DIV>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 ?</DIV>
> > <DIV>I know I have to join systables, syscolumns and sysindexes, and join on sysindexes.part1 etc to syscolumns.colno and then get the collength.</DIV>
> > <DIV>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.</DIV>
> > <DIV> </DIV>
> > <DIV>select si.idxname,st.tabname,sc.colname,sc.collength<BR>from sysindexes si,systables st,syscolumns sc<BR>where st.tabid>99 and st.tabname not matches '*[0-9]*'<BR>and st.tabid=si.tabid<BR>and si.tabid=sc.tabid<BR>and (si.part1=sc.colno or si.part2=sc.colno or si.part3=sc.colno)<BR>and st.tabname='proc_stats'<BR>order by sc.collength desc</DIV>
> > <DIV> </DIV>
> > <DIV>Thanks for any help.</DIV>
> > <DIV><BR> </DIV>
> > <DIV><BR>
> > <DIV><BR>
> > <DIV><BR>
> > <DIV><BR>
> > <DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV><BR>
> > <DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A></DIV><BR>
> > <DIV>Home: 703-430-0805</DIV><BR>
> > <DIV>Cell: 703-477-6045<BR>========================</DIV><BR>
> > <DIV><A href="http://www.one.org/">http://www.one.org/</A></DIV></DIV></DIV></DIV></DIV>;
> > <DIV></DIV></div></body></html>
> > --0-1998975190-1155143483=:41650--