RE: Query to find indexes for a specific table
Jerry
Here is a general algorithm that you could use:
/* systables - table catalog.
sysindexes - index catalog.
syscolumns - table column catalog.
*/
select tabname from systables
where tabid > 99foreach tabname
select indexname, part1, ..., part16
from sysindexes a, systables b
where a.tabid = b.tabid
and b.tabname = "$tabname"
foreach indexname
select colname
from syscolumns a, systables b
where a.tabid = b.tabid
and colno = part1
...
select colname
from syscolumns a, systables b
where a.tabid = b.tabid
and colno = part16
end foreach
end foreach
You will find information about system catalogs in the SQL ref manual.
HTH
Sujit
----------
From: Jerry Gitomer[SMTP:jgitomer@p3.net]
Sent: Wednesday, November 19, 1997 10:35 PM
To: informix-list@rmy.emory.edu
Subject: Query to find indexes for a specific table
Is there a query I can set up that will permit our computer
operators to determine if an index exits on a table?
We have a job that loads four tables and creates a record_id
index on each. Last week one of the programmers had to make
some changes to one of the tables. Somehow he blew the indexes
on three of the four tables (they only have 230,000 rows each).
Needless to say the four table join took forever. (Literally
three orders of magnitude slower than usual).
I know that I can go into DBACCESS and see the indexes for
a table, but I don't plan to on premises when the third shift
operator starts up the joins at 3:00 AM :-) So our operations
manager asked me to come up with an EASY way for an operator
to find out if the indexes exist.
I did Read The Fine Manual, but couldn't find out where Informix
stashes the index names (numbers?) along with the identity of
table being indexed, and the ids of the columns comprising the
index.
Jerry