Often you
need to know if the new SQL select statement is equivalent to old one. Let’s
say You rewrite select and it’s much faster, but is it equivalent to old one?
You need to compare result sets of old and new select statements. You can
compare result sets with different combinations of bind variables. If you don’t
have any bind variables, create them by replacing constant values. If results
sets are equal in all test scenarios you are good to go. Probability that new
SQL select statement is equivalent to old one is greater, if you test selects
with more different combinations of bind variables and if result sets are
bigger.
Select for
comparing old select with new select is:
WITH old_select as (select …)
,new_select as (select …)
select count(*), ‘diffs’ description from
(
(
select * from old_select
minus
select * from new_select
)
union
(
select * from new_select
minus
select * from old_select
)
)
union all
select count(*),
'new_select rows' description from new_select
union all
select count(*),
'old_select rows' description from old_select
;
Following
result set means that result sets are equal and that there are 130 rows in each result
set.
0 ‘diffs’
130 'new_select
rows'
130 'old_select
rows'
