Re: index width
Floyd Wellershaus wrote:
> I'm reading that B-tree indexes don't perform well on wide columns, specifically those above 32bytes.
Here is a perl script that should decode the lengths of fields. I
haven't looked at it in awhile so it probably doesn't handle lvarchar's
etc. The next script gives you all of the fields of an index. You can
combine the sql in the second script with the processing of the
scolsdet to determine the length of a field to find the length of each
field in an index.
<<<<< BEGIN scolsdet >>>>>>>>
#!/usr/bin/perl
@dtp = qw(char smallint integer float smallfloat decimal
serial date money unknown datetime byte text
varchar interval nchar nvarchar unk unk unk unk);
@datp = qw(nyet year nyet month nyet day
nyet hour nyet minute nyet second
fraction(1) fraction(2) fraction(3) fraction(4) fraction(5));
@int_end = (undef, 5, undef, 7, undef, 9, undef, 11, undef, 13, undef,
15, 16, 17, 18, 19, 20);
@int_start = (undef, 1, undef, 5, undef, 7, undef, 9, undef, 11, undef,
13, 15, 16, 17, 18, 19);
sub fixnm($$) {
($coll, $tp) = @_;
$i = int($coll / 256);
$j = $coll % 256;
return $j > $i ? $i : sprintf("%s,%s", $i, $j) if $tp == 0;
return $i == 0 ? $j : sprintf("%s,%s", $j, $i);
} # fixnm
sub fixdt($) {
$coll = shift;
$i = $coll % 16 + 1;
$j = int(($coll % 256) / 16) + 1;
$k = int($coll / 256);
$ln = $int_end[$i] - $int_start[$j];
$ln = $k - $ln;
return sprintf "%s to %s", $datp[$j], $datp[$i] if $ln == 0 or $j >
11;
$k = $int_end[$j] - $int_start[$j];
$k += $ln;
return sprintf "%s (%d) to %s", $datp[$j], $k, $datp[$i];
} # fixdt
sub process ($) {
my( $tabname, $colno, $colname, $coltype, $collength ) = split / /,
$_[0] ;
$nonull = int($coltype / 256) ? ' NOT NULL' : '';
$coltype = $coltype % 256;
$collength = $collength;
$qualifier = '';
$qualifier = $collength if $coltype == 0;
$qualifier = fixnm($collength, 0) if $coltype == 5 or $coltype ==
8;
$qualifier = fixnm($collength, 1) if $coltype == 13;
$qualifier = fixdt($collength) if $coltype == 10 or $coltype ==
14;
$qualifier = "($qualifier)" if $qualifier ne '';
printf "%-20s %-20s %-25s\\n",
$tabname, $colname, "$dtp[$coltype]$qualifier$nonull";
} # process
die <<USAGE unless @ARGV == 1 or @ARGV == 2 ;
usage: scols <database> [tab]
It will return the tablename, column name and datatype/length of
columns for each table or for all tables.
USAGE
$dbname = shift;
$tabname = shift if @ARGV == 1 ;
$tabname = "and tabname = '" . $tabname . "'" if $tabname ne "" ;
system "dbaccess $dbname <<EOF >/dev/null 2>&1
set isolation to dirty read ;
unload to '/tmp/new_scols_$$.out' delimiter ' '
select
t.tabname,
c.colno,
c.colname,
c.coltype,
c.collength
from
systables t,
syscolumns c
where
t.tabid = c.tabid and
t.tabid >= 100
$tabname
order by
t.tabname,
c.colno
;EOF
" ;
unless( open TABLIST, "/tmp/new_scols_$$.out" ) {
#&cleanup ;
die "Could not get tables." ;
}
process $row while ( chomp( $row = <TABLIST> ) ) ;
unlink "/tmp/new_scols_$$.out" ;
#process $row while $row = $cursor->fetchrow_hashref;
<<<<<<END FIRST SCRIPT>>>>>>>>>>
<<<< begin sidxn >>>>>>>>
#!/usr/bin/ksh
PROC_NAME=`basename $0`
trap "cleanup ; exit" 0 1 2 15
function usage {
printf "\\n\\n Usage: %s <database> [tabname] \\n\\n" "$PROC_NAME" 1>&2
exit 1
}
function cleanup {
rm -f /tmp/${PROC_NAME}_$$.out
}
if [ $# -lt 1 -o $# -gt 2 ] ; then
usage
fi
if [ $# -eq 2 ] ; then
where=" and t.tabname = \\"$2\\""
else
where=""
fi
dbaccess $1 <<EOF 1>/dev/null 2>/dev/null
set isolation to dirty read;
unload to /tmp/${PROC_NAME}_$$.out
select
tabname,
idxname,
idxtype,
c1.colname,
case
when i.part1 < 0 then 'D'
when i.part1 > 0 then 'A'
else null
end,
c2.colname,
case
when i.part2 < 0 then 'D'
when i.part2 > 0 then 'A'
else null
end,
c3.colname,
case
when i.part3 < 0 then 'D'
when i.part3 > 0 then 'A'
else null
end,
c4.colname,
case
when i.part4 < 0 then 'D'
when i.part4 > 0 then 'A'
else null
end,
c5.colname,
case
when i.part5 < 0 then 'D'
when i.part5 > 0 then 'A'
else null
end,
c6.colname,
case
when i.part6 < 0 then 'D'
when i.part6 > 0 then 'A'
else null
end,
c7.colname,
case
when i.part7 < 0 then 'D'
when i.part7 > 0 then 'A'
else null
end,
c8.colname,
case
when i.part8 < 0 then 'D'
when i.part8 > 0 then 'A'
else null
end,
c9.colname,
case
when i.part9 < 0 then 'D'
when i.part9 > 0 then 'A'
else null
end,
c10.colname,
case
when i.part10 < 0 then 'D'
when i.part10 > 0 then 'A'
else null
end,
c11.colname,
case
when i.part11 < 0 then 'D'
when i.part11 > 0 then 'A'
else null
end,
c12.colname,
case
when i.part12 < 0 then 'D'
when i.part12 > 0 then 'A'
else null
end,
c13.colname,
case
when i.part13 < 0 then 'D'
when i.part13 > 0 then 'A'
else null
end,
c14.colname,
case
when i.part14 < 0 then 'D'
when i.part14 > 0 then 'A'
else null
end,
c15.colname,
case
when i.part15 < 0 then 'D'
when i.part15 > 0 then 'A'
else null
end,
c16.colname,
case
when i.part16 < 0 then 'D'
when i.part16 > 0 then 'A'
else null
end
from
sysindexes i,
systables t,
syscolumns c1, -- At least one column
outer syscolumns c2,
outer syscolumns c3,
outer syscolumns c4,
outer syscolumns c5,
outer syscolumns c6,
outer syscolumns c7,
outer syscolumns c8,
outer syscolumns c9,
outer syscolumns c10,
outer syscolumns c11,
outer syscolumns c12,
outer syscolumns c13,
outer syscolumns c14,
outer syscolumns c15,
outer syscolumns c16
where
t.tabid >= 100 and
t.tabtype = "T" and
t.tabid = i.tabid and
t.tabid = c1.tabid and
t.tabid = c2.tabid and
t.tabid = c3.tabid an
Oh, a secret way to figure this stuff out sometimes is to set explain
on ; in dbaccess and then go to the info screen to look at the tables.
You sqexplain.out file will now contain the SQL that the dbaccess
designers used to build the table info screens. ;-)
bozon wrote:
> Floyd Wellershaus wrote:
> > I'm reading that B-tree indexes don't perform well on wide columns, specifically those above 32bytes.
>
> Here is a perl script that should decode the lengths of fields. I
> haven't looked at it in awhile so it probably doesn't handle lvarchar's
> etc. The next script gives you all of the fields of an index. You can
> combine the sql in the second script with the processing of the
> scolsdet to determine the length of a field to find the length of each
> field in an index.
>
> <<<<< BEGIN scolsdet >>>>>>>>
> #!/usr/bin/perl
>
> @dtp = qw(char smallint integer float smallfloat decimal
> serial date money unknown datetime byte text
> varchar interval nchar nvarchar unk unk unk unk);
>
> @datp = qw(nyet year nyet month nyet day
> nyet hour nyet minute nyet second
> fraction(1) fraction(2) fraction(3) fraction(4) fraction(5));
>
> @int_end = (undef, 5, undef, 7, undef, 9, undef, 11, undef, 13, undef,
> 15, 16, 17, 18, 19, 20);
>
> @int_start = (undef, 1, undef, 5, undef, 7, undef, 9, undef, 11, undef,
> 13, 15, 16, 17, 18, 19);
>
> sub fixnm($$) {
> ($coll, $tp) = @_;
> $i = int($coll / 256);
> $j = $coll % 256;
>
> return $j > $i ? $i : sprintf("%s,%s", $i, $j) if $tp == 0;
> return $i == 0 ? $j : sprintf("%s,%s", $j, $i);
> } # fixnm
>
>
> sub fixdt($) {
> $coll = shift;
>
> $i = $coll % 16 + 1;
> $j = int(($coll % 256) / 16) + 1;
> $k = int($coll / 256);
> $ln = $int_end[$i] - $int_start[$j];
> $ln = $k - $ln;
>
> return sprintf "%s to %s", $datp[$j], $datp[$i] if $ln == 0 or $j >
> 11;
>
> $k = $int_end[$j] - $int_start[$j];
> $k += $ln;
> return sprintf "%s (%d) to %s", $datp[$j], $k, $datp[$i];
> } # fixdt
>
>
> sub process ($) {
> my( $tabname, $colno, $colname, $coltype, $collength ) = split / /,
> $_[0] ;
>
> $nonull = int($coltype / 256) ? ' NOT NULL' : '';
> $coltype = $coltype % 256;
> $collength = $collength;
>
> $qualifier = '';
> $qualifier = $collength if $coltype == 0;
> $qualifier = fixnm($collength, 0) if $coltype == 5 or $coltype ==
> 8;
> $qualifier = fixnm($collength, 1) if $coltype == 13;
> $qualifier = fixdt($collength) if $coltype == 10 or $coltype ==
> 14;
>
> $qualifier = "($qualifier)" if $qualifier ne '';
>
> printf "%-20s %-20s %-25s\\n",
> $tabname, $colname, "$dtp[$coltype]$qualifier$nonull";
> } # process
>
>
> die <<USAGE unless @ARGV == 1 or @ARGV == 2 ;
> usage: scols <database> [tab]
>
> It will return the tablename, column name and datatype/length of
> columns for each table or for all tables.
>
> USAGE
>
> $dbname = shift;
> $tabname = shift if @ARGV == 1 ;
> $tabname = "and tabname = '" . $tabname . "'" if $tabname ne "" ;
>
> system "dbaccess $dbname <<EOF >/dev/null 2>&1
> set isolation to dirty read ;
> unload to '/tmp/new_scols_$$.out' delimiter ' '
> select
> t.tabname,
> c.colno,
> c.colname,
> c.coltype,
> c.collength
> from
> systables t,
> syscolumns c
> where
> t.tabid = c.tabid and
> t.tabid >= 100
> $tabname
> order by
> t.tabname,
> c.colno
> ;> EOF
> " ;
>
> unless( open TABLIST, "/tmp/new_scols_$$.out" ) {
> #&cleanup ;
> die "Could not get tables." ;
> }
>
> process $row while ( chomp( $row = <TABLIST> ) ) ;
>
> unlink "/tmp/new_scols_$$.out" ;
>
>
>
> #process $row while $row = $cursor->fetchrow_hashref;
>
>
> <<<<<<END FIRST SCRIPT>>>>>>>>>>
>
> <<<< begin sidxn >>>>>>>>
> #!/usr/bin/ksh
>
> PROC_NAME=`basename $0`
>
> trap "cleanup ; exit" 0 1 2 15
>
> function usage {
> printf "\\n\\n Usage: %s <database> [tabname] \\n\\n" "$PROC_NAME" 1>&2
> exit 1
> }
>
> function cleanup {
> rm -f /tmp/${PROC_NAME}_$$.out
> }
>
> if [ $# -lt 1 -o $# -gt 2 ] ; then
> usage
> fi
>
> if [ $# -eq 2 ] ; then
> where=" and t.tabname = \\"$2\\""
> else
> where=""
> fi
>
> dbaccess $1 <<EOF 1>/dev/null 2>/dev/null
> set isolation to dirty read;
> unload to /tmp/${PROC_NAME}_$$.out
> select
> tabname,
> idxname,
> idxtype,
> c1.colname,
> case
> when i.part1 < 0 then 'D'
> when i.part1 > 0 then 'A'
> else null
> end,
> c2.colname,
> case
> when i.part2 < 0 then 'D'
> when i.part2 > 0 then 'A'
> else null
> end,
> c3.colname,
> case
> when i.part3 < 0 then 'D'
> when i.part3 > 0 then 'A'
> else null
> end,
> c4.colname,
> case
> when i.part4 < 0 then 'D'
> when i.part4 > 0 then 'A'
> else null
> end,
> c5.colname,
> case
> when i.part5 < 0 then 'D'
> when i.part5 > 0 then 'A'
> else null
> end,
> c6.colname,
> case
> when i.part6 < 0 then 'D'
> when i.part6 > 0 then 'A'
> else null
> end,
> c7.colname,
> case
> when i.part7 < 0 then 'D'
> when i.part7 > 0 then 'A'
> else null
> end,
> c8.colname,
> case
> when i.part8 < 0 then 'D'
> when i.part8 > 0 then 'A'
> else null
> end,
> c9.colname,
> case
> when i.part9 < 0 then 'D'
> when i.part9 > 0 then 'A'
> else null
> end,
> c10.colname,
> case
> when i.part10 < 0 then 'D'
> when i.part10 > 0 then 'A'
> else null
> end,
> c11.colname,
> case
> when i.part11 < 0 then 'D'
> when i.part11 > 0 then 'A'
> else null
> end,
> c12.colname,
> case
> when i.part12 < 0 then 'D'
> when i.part12 > 0 then 'A'
> else null
> end,
> c13.colname,
> case
> when i.part13 < 0 then 'D'
> when i.part13 > 0 then 'A'
> else null
> end,
> c14.colname,
> case
> when i.part14 < 0 then 'D'
> when i.part14 > 0 then 'A'
> else null
> end,
> c15.colname,
> case
> when i.part15 < 0 then 'D'
> when i.part15 > 0 then 'A'
> else null
> end,
> c16.colname,
> case
> when i.part16 < 0 then 'D'
> when i.par
That's a pretty cool trick. I like that. 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: bozon <curtis@crowson1.com>
To: informix-list@iiug.org
Sent: Thursday, August 10, 2006 9:42:33 AM
Subject: Re: index width
Oh, a secret way to figure this stuff out sometimes is to set explain
on ; in dbaccess and then go to the info screen to look at the tables.
You sqexplain.out file will now contain the SQL that the dbaccess
designers used to build the table info screens. ;-)
bozon wrote:
> Floyd Wellershaus wrote:
> > I'm reading that B-tree indexes don't perform well on wide columns, specifically those above 32bytes.
>
> Here is a perl script that should decode the lengths of fields. I
> haven't looked at it in awhile so it probably doesn't handle lvarchar's
> etc. The next script gives you all of the fields of an index. You can
> combine the sql in the second script with the processing of the
> scolsdet to determine the length of a field to find the length of each
> field in an index.
>
> <<<<< BEGIN scolsdet >>>>>>>>
> #!/usr/bin/perl
>
> @dtp = qw(char smallint integer float smallfloat decimal
> serial date money unknown datetime byte text
> varchar interval nchar nvarchar unk unk unk unk);
>
> @datp = qw(nyet year nyet month nyet day
> nyet hour nyet minute nyet second
> fraction(1) fraction(2) fraction(3) fraction(4) fraction(5));
>
> @int_end = (undef, 5, undef, 7, undef, 9, undef, 11, undef, 13, undef,
> 15, 16, 17, 18, 19, 20);
>
> @int_start = (undef, 1, undef, 5, undef, 7, undef, 9, undef, 11, undef,
> 13, 15, 16, 17, 18, 19);
>
> sub fixnm($$) {
> ($coll, $tp) = @_;
> $i = int($coll / 256);
> $j = $coll % 256;
>
> return $j > $i ? $i : sprintf("%s,%s", $i, $j) if $tp == 0;
> return $i == 0 ? $j : sprintf("%s,%s", $j, $i);
> } # fixnm
>
>
> sub fixdt($) {
> $coll = shift;
>
> $i = $coll % 16 + 1;
> $j = int(($coll % 256) / 16) + 1;
> $k = int($coll / 256);
> $ln = $int_end[$i] - $int_start[$j];
> $ln = $k - $ln;
>
> return sprintf "%s to %s", $datp[$j], $datp[$i] if $ln == 0 or $j >
> 11;
>
> $k = $int_end[$j] - $int_start[$j];
> $k += $ln;
> return sprintf "%s (%d) to %s", $datp[$j], $k, $datp[$i];
> } # fixdt
>
>
> sub process ($) {
> my( $tabname, $colno, $colname, $coltype, $collength ) = split / /,
> $_[0] ;
>
> $nonull = int($coltype / 256) ? ' NOT NULL' : '';
> $coltype = $coltype % 256;
> $collength = $collength;
>
> $qualifier = '';
> $qualifier = $collength if $coltype == 0;
> $qualifier = fixnm($collength, 0) if $coltype == 5 or $coltype ==
> 8;
> $qualifier = fixnm($collength, 1) if $coltype == 13;
> $qualifier = fixdt($collength) if $coltype == 10 or $coltype ==
> 14;
>
> $qualifier = "($qualifier)" if $qualifier ne '';
>
> printf "%-20s %-20s %-25s\\n",
> $tabname, $colname, "$dtp[$coltype]$qualifier$nonull";
> } # process
>
>
> die <<USAGE unless @ARGV == 1 or @ARGV == 2 ;
> usage: scols <database> [tab]
>
> It will return the tablename, column name and datatype/length of
> columns for each table or for all tables.
>
> USAGE
>
> $dbname = shift;
> $tabname = shift if @ARGV == 1 ;
> $tabname = "and tabname = '" . $tabname . "'" if $tabname ne "" ;
>
> system "dbaccess $dbname <<EOF >/dev/null 2>&1
> set isolation to dirty read ;
> unload to '/tmp/new_scols_$$.out' delimiter ' '
> select
> t.tabname,
> c.colno,
> c.colname,
> c.coltype,
> c.collength
> from
> systables t,
> syscolumns c
> where
> t.tabid = c.tabid and
> t.tabid >= 100
> $tabname
> order by
> t.tabname,
> c.colno
> ;> EOF
> " ;
>
> unless( open TABLIST, "/tmp/new_scols_$$.out" ) {
> #&cleanup ;
> die "Could not get tables." ;
> }
>
> process $row while ( chomp( $row = <TABLIST> ) ) ;
>
> unlink "/tmp/new_scols_$$.out" ;
>
>
>
> #process $row while $row = $cursor->fetchrow_hashref;
>
>
> <<<<<<END FIRST SCRIPT>>>>>>>>>>
>
> <<<< begin sidxn >>>>>>>>
> #!/usr/bin/ksh
>
> PROC_NAME=`basename $0`
>
> trap "cleanup ; exit" 0 1 2 15
>
> function usage {
> printf "\\n\\n Usage: %s <database> [tabname] \\n\\n" "$PROC_NAME" 1>&2
> exit 1
> }
>
> function cleanup {
> rm -f /tmp/${PROC_NAME}_$$.out
> }
>
> if [ $# -lt 1 -o $# -gt 2 ] ; then
> usage
> fi
>
> if [ $# -eq 2 ] ; then
> where=" and t.tabname = \\"$2\\""
> else
> where=""
> fi
>
> dbaccess $1 <<EOF 1>/dev/null 2>/dev/null
> set isolation to dirty read;
> unload to /tmp/${PROC_NAME}_$$.out
> select
> tabname,
> idxname,
> idxtype,
> c1.colname,
> case
> when i.part1 < 0 then 'D'
> when i.part1 > 0 then 'A'
> else null
> end,
> c2.colname,
> case
> when i.part2 < 0 then 'D'
> when i.part2 > 0 then 'A'
> else null
> end,
> c3.colname,
> case
> when i.part3 < 0 then 'D'
> when i.part3 > 0 then 'A'
> else null
> end,
> c4.colname,
> case
> when i.part4 < 0 then 'D'
> when i.part4 > 0 then 'A'
> else null
> end,
> c5.colname,
> case
> when i.part5 < 0 then 'D'
> when i.part5 > 0 then 'A'
> else null
> end,
> c6.colname,
> case
> when i.part6 < 0 then 'D'
> when i.part6 > 0 then 'A'
> else null
> end,
> c7.colname,
> case
> when i.part7 < 0 then 'D'
> when i.part7 > 0 then 'A'
> else null
> end,
> c8.colname,
> case
> when i.part8 < 0 then 'D'
> when i.part8 > 0 then 'A'
> else null
> end,
> c9.colname,
> case
> when i.part9 < 0 then 'D'
> when i.part9 > 0 then 'A'
> else null
> end,
> c10.colname,
> case
> when i.part10 < 0 then 'D'
> when i.part10 > 0 then 'A'
> else null
> end,
> c11.colname,
> case
> when i.part11 < 0 then 'D'
> when i.part11 > 0 then 'A'
> else null
> end,
> c12.colname,
> case
> when i.part12 < 0 then 'D'
> when i.part12 > 0 then 'A'
> else null
> end,
> c13.col