SQL & PL/SQL

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.

>

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;

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.

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?

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?

Well the problem with that package that in your case where your indexed columns change, the pacakge (correct to be honest) will not be able to generate sql alter statement (understandable).. And eventually you end up with ora-39287.

Cheers,

Manik.

Yes, I just notice that it doesn't check the index columns if they are the same or not. Than this case what should I do? I think "dbms_metadata_diff.compare_alter" not a option for me to use...

Check this link and see if the schema comparator script can be of your use.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:941629680330

Cheers,

Manik.

Thanks Manik, I will check this out!... Hope find something here

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

By saying IDENTICAL If you mean DUPLICATE indexes, it would make sense.

To list all duplicate indexes use following SQL statement -

SELECT 
  /*+ RULE */ 
  tab_owner.name owner, t.name table_name, 
  o1.name || '(' || DECODE(bitand(i1.property, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name , 
  o2.name || '(' || DECODE(bitand(i2.property, 1), 0, 'N', 1, 'U', '*') || ')' including_index_name 
FROM sys.USER$ tab_owner, sys.OBJ$ t, sys.IND$ i1, sys.OBJ$ o1, sys.IND$ i2, sys.OBJ$ o2 
WHERE i1.bo# = i2.bo# AND i1.obj# <> i2.obj# AND i2.cols >= i1.cols AND i1.cols > 0 AND
  i1.cols = ( SELECT /*+ ORDERED */ COUNT(1) FROM sys.ICOL$ cc1, sys.icol$ cc2 
  WHERE cc2.obj# = i2.obj# AND cc1.obj# = i1.obj# AND 
  cc2.pos# = cc1.pos# AND cc2.COL# = cc1.COL#) AND 
  i1.obj# = o1.obj# AND i2.obj# = o2.obj# AND t.obj# = i1.bo# AND 
  t.owner# = tab_owner.USER# AND tab_owner.name LIKE '<schema name>' 
ORDER BY 1, 2

What is this?

Duplicate

Hi manik,

Can you please check this:

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:

The owners are different, so there are 2.  2 > 1.

I know owners are different but I only want to compare the columns not the owners... can we do that?

Have you considered doing the comparison the old fashioned way and just look at the index definitions?

You could have compared them manually in a few minutes and you have already spent a day on it.

Erhan_toronto wrote:

What is this?

What do you expect it to be? You wanted a query to find out the duplicate indexes, right? So i gave you the query to do that. It will list out all the duplicate indexes.

Analyze on list and drop the duplicate indexes.

If you have an answer you should mark this thread as "answered", so people won't waste their time on this question.  

Cheers, APC