querying systables
Nick Rumble
I have to perform frequent validation against the tabname
column within systables. This column is *not* indexed and
therefore can be quite costly if a database has a large number
of tables (200+).
Does Informix have plans to index this column (or allow others
to do so)?
Regards,
Nick Rumble.
Dave Snyder
> I have to perform frequent validation against the tabname
> column within systables. This column is *not* indexed and
> therefore can be quite costly if a database has a large number
> of tables (200+).
Someone correct me if I'm wrong, but I thought systables had two indexes.
One on tabname & owner and the other on just tabid.
DAS
--
David A. Snyder @ Snide Inc. - Folcroft, PA Current Release
is db4glgen-3.6
UUCP: ..!uunet!das13!dave INTERNET: dave....@snide.com
Jonathan Leffler
>From: uunet!tetra.co.uk!nick (Nick Rumble)
>Subject: querying systables
>Date: Wed, 10 Jun 92 21:17:49 BST
>Message-Id: <920610201...@saturn.tetra.co.uk>
>X-Informix-List-Id: <list.1232>
>
>I have to perform frequent validation against the tabname
>column within systables. This column is *not* indexed and
>therefore can be quite costly if a database has a large number
>of tables (200+).
Oh yes it is...
Standard DBSCHEMA won't produce a schema for systables -- it comes up with
the improbable message: No table or view systables.
My non-standard dbschema run against a 4.10 OnLine database produced:
CREATE TABLE {informix.}systables
(
tabname CHAR(18),
owner CHAR(8),
partnum INTEGER,
tabid SERIAL,
rowsize SMALLINT,
ncols SMALLINT,
nindexes SMALLINT,
nrows INTEGER,
created DATE,
version INTEGER,
tabtype CHAR(1),
locklevel CHAR(1),
npused INTEGER,
fextsize INTEGER,
nextsize INTEGER,
flags SMALLINT,
site CHAR(18),
dbname CHAR(18)
);
CREATE UNIQUE INDEX {informix.}tabid ON {informix.}systables(tabid);
CREATE UNIQUE INDEX {informix.}tabname ON {informix.}systables(tabname, owner);
The Tabname index looks suspiciously like an index on Systables.Tabname to me.
Now, PERFORM would probably complain about no index on tabname because
there isn't a column on just tabname -- I find this to be more of a problem
with Sysusers than Systables, but that's a different matter. That is a
defect in PERFORM -- it doesn't fully understand indexes. But if you scan
for tables using "where tabname = 'fred'", you will be using the index.
Have you tried checking with SET EXPLAIN ON?
Yours,
Jonathan Leffler (jo...@obelix.informix.com) #include <disclaimer.h>
David Cortesi
>
> I have to perform frequent validation against the tabname
> column within systables. This column is *not* indexed and
> therefore can be quite costly if a database has a large number
> of tables (200+).
Since 4.0 (oldest manual in reach) systables has a composite
index on tabname, owner; which would be usable in a search on tabname.
Others have posted other remarks (the suggestion to update
statistics sounds good) but...
In your shoes, I would consider that 18*200 ~= 4K bytes is a pretty
reasonable overhead, and I'd think about reading the whole tabname
column one time and making an in-memory lookup table. That should
make a "frequent" validation go pretty good...