Posting from the Informix-list

Can anyone help me with a sysmaster tables question? Given a table name and a list of column names I would like to know if there is an index on that table with those columns, and if so what is the name of that index? I have been working on this and all I can get is a list of indexes on a given table. Thanks. Andrew Ford Simplified Telesys 5000 Plaza on the Lake Suite 170 Austin, TX 78746 United States (512) 425-9767

Andrew Ford wrote: > > Can anyone help me with a sysmaster tables question? > > Given a table name and a list of column names I would like to know if there is an index on that table with those columns, and if so what is the name of that index? > > I have been working on this and all I can get is a list of indexes on a given table. OK, this is not as hard as it looks but it will wear your fingers down unless the list of columns is short. You can implement it in SPL ONLY if the column list is fixed which is not practical or particularly useful but in 4GL or ESQL/C it is certainly doable: This assumes that you care which order the columns are listed in the index. If not the algorithm is suitably more complex. Steps ----------------- 1) SELECT colno FROM syscolumns c, systables t WHERE c.tabid = t.tabid AND t.tabname = "sometable" AND c.colname IN ("firstcol", "secondcol",...); If the column list is not fixed you can build the IN clause in code and prepare the statement then as you FETCH the column numbers build the statement string below and prepare that: 2) SELECT idxname FROM sysindexes i, systables t WHERE i.tabid = t.tabid AND t.tabname = "sometable" AND abs(i.part1) = col_number[0] AND abs(i.part2) = col_number[1] .... OK now an attempt to use pure SQL because if I don't Jonathan will and I was already shown up once today .... SELECT idxname FROM sysindexes i, systables t WHERE i.tabid = t.tabid AND t.tabname = "sometable" AND abs(i.part1) = ( SELECT colno FROM syscolumns c1 WHERE c1.tabid = t.tabid AND colname = "firstcol" ) AND abs(i.part2) = ( SELECT colno FROM syscolumns c1 WHERE c1.tabid = t.tabid AND colname = "secondcol" ) ...; You can readily see how messy this will become if you do not care about the order of columns and want to see all indexes that list the columns in any order as you will have to search part1...part16 (in IDS or part8 in SE) for each column. Art S. Kagel

In article <7opmms$2ng$1@news.xmission.com>, Andrew Ford <aford@simpletel.com> wrote: > > Can anyone help me with a sysmaster tables question? > > Given a table name and a list of column names I would like to know if there is an index on that table with those columns, and if so what is the name of that index? > > I have been working on this and all I can get is a list of indexes on a given table. > > Thanks. > > Andrew Ford > Simplified Telesys > 5000 Plaza on the Lake > Suite 170 > Austin, TX 78746 > United States > > (512) 425-9767 > > Andrew Invest in a copy of SPL Workstation from AGS Limited (www.agsltd.com). There is a downloadable eval from their Web Site. I have no connection whatsoever with AGS Limited, other than being a satisfied user (wuick - have him stuffed). Regards Glyn Balmer -- If it always works, why don't parachutists pull the emergency 'chute first? Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Andrew Ford wrote: > Can anyone help me with a sysmaster tables question? > > Given a table name and a list of column names I would > like to know if there is an index on that table with > those columns, and if so what is the name of that index? This is a non-trivial question in general; I don't have a fully worked out solution to offer, but maybe these pointers will be helpful. First, I'd expect to be working with sysindexes, syscolumns and perhaps systables from the database's system catalogue, not using the sysmaster database at all. Next, you need to decide how many assumptions you can make about your indexes. Are the columns given to you in the order in which they must appear in the index, or can the index be any permutation of those columns? Also, could any of your indexes make use of the DESC option (descending order)? If so, the column numbers in sysindexes will be negated and you have to mess around with the ABS function. Assuming that any sequence of columns is acceptable, then you either need to convert the column numbers in sysindexes into column names, or you need to turn the column names in your list into column numbers -- the latter is probably easier. Next, you need to be able to compare two lists of column numbers, one for the specified index, and one for the index currently being checked. This is the bit which I'm not sure how to do -- I hope someone else can come up with a good suggestion for you. If you create a temp table putative_index with the column numbers of the list of columns you were given, and if you mangle sysindexes into another temporary table, normalized_indexes, containing the index name and the column number, then you may be able to do something like: SELECT DISTINCT indexname FROM normalized_indexes N1 WHERE NOT EXISTS (SELECT * FROM putative_index WHERE ColNo NOT IN (SELECT ColNo FROM normalized_indexes N2 WHERE N2.indexname = N1.indexname)) Basically, the possible indexes are those for which there is no column in the putative index which is not also listed in the normalized index. The code is untested - you were warned. Note that creating both the putative index and (especially) the normalized index is non-trivial if you have to do it in full. With OnLine, the normalized index involves a 16-way UNION (one per possible index column). -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>