Skip to Main Content
  • Questions

  • Compare the Indexes of tables from two different databases

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, mahdi.

Asked: October 28, 2015 - 9:27 am UTC

Last updated: March 07, 2018 - 11:29 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

hi Tom

I would like to compare indexes of tables from defferent database.
challenge is that the tables have index name different.

example
db1
schema1
table1-->
--> index1 (col1,col2)
--> index2 (col11,col22,col33)
--> index3 (col111,col222)

db2
schema2
table1-->
--> indexA (col1,col2)
--> indexB (col11,col22,col33)

Now index1 is different in name with indexA and respectively.
In this senario i would like to find out the index3 which is missing in db2.




and Chris said...

I'm creating two users in the same database to show the principle. To do this across databases you'll need to have a db link in place and modify the query appropriately.

You can find the missing indexes by using the *_ind_columns views. Check to see if there's an entry here that doesn't exist in the other schema/database. You can do this by comparing on table and column names.

You also need to check on column positions. In the example below U1 as an index on (y, z). U2 has an index on (z, y). These aren't the same! Therefore we should report I2 and I3 as missing from the second schema:

create user u1 identified by "u1";
create user u2 identified by "u2";

create table u1.t ( x int, y int, z int );
create table u2.t ( x int, y int, z int );

create index u1.i1 on u1.t (x);
create index u1.i2 on u1.t (y, z);
create index u1.i3 on u1.t (z, y, x);

create index u2.i1 on u2.t (x);
create index u2.i2 on u2.t (z, y);

select distinct i1.index_name 
from   dba_ind_columns i1
where  table_owner = 'U1'
and    not exists (
  select * from dba_ind_columns i2
  where  i2.table_owner = 'U2'
  and    i1.table_name = i2.table_name
  and    i1.column_name = i2.column_name
  and    i1.column_position = i2.column_position
);

INDEX_NAME                                                                     
--------------------------------------------------------------------------------
I3                                                                              
I2


To get all the differences, you should also do the reverse check.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Index comparison deficiency

Leontinus, March 06, 2018 - 7:45 am UTC

I created a same code and found a deficiency.
I searched the indexes of a1 in table a2. Look at this example:

create table a1 ( x int, y int, z INT, w INT, v int );
create table a2 ( x int, y int, z INT, w INT, v int );

create index a1_1 on a1 (x, z);
create index a1_2 on a1 (x, y);


create index a2_1 on a1 (x, w);
create index a2_2 on a1 (x, v);

create index a2_3 on a1 (w, z);
create index a2_4 on a1 (w, y);


                       
select distinct i1.index_name 
  from user_ind_columns i1
 where table_name='A1'
   and not exists (select * 
                     from user_ind_columns i2
                    where i2.table_name = 'A2'
                      and i1.column_name = i2.column_name
                      and i1.column_position = i2.column_position
                  );

No rows.


Chris Saxon
March 06, 2018 - 5:57 pm UTC

Works for me...

SQL> create table a1 ( x int, y int, z INT, w INT, v int );

Table created.

SQL> create table a2 ( x int, y int, z INT, w INT, v int );

Table created.

SQL>
SQL> create index a1_1 on a1 (x, z);

Index created.

SQL> create index a1_2 on a1 (x, y);

Index created.

SQL>
SQL>
SQL> create index a2_1 on a1 (x, w);

Index created.

SQL> create index a2_2 on a1 (x, v);

Index created.

SQL>
SQL> create index a2_3 on a1 (w, z);

Index created.

SQL> create index a2_4 on a1 (w, y);

Index created.

SQL>
SQL>
SQL>
SQL> select distinct i1.index_name
  2    from user_ind_columns i1
  3   where table_name='A1'
  4     and not exists (select *
  5                       from user_ind_columns i2
  6                      where i2.table_name = 'A2'
  7                        and i1.column_name = i2.column_name
  8                        and i1.column_position = i2.column_position
  9                    );

INDEX_NAME
------------------------------
A2_4
A1_1
A2_1
A1_2
A2_3
A2_2

Find index differences between two tables

Leontinus, March 06, 2018 - 9:29 am UTC

I searched index structures what are existing on the first table and not existing on the second.

DROP TABLE a1;
DROP TABLE a2;

create table a1 ( x int, y int, z INT, w INT, v int );
create table a2 ( x int, y int, z INT, w INT, v int );

create index a1_1 on a1 (x, z);
create index a1_2 on a1 (x, y);


create index a2_1 on a2 (x, w);
create index a2_2 on a2 (x, v);                                            

create index a2_3 on a2 (w, z);
create index a2_4 on a2 (w, y);

WITH 
 ic1 AS (
  SELECT index_name, 
         LISTAGG(COLUMN_name, '; ') WITHIN GROUP (ORDER BY column_position) index_list
   FROM user_ind_columns
  WHERE table_name='A1'
  GROUP BY index_name
  ORDER BY index_list
 )
,ic2 AS (
  SELECT index_name, 
         LISTAGG(COLUMN_name, '; ') WITHIN GROUP (ORDER BY column_position) index_list
   FROM user_ind_columns
  WHERE table_name='A2'
  GROUP BY index_name
  ORDER BY index_list
)
SELECT ic1.index_list AS "Missing INDEX structure" 
  FROM ic1,
       ic2
 WHERE ic1.index_list = ic2.index_list(+)
   AND ic2.index_list IS NULL;                                              

"Missing INDEX structure"
---------------------------
X; Z                     
X; Y

Chris Saxon
March 06, 2018 - 6:02 pm UTC

Ahh, I see what you mean now. Yes, good catch.

vive la difference

Racer I., March 07, 2018 - 10:45 am UTC

Hi,

...
SELECT ic1.index_name || '(' || ic1.index_list || ')' "Index in A1 but not A2",
ic2.index_name || '(' || ic2.index_list || ')' "Index in A2 but not A1"
FROM ic1
FULL JOIN ic2 ON (ic1.index_list = ic2.index_list)
WHERE ic1.index_list IS NULL
OR ic2.index_list IS NULL;

for both directions. But that is just the tip of the iceberg. What about uniqueness, compression, partitioning, storage, etc?

regards,
Chris Saxon
March 07, 2018 - 11:29 am UTC

What about uniqueness, compression, partitioning, storage, etc?

Indeed. It comes down to when the OP considers indexes to be "the same".
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.