Forum Stats

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

Discussions

How to compare index names and columns from different user?

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

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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    edited Jul 25, 2013 4:25PM

    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
    John Spencer Member Posts: 8,567

    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

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

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

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

This discussion has been closed.