How to compare values between two different tables?
I have two tables as shown below:
Table 'A' sql_id OPERATION OPTIONS 63kf3an2j0pk SELECT STATEMENT 63kf3an2j0pkc TABLE ACCESS FULL Table 'B' sql_id OPERATION OPTIONS 63kf3an2j0pk SELECT STATEMENT 63kf3an2j0pkc INDEX UNIQUE SCAN IDX_THU
I need to compare the values in Table 'A' and Table 'B' for the given sql_id
. My question is how to find the changed values.
In this case in Table 'A' the second line is TABLE ACCESS FULL
. Table 'B' the the second line is INDEX UNIQUE SCAN IDX_THU
. So here the value has changed. How can I find that using query? Something like when matched then "matched" when not changed "value changed".
I'm looking for output something like below:
sql_id comaparion outcome dmqb1hzrf5uxh No change in the plan 74anujtt8zw4h plan changed
Can I do something like this?
select sql_id,case when ( select sql_id,OPERATION,options from a where sql_id='dmqb1hzrf5uxh'
minus
select sql_id,OPERATION,options from b where sql_id='dmqb1hzrf5uxh') is null then 'matched' else 'changed' end as status;
I am comparing the explain plans before and after patch. The table 'A' is before patch result of the sqlid
and table 'B' is after patch result. Now I want to compare the plans in both the tables. I want to compare the plan by using a query.
Best Answer
Assuming that
sql_id
has a unique or primary key constraint, you can do this withFULL JOIN
(orLEFT JOIN
if there are no new "inserts" in table B, or justJOIN
if there are no inserts and no deletions):