Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to compare index names and columns from different user?

Erhan_torontoJul 25 2013 — edited Jul 25 2013

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

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 22 2013
Added on Jul 25 2013
5 comments
663 views