petek, 07. december 2018

How to check equality of two SQL select result sets in Oracle


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’ desc 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' desc from new_select   
  union all
  select count(*), 'old_select rows' desc 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'