Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    34

    Unanswered: How to find duplicate indexes

    Am trying to get duplicate indexes by using sysindexes and syskeys , the remaining problem is that the sql does not filter out the order of columns yet :
    So am able to find indexes with the same columns on the same table (indexes created by a different user) but the sql does not distinguish the order of columns .. so it will still log the indexes with the same columns on the same table with a different order of columns .

    SELECT
    SUBSTR(TBNAME,1,12) AS TBNAME,SUBSTR(IXNAME,1,12) AS IXNAME,
    SUBSTR(COLNAME,1,12) AS COLNAME,COLSEQ,SUBSTR(IXCREATOR,1,10) AS
    IXCREATOR,COUNT(*) AS COUNT
    FROM SYSIBM.SYSINDEXES
    JOIN SYSIBM.SYSKEYS ON NAME=IXNAME
    WHERE DBNAME = 'DATABASE'
    GROUP BY TBNAME,IXNAME,COLNAME,COLSEQ,IXCREATOR
    HAVING COUNT(*) > 1
    WITH UR;

    How can I get rid of the indexes on the same table with the same columns but a different order ? Thanks .

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Thumbs up

    I found no SYSIBM.SYSKEYS on my system, what DB2 version on what platform are you using?

    How about this?:
    Code:
    SELECT INDEX1.INDSCHEMA,
    	INDEX1.INDNAME,
    	INDEX1.DEFINER,
    	INDEX1.TABSCHEMA,
    	INDEX1.TABNAME,
    	INDEX1.COLNAMES,
    	INDEX1.COLCOUNT,
    	INDEX1.INDEXTYPE,
    	INDEX2.INDSCHEMA,
    	INDEX2.INDNAME,
    	INDEX2.DEFINER,
    	INDEX2.TABSCHEMA,
    	INDEX2.TABNAME,
    	INDEX2.COLNAMES,
    	INDEX2.COLCOUNT,
    	INDEX2.INDEXTYPE
    FROM "SYSCAT"."INDEXES" as INDEX1,
    	"SYSCAT"."INDEXES" as INDEX2
    WHERE INDEX1.TABSCHEMA = INDEX1.TABSCHEMA AND
    	INDEX1.TABNAME = INDEX1.TABNAME AND
    	NOT (	INDEX1.INDSCHEMA = INDEX1.INDSCHEMA AND
    		INDEX1.INDNAME = INDEX1.INDNAME AND
    		INDEX1.DEFINER = INDEX1.DEFINER ) AND
    	INDEX1.COLCOUNT <= INDEX2.COLCOUNT AND
    	INDEX1.INDSCHEMA || INDEX1.INDNAME || INDEX1.DEFINER <
    		 INDEX1.INDSCHEMA || INDEX1.INDNAME || INDEX1.DEFINER	
    //	AND POSSTR(INDEX2.COLNAMES, INDEX1.COLNAMES) >= 0
    First I select indexes that are defined on the same table, then I exclude those cases where the indexes are in fact one and the same index, then I take the index with the lowest number of columns and/or the "smallest" name.
    With
    Code:
    POSSTR(INDEX2.COLNAMES, INDEX1.COLNAMES) >= 0
    I wanted to see if INDEX2 contains the same sequence of columns as INDEX1, possibly preceded by or followed by other columns. I received an error on that line, but I stopped debugging it because I wonder if such duplicate indexes can really occur in a DB2 database. AFAIK DB2 gives an error message when one tries to create an index with the same description as one already present.

    So I wonder if this whole thing has any merit.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2007
    Posts
    34
    We're using DB2 ver 8 on z/os

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Try this query:

    SELECT A.* FROM SYSIBM.SYSINDEXES A INNER JOIN SYSIBM.SYSINDEXES B
    ON A.TBNAME = B.TBNAME
    AND A.TBCREATOR = B.TBCREATOR
    AND A.COLCOUNT = B.COLCOUNT
    AND NOT ( A.NAME = B.NAME AND A.CREATOR = B.CREATOR )
    WHERE A.COLCOUNT =
    ( SELECT COUNT(*) FROM
    SYSIBM.SYSKEYS C
    WHERE C.IXNAME = B.NAME
    AND C.IXCREATOR = B.CREATOR
    AND ( COLNAME , COLSEQ, ORDERING )IN
    ( SELECT COLNAME,COLSEQ, ORDERING
    FROM SYSIBM.SYSKEYS D
    WHERE D.IXNAME = A.NAME
    AND D.IXCREATOR = A.CREATOR ))
    Last edited by umayer; 07-06-07 at 05:16.

  5. #5
    Join Date
    Jul 2007
    Posts
    34

    Thanks a lot

    this last sql works fine ... Now I've got another question regarding DB2 ver 8 :

    Suppose TABLE X with 2 indexes :
    Index 1
    COL A
    COL B

    and Index 2
    COL A
    COL B
    COL C
    COL D

    is this Index 1 in DB2 ver 8 obsolete like in Oracle version 9,10 as Oracle has the ability to choose only some cols of index 2 .

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    of course DB2 can use Index 2 even if only COL A and COL B are part of the WHERE-clause.
    But the use of Index 1 might be more efficient in that case.

  7. #7
    Join Date
    Jul 2007
    Posts
    34
    Thanks a lot for your help umayer!

  8. #8
    Join Date
    Jul 2007
    Posts
    34
    Quote Originally Posted by umayer
    Try this query:

    SELECT A.* FROM SYSIBM.SYSINDEXES A INNER JOIN SYSIBM.SYSINDEXES B
    ON A.TBNAME = B.TBNAME
    AND A.TBCREATOR = B.TBCREATOR
    AND A.COLCOUNT = B.COLCOUNT
    AND NOT ( A.NAME = B.NAME AND A.CREATOR = B.CREATOR )
    WHERE A.COLCOUNT =
    ( SELECT COUNT(*) FROM
    SYSIBM.SYSKEYS C
    WHERE C.IXNAME = B.NAME
    AND C.IXCREATOR = B.CREATOR
    AND ( COLNAME , COLSEQ, ORDERING )IN
    ( SELECT COLNAME,COLSEQ, ORDERING
    FROM SYSIBM.SYSKEYS D
    WHERE D.IXNAME = A.NAME
    AND D.IXCREATOR = A.CREATOR ))

    to eliminate duplicates (because of the multiple rows in SYSKEYS) and for clarity, its usefull to add a group by/order by
    GROUP BY A.DBNAME
    ,A.CREATOR
    ,A.TBNAME
    ,A.NAME
    ORDER BY A.DBNAME

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •