Home » RDBMS Server » Performance Tuning » Indexes Difference
Indexes Difference [message #142761] Mon, 17 October 2005 10:30 Go to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi All

I have 2 databases TEST and QA, list of tables are same in both the databases,I want to compare the indexes in these 2 databases and send the output to excel file,
Let's say index1 is present in database TEST and not in QA..




Some good approach or script is requested.


Regards



Re: Indexes Difference [message #142766 is a reply to message #142761] Mon, 17 October 2005 11:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
spool the output.
this works, assuming you have the indexes named identically.
DB2 is the database link to talk to database 2
Create it in DB1.
use dba_indexes or user_indexes
prompt comparing indexes in DB1  and  DB2
select table_name,index_name from dba_indexes
where owner='SCOTT'
minus
select table_name,index_name from dba_indexes@DB2
where owner='SCOTT';
prompt comparing indexes in DB2  and  DB1
select table_name,index_name from dba_indexes@DB2
where owner='SCOTT'
minus
select table_name,index_name from dba_indexes
where owner='SCOTT';
Re: Indexes Difference [message #142768 is a reply to message #142766] Mon, 17 October 2005 11:28 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi Mahesh

Thanks for your response.

You want me to create a link between Test and QA databases to run your code,Thing is Sir i don't have permission to create a link, another thing is as you said assuming indexes names should be same, Well that's what i want to compare between databases is which indexes are there in TEST and Not in QA and vice versa.. Hope i am able clear the question..

Thanks
Re: Indexes Difference [message #142772 is a reply to message #142768] Mon, 17 October 2005 11:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Within oracle, the only way to talk between two databases is to use DBlink ( unless you want to check manually, taking a printout, read line by line, strikeout whatever is matching).
else.
Then spool the output and do a diff in OS level ( or whatever tool you have in your OS). Like this
oracle@mutation#cat file1
IndexName
oracle@mutation#cat file2
index_name
oracle@mutation#diff file1 file2
1c1
< IndexName
---
> index_name

>>another thing is as you said assuming indexes name
I should have been clear.
What i meant is , it depends on your database naming conventions.
assuming two schema's SCOTT and TEST as different databases and you want to compare their indexes.
Here , both schema has same table and index on the same column.
But the index name will be different.

scott@9i > create table foo (id number primary key);

Table created.

scott@9i > select index_name from user_indexes;

INDEX_NAME
------------------------------
SYS_C001575

scott@9i > !sqlplus -s test/test
Enter value for gname: test
test >  create table foo (id number primary key);

Table created.

test > select index_name from user_indexes;

INDEX_NAME
------------------------------
SYS_C001577


If you have created the indexes like in this case, then the above sql will generate correct list.
scott@9i > create table foo (id number);

Table created.

scott@9i > alter table foo add constraint pk_1 primary key (id);

Table altered.

scott@9i > select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_1


Re: Indexes Difference [message #143177 is a reply to message #142772] Wed, 19 October 2005 07:05 Go to previous message
ziggy
Messages: 27
Registered: July 2005
Location: Moscow, Russia
Junior Member

As far as I know there are a lot of high level tools such as ErWin which would compare two database's physical model.

If such way is not enough easy for you then try such algorithm as follows.

1. First database and etalon schema
create table my_indexes
as
select * 
  from user_indexes;  


Then export table my_indexes from etalon schema.

2. Second database and compared schema

Import given table my_indexes into compared schema.

Then use queries like Mahesh offers:
prompt Compare current schema with etalon
  
select table_name, index_name 
  from user_indexes
minus
select table_name, index_name 
 from my_indexes;

prompt And vice versa as you'll want
 
select table_name, index_name 
  from user_indexes
minus
select table_name, index_name 
 from my_indexes;



Regards,
Ziggy.
Previous Topic: Interpreting TKPROF for Complex Inserts/Updates
Next Topic: Query Based on Two Views
Goto Forum:
  


Current Time: Thu Mar 28 06:07:11 CDT 2024