RE: Query to find indexes for a specific table
select t.tabname
from systables t, sysindexes i, syscolumns c
where t.tabid = i.tabid and t.tabid = c.tabid
and i.part1 = c.colno
and c.colname = 'record_id'
yields all tables which have a column 'record_id' indexed.
In your case you can speed up the query by adding
and t.tabname in ('mybigtab1', 'mybigtab2', 'mybigtab3', 'mybigtab4')
------------------------------------------------------------------------
-
Frido van Orden
FAA Partners BV
Planetenbaan 117
3606 AK Maarssen
The Netherlands
Phone: +31-346-587076
Fax: +31-346-587086
Email: fridoo@faapartners.com
------------------------------------------------------------------------
-
} -----Original Message-----
} From: Jerry Gitomer [SMTP:jgitomer@p3.net]
} Sent: Thursday, November 20, 1997 6:35 AM
} 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