Re: querying systables
Hi Nick,
>From: uunet!tetra.co.uk!nick (Nick Rumble)
>Subject: querying systables
>Date: Wed, 10 Jun 92 21:17:49 BST
>Message-Id: <9206102017.aa11180@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 (johnl@obelix.informix.com) #include <disclaimer.h>