Forum Stats

  • 3,768,297 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

how would i compare if two indexes are identical?

Erhan_toronto
Erhan_toronto Member Posts: 241
edited Jul 31, 2013 4:12AM in SQL & PL/SQL

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

Umesh P

Best Answer

«13

Answers

  • Manik
    Manik Member Posts: 2,906 Gold Trophy

    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.

    Umesh PManik
  • Unknown
    edited Jul 24, 2013 11:25PM

    >

    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?

  • Hi Manik,

    can I have the result like below?

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

  • Manik
    Manik Member Posts: 2,906 Gold Trophy

    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.

    Umesh P
  • 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).



  • why is the reason that it is not efficient ?

  • 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
    Manik Member Posts: 2,906 Gold Trophy
    edited Jul 24, 2013 11:53PM

    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.

  • Actually I did upgrade my version to 11gR2

  • 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?

This discussion has been closed.