[Progress Communities] [Progress OpenEdge ABL] Forum Post: RE: Check for redundant indexes

[Rob Fitzpatrick]

Here is an attempt at this: /* redundantindexes.p * * An index I is redundant if there exists an index I' * on the same table that satisfies the following conditions: * - the components of I are the leading components of I', in the same order * - the sort order of the components of I (ASC or DESC) are the same in I' * - it is not the case that I is unique and I' is non-unique * * Rob Fitzpatrick * 09/28/2018 */ define variable IndexString as character no-undo. define variable i as integer no-undo. define variable Index-Name as character no-undo. define variable Component-String as character no-undo. define variable Is-Unique as logical no-undo. define temp-table ttFile no-undo field TableName as character format "x(32)" field IndexCount as integer index TableName TableName . define temp-table ttIndex no-undo field TableName as character format "x(32)" field IndexName as character format "x(32)" field ComponentString as character format "x(80)" field IsUnique as logical index ComponentString TableName ComponentString . define buffer bttIndex for ttIndex. for each dictdb._file no-lock where _file._file-number > 0 and _file._file-number 1 with frame a: for each ttIndex where ttIndex.TableName = ttFile.TableName with frame a: for each bttIndex where ttIndex.TableName = bttIndex.TableName and ttIndex.IndexName <> bttIndex.IndexName and bttIndex.ComponentString begins ttIndex.ComponentString with frame a: if ttIndex.IsUnique and not bttIndex.IsUnique then next. display ttIndex.TableName ttIndex.IndexName @ Index-Name format "x(32)" ttIndex.IsUnique @ Is-Unique ttIndex.ComponentString @ Component-String format "x(120)" . down with frame a. display bttIndex.IndexName @ Index-Name format "x(32)" bttIndex.IsUnique @ Is-Unique bttIndex.ComponentString @ Component-String format "x(120)" with width 200 . down(1) with frame a. end. end. end. Feel free to critique.

Continue reading...