how would i compare if two indexes are identical?

Erhan_toronto

I like to compare if two indexes from both users are identical. Is there any way to to that. I need similer to below query:

select CASE (2*(select count(*) from (select * from XPKTBL_A INTERSECT select * from XPKTBL_A )) - (select count(*) from XPKTBL_A ) - (select count(*) from XPKTBL_A )) WHEN 0 THEN 'Indexes are identical' ELSE 'Indexes are different' END "Result" from  DUAL;

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

Erhan_toronto

Thanks for reply Lalit and sorry for the delayed response.. I got my answer allready.

Manik

Try 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 LIKE '%XPKTBL_A%'

GROUP BY index_owner, index_name;

--------------

Cheers,

Manik.

Unknown

>

I like to compare if two indexes from both users are identical.

>

And why is it you think you need to do that? Explain how that would even be useful.

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

Erhan_toronto

Hi Manik,

can I have the result like below?

'Indexes are identical' ELSE 'Indexes are different' END "Result" from  DUAL;

Manik

Not efficient way but here it is...

----------------------------------------

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 LIKE '%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

----------------------------------

Cheers,

Manik.

Erhan_toronto

Thanks for reply RP,

The reason that I want to compare index if they are identical or not I want to run another query depends on the result is correct or not like  below.:

if result is true (yes they are identical) than do nothing, else (run another query).

Erhan_toronto

why is the reason that it is not efficient ?

Erhan_toronto

Is this query checks all the indexes in userA or it checks both userA and UserB indexes and if it finds same index name from both user. than do the comparison?

Manik

Because there are efficient ways to deal with such comparisons. (which I have practically not used in my little experience)

Something like this will be more efficient (but even here I am abusing the dbms_metadata_diff package just because of the reason that you were getting null when you used it in 11gr1 (your version) when you asked similar question yesterday.

---------------------------------------------

SELECT CASE

          WHEN INSTR (NVL (dbms_metadata_diff.compare_alter ('INDEX',

                                                             'XPKTBL_A',

                                                             'XPKTBL_A',

                                                             'SCOTT1',

                                                             'SCOTT2'),

                           '0'),

                      'ORA-39287') > 0 THEN

             'INDEXES DIFFERENT'

             ELSE 'INDEXES SAME'

       END

          comparison

  FROM DUAL;

---------------------------------------

Cheers,

Manik.

Erhan_toronto

Actually I did upgrade my version to 11gR2

Erhan_toronto

Thanks Manik, I wasn't sure that I can also compare indexes using dbms_metadata_diff.compare_alter .... I did compare my table and it worked perfectly, now I want to compare my indexes.. I need to change my question little bite than... instead of getting result like same or different. just do the comparison " using dbms_metadata_diff.compare_alter " ? than my query looks like:

select dbms_metadata_diff.compare_alter ('INDEX','XPKTBL_A','XPKTBL_A','USER1','USER2') from dual; is this correct?