How to compare index names and columns from different user?

Erhan_toronto

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_owner	Index_name	Index_cols
USER1	XPKTBL_A	FIELD_A1
USER2	XPKTBL_A	FIELD_A1

Frank Kulash

Hi,

Erhan_toronto wrote:

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)
...

So index_owner is 'USER1' in one case, and 'USER2' in the other; right?

A string that starts with 'USER1' will be distinct from a string that starts with 'USER2', no matter what the rest of the string contains.  Maybe you don't want to compare the owners, or maybe you meant to use some other column (such as table_name) instead of index_owner).

.

I hope this answers your question.
If not, post a little sample data (CREATE TABLE, CREATE INDEX and CONNECT statements), and also post the results you want from that data.
Explain, using specific examples, how you get those results from that data.
Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

See the forum FAQ: https://forums.oracle.com/message/9362002

John Spencer

You might also want to check your case condition as well.  I think that you need cnt = 1 THEN 'Indexes are identical' in the last case.

John

Erhan_toronto

Thanks for your reply Frank,

Hope this help,

I am using 11gR2...

UserA:

create table TBL_A (       FIELD_A1 number not null,

FIELD_A2 varchar2(50),

FIELD_A3 date,

FIELD_A4 number(5,2) default 0,

FIELD_A5 varchar2(10) not null

                                );

                                create unique index XPKTBL_A on TBL_A (FIELD_A1);

UserB:

create table TBL_A (       FIELD_A1 number not null,

FIELD_A2 varchar2(20),

FIELD_A4 number(5,2) not null,

FIELD_A5 varchar2(10),

FIELD_A6 number(2) not null

                                );

                                create unique index XPKTBL_A on TBL_A (FIELD_A1, FIELD_A6);

Like to see the result if same index name also has same columns... if yes than index is identical else they are different...

Erhan_toronto

I did notice that when I remove index_owner from my query it shows as index are identical even though they are not!

Unknown

DUPLICATE THREAD!

This is the THIRD thread that asks the same question. Please don't duplicate threads. People can't follow what is happening.

https://forums.oracle.com/thread/2563449?start=15&tstart=0

https://forums.oracle.com/thread/2563840

Mark two of these threads ANSWERED and use the remaining thread for your question.