Forum Stats

  • 3,769,359 Users
  • 2,252,957 Discussions
  • 7,875,002 Comments

Discussions

Re: How to compare index columns from different users with same table name.... if the index columns

Erhan_toronto
Erhan_toronto Member Posts: 241
edited Jul 30, 2013 9:25PM in SQL & PL/SQL

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

I change index_owner to table_name still doesnt show the differences:

WITH t AS

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

                   cnt

           FROM (  SELECT table_name,

                          index_name,

                          listagg (column_name, ',')

                             WITHIN GROUP (ORDER BY column_position)

                             indexed_cols

                     FROM dba_ind_columns

                    WHERE index_name='XPKTBL_A' and table_name= 'TBL_A'

                 GROUP BY table_name, 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 identical

Comments

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    Try this

    select decode

           (

                sum(decode(column_name_1, column_name_2, 0, 1))

              , 0

              , 'Identical'

              , null

              , 'Not exist'

              , 'Different'

           ) index_status

      from (

              select column_position

                   , max(decode(index_owner, 'USERA' , column_name)) column_name_1

                   , max(decode(index_owner, 'USERB', column_name)) column_name_2

                from all_ind_columns

               where table_name = 'TBL_A'

               group

                  by column_position

           )

  • Thanks Karthick for your reply and sorry for delayed response...

This discussion has been closed.