Oninit cdi / Archive
Talk to an expert →

Re: SMI query for Index column names

Posted in 1998

Mary.K.Pastrell@ecc.com — — source: Informix-list mailing list archive (1991-1998)

--0__=hk7x8mxCIXT6x4YQfk4dzOmvlQZp2aDGMSiSUijWpaU5MkAcqWx49Typ Content-type: text/plain; charset=us-ascii Mary K. Gregory @ ECCI 10/01/98 11:56 AM (Embedded image moved to file: PIC12179.PCX) I've been doing something similar here, using the following query: select tabname, idxname, sum( collength) from systables,sysindexes, syscolumns where sysindexes.tabid = syscolumns.tabid and systables.tabid = syscolumns.tabid and systables.tabid >= 100 and ((sysindexes.part1 = syscolumns.colno) or (sysindexes.part2 = syscolumns.colno) or (sysindexes.part3 = syscolumns.colno) or (sysindexes.part4 = syscolumns.colno) or (sysindexes.part5 = syscolumns.colno) or (sysindexes.part6 = syscolumns.colno) or (sysindexes.part7 = syscolumns.colno) or (sysindexes.part8 = syscolumns.colno) or (sysindexes.part9 = syscolumns.colno) or (sysindexes.part10 = syscolumns.colno) or (sysindexes.part11 = syscolumns.colno) or (sysindexes.part12 = syscolumns.colno) or (sysindexes.part13 = syscolumns.colno) or (sysindexes.part14 = syscolumns.colno) or (sysindexes.part15 = syscolumns.colno) or (sysindexes.part16 = syscolumns.colno)) group by tabname, idxname order by tabname, idxname My goal was to come as close as I could to the length of each index. Again, this is run against the system tables, not the SMI tables, but it seems to work. Nick Nobbe <nnob@loc.gov> on 10/01/98 10:31:58 AM To: Jonathan Leffler <jleffler@informix.com>, "Art S. Kagel" <kagel@bloomberg.net> cc: informix-list@iiug.org (bcc: Mary K. Gregory/AMPAC/ECCI) Subject: Re: SMI query for Index column names Hey, you all, there's got to be an easier way. I know the question is how to using the SMI. I gave up in frustration with the SMI way and just did a dbschema and applied sed and awk to the result. The only gotcha first time was long concatenated indexes that caused line wraps. Yours, Nick At 10:57 AM 9/24/98 -0700, Jonathan Leffler wrote: >On Thu, 24 Sep 1998, Art S. Kagel wrote: >> Douglas Agnew wrote: >> > the solution would appear to be a union across all the part1--part16 >> > where the value is not zero. >> [SNIP] >> > > Let me put in this way. If you use >> > > dbaccess->database->tables->indexes >> > > it shows you all the indexcolumn names for all the indexes. >> > > How can i get this from SMI query. >> >> Actually you are querying the database's system catalog tables not SMI, >> but I won't nit pick ;-) I just want to add that if any column is >> declared as descending in the index (less common in 7.3x since 7.3 can >> use ascending indexes for descending searches) the column number in the >> partxx will be negated so the filter should be: c.colno = abs(x.part1) >> etc. > >Given a tabid as pre-requisite information, this query produces a set of >column names in a single row of data for each index for an OnLine table >(max 16 parts). The simplication to SE with 8 parts is trivial. Note >that this loses the information about whether the columns are ascending >or descending. > >SELECT t0.tabid, t0.idxtype, t0.owner, t0.idxname, > c01.colname col01, c02.colname col02, c03.colname col03, > c04.colname col04, c05.colname col05, c06.colname col06, > c07.colname col07, c08.colname col08, c09.colname col09, > c10.colname col10, c11.colname col11, c12.colname col12, > c13.colname col13, c14.colname col14, c15.colname col15, > c16.colname col16 > FROM "informix".SysIndexes t0, > "informix".SysColumns c01, > OUTER "informix".SysColumns c02, > OUTER "informix".SysColumns c03, > OUTER "informix".SysColumns c04, > OUTER "informix".SysColumns c05, > OUTER "informix".SysColumns c06, > OUTER "informix".SysColumns c07, > OUTER "informix".SysColumns c08, > OUTER "informix".SysColumns c09, > OUTER "informix".SysColumns c10, > OUTER "informix".SysColumns c11, > OUTER "informix".SysColumns c12, > OUTER "informix".SysColumns c13, > OUTER "informix".SysColumns c14, > OUTER "informix".SysColumns c15, > OUTER "informix".SysColumns c16 > WHERE t0.tabid = <<tabid>> > AND ABS(t0.part1) = c01.colno AND t0.tabid = c01.tabid > AND ABS(t0.part2) = c02.colno AND t0.tabid = c02.tabid > AND ABS(t0.part3) = c03.colno AND t0.tabid = c03.tabid > AND ABS(t0.part4) = c04.colno AND t0.tabid = c04.tabid > AND ABS(t0.part5) = c05.colno AND t0.tabid = c05.tabid > AND ABS(t0.part6) = c06.colno AND t0.tabid = c06.tabid > AND ABS(t0.part7) = c07.colno AND t0.tabid = c07.tabid > AND ABS(t0.part8) = c08.colno AND t0.tabid = c08.tabid > AND ABS(t0.part9) = c09.colno AND t0.tabid = c09.tabid > AND ABS(t0.part10) = c10.colno AND t0.tabid = c10.tabid > AND ABS(t0.part11) = c11.colno AND t0.tabid = c11.tabid > AND ABS(t0.part12) = c12.colno AND t0.tabid = c12.tabid > AND ABS(t0.part13) = c13.colno AND t0.tabid = c13.tabid > AND ABS(t0.part14) = c14.colno AND t0.tabid = c14.tabid > AND ABS(t0.part15) = c15.colno AND t0.tabid = c15.tabid > AND ABS(t0.part16) = c16.colno AND t0.tabid = c16.tabid;> >If you want to list these as a series of rows then you use a 16-way >UNION as suggested. > >Yours, >Jonathan Leffler (jleffler@informix.com) #include <witticism.h> >Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN >Informix IDN for D4GL & Linux -- http://www.informix.com/idn > > ********************* Nick Nobbe NLS/BPH Library of Congress nnob@loc.gov --0__=hk7x8mxCIXT6x4YQfk4dzOmvlQZp2aDGMSiSUijWpaU5MkAcqWx49Typ Content-type: application/octet-stream; name="PIC12179.PCX" Content-transfer-encoding: base64 CgUBCAAAAADCAQ8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAABwwEBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAP/wf/B/8H/wf/B/8H5QfSB8QHxw/ED8IPD/8L/QsPwwsPwwsPwwsPwwsP wwsPwwsPwwsPwwsPwwsPwwsPwwsPwwsPwwsPwwsPwwsPwwsPwwsPwwsPwwsPwwsPCw8LD8MLDwsP Cw8LDwsPCw8LDwsPCw8LDwsPC8MPCw8LDwvDDwvDDwvDDwvDDwvDDwvDDwvDDwvDDwvDDwvDDwvD DwvDDwvDDwvDDwvDDwvDDwvDDwvDDwvDDwvDDwv/D+EP0Q/ID8QPwg8PD+ULD8cLD8MLD8MLD8ML D8MLD8MLDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsP Cw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8L DwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsP Cw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8L DwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsP Cw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPC8MPC8MPC8MPC8MPC8cPC9cPzA/G D8MPDw//C/8L2AsPxwsPwwsPwwsPwwsPwwsPwwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8L DwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsP Cw8LDwvDDwvDDwvDDwvDDwvDDwvHDwv/D+4P1w/MD8YPww8PD8cLD8cLD8cLD8MLD8MLD8MLD8ML D8MLD8MLD8MLD8MLD8MLD8MLD8MLD8MLD8MLD8MLD8MLD8MLDwsPCw/DCw8LDwsPwwsPCw8LDwsP Cw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8LDwsPCw8L DwsPCw8LDw

Related threads