Forum Stats

  • 3,816,002 Users
  • 2,259,128 Discussions
  • 7,893,362 Comments

Discussions

how would i compare if two indexes are identical?

2

Answers

  • Manik
    Manik Member Posts: 2,908 Gold Trophy

    Well the problem with that package that in your case where your indexed columns change, the pacakge (correct to be honest) will not be able to generate sql alter statement (understandable).. And eventually you end up with ora-39287.

    Cheers,

    Manik.

  • Yes, I just notice that it doesn't check the index columns if they are the same or not. Than this case what should I do? I think "dbms_metadata_diff.compare_alter" not a option for me to use...

  • Manik
    Manik Member Posts: 2,908 Gold Trophy

    Check this link and see if the schema comparator script can be of your use.

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:941629680330

    Cheers,

    Manik.

  • Thanks Manik, I will check this out!... Hope find something here

  • padders
    padders Member Posts: 1,073 Silver Trophy

    > What does 'identical' even mean to you in terms of indexes?

  • By saying IDENTICAL If you mean DUPLICATE indexes, it would make sense.

    To list all duplicate indexes use following SQL statement -


    SELECT 
      /*+ RULE */ 
      tab_owner.name owner, t.name table_name, 
      o1.name || '(' || DECODE(bitand(i1.property, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name , 
      o2.name || '(' || DECODE(bitand(i2.property, 1), 0, 'N', 1, 'U', '*') || ')' including_index_name 
    FROM sys.USER$ tab_owner, sys.OBJ$ t, sys.IND$ i1, sys.OBJ$ o1, sys.IND$ i2, sys.OBJ$ o2 
    WHERE i1.bo# = i2.bo# AND i1.obj# <> i2.obj# AND i2.cols >= i1.cols AND i1.cols > 0 AND
      i1.cols = ( SELECT /*+ ORDERED */ COUNT(1) FROM sys.ICOL$ cc1, sys.icol$ cc2 
      WHERE cc2.obj# = i2.obj# AND cc1.obj# = i1.obj# AND 
      cc2.pos# = cc1.pos# AND cc2.COL# = cc1.COL#) AND 
      i1.obj# = o1.obj# AND i2.obj# = o2.obj# AND t.obj# = i1.bo# AND 
      t.owner# = tab_owner.USER# AND tab_owner.name LIKE '<schema name>' 
    ORDER BY 1, 2
    
    Lalit Kumar B
  • Erhan_toronto
    Erhan_toronto Member Posts: 241
    edited Jul 25, 2013 5:31PM

    Hi manik,

    Can you please check this:

    I am using below query to compare two indexes from 2 different users but even though index name and columns are same... result shows me they are different.. what I am doing wrong? Thanks

    WITH t AS

            (SELECT COUNT (DISTINCT index_owner || index_name || indexed_cols)

                       cnt

               FROM (  SELECT index_owner,

                              index_name,

                              listagg (column_name, ',')

                                 WITHIN GROUP (ORDER BY column_position)

                                 indexed_cols

                         FROM dba_ind_columns

                        WHERE index_name='XPKTBL_A'

                     GROUP BY index_owner, index_name))

    SELECT CASE

              WHEN cnt > 1 THEN 'Indexes are different'

              WHEN cnt = 0 THEN 'Indexes dont exist'

              WHEN cnt = 1 THEN 'Indexes are identical'

           END

              commnt

      FROM t

    Result:

    Indexes are different

    but Actually if you check below they are same After when I run this query:

    SELECT index_owner,

             index_name,

             listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position)

                indexed_cols

        FROM dba_ind_columns

       WHERE index_name='XPKTBL_A'

    GROUP BY index_owner, index_name;

    Result:

    Index_ownerIndex_nameIndex_cols
    USER1XPKTBL_AFIELD_A1
    USER2XPKTBL_AFIELD_A1
  • jgarry
    jgarry Member Posts: 13,844 Gold Crown

    The owners are different, so there are 2.  2 > 1.

This discussion has been closed.