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.