OraFAQ Forum: Performance Tuning » Indexes Difference

Home » RDBMS Server » Performance Tuning » Indexes Difference

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Indexes Difference [message #142761]

Mon, 17 October 2005 10:30

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

Report message to a moderator

Re: Indexes Difference [message #142766 is a reply to message #142761]

Mon, 17 October 2005 11:04

Mahesh Rajendran
Messages: 10708
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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Indexes Difference [message #142768 is a reply to message #142766]

Mon, 17 October 2005 11:28

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

Report message to a moderator

Re: Indexes Difference [message #142772 is a reply to message #142768]

Mon, 17 October 2005 11:48

Mahesh Rajendran
Messages: 10708
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

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Indexes Difference [message #143177 is a reply to message #142772]

Wed, 19 October 2005 07:05

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

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Previous Topic:	Interpreting TKPROF for Complex Inserts/Updates
Next Topic:	Query Based on Two Views

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 08:49:21 CDT 2025