How to compare two sets of rows in Oracle?

Question

So, the problem is that i have two results (eg. number):

RES1: 10 11

RES2: 10 13

I need to compare those like if RES1 in RES2 and RES2 in RES1.

I would like to have result like:

RES3: 11 13

How do i do that?

I tried

RES1 MINUS RES2 UNION RES2 MINUS RES1

but this approach is very slow, becouse my table contains milions of rows...

score 1 · Accepted Answer · answered Jun 14 '19 at 11:20

Why not to use one of supplied packages. DBMS_COMPARISON The Package allows to compare and sync tables. It's only required that tables have an index.

1) create diff datasets

create table to_compare2 as (select OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, case when mod(object_id,18) = 0 then  CREATED +1 else CREATED end  CREATED  from all_objects where mod(object_id,6) = 0 );
CREATE table to_compare1 as (SELECT OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, case when mod(object_id,12) = 0 then  CREATED +1 else CREATED end  CREATED  FROM ALL_OBJECTS where mod(object_id,3) = 0 );

2) create indexes.

  CREATE UNIQUE INDEX to_compare1_idx  on  to_compare1(object_id);
  CREATE UNIQUE INDEX to_compare2_idx  on  to_compare2(object_id);

3) Prepare comparision context

BEGIN
  DBMS_COMPARISON.create_comparison (
    comparison_name    => 'MY_COMPARISION',
    schema_name        => user,
    object_name        => 'to_compare1',
    dblink_name        => NULL,
    remote_schema_name => null,
    remote_object_name => 'to_compare2');
END;
/

4) Execute comparison and check results.

DECLARE
  v_scan_info  DBMS_COMPARISON.comparison_type;
  v_result     BOOLEAN;
BEGIN
  v_result := DBMS_COMPARISON.compare (
                comparison_name => 'MY_COMPARISION',
                scan_info       => v_scan_info,
                perform_row_dif => TRUE
              );

  IF NOT v_result THEN
    DBMS_OUTPUT.put_line('Differences. scan_id=' || v_scan_info.scan_id);
  ELSE
    DBMS_OUTPUT.put_line('No differences.');
  END IF;
END;
/

4) Results

SELECT *
FROM   user_comparison_row_dif
WHERE  comparison_name = 'MY_COMPARISION';

if local_rowid is not null and remote_rowid is null -> record exit in table_1
if local_rowid is null and remote_rowid is not null -> record exit in table_2
if local_rowid is not null and remote_rowid is not null -> record exist in both tables but it has different values

hotfix · Answer 2 · 2019-06-14T08:49:58.787

0

solutin 1:

try UNION ALL instead of UNION.

why UNION ALL is better then UNION you can read here: What is the difference between UNION and UNION ALL?

solutin 2:

you can try to use full outer join

select coalesce(a.id,b.id)
from a
full outer join b on a.id = b.id
where a.id is null
or b.id is null

Example: http://www.sqlfiddle.com/#!4/88f81/3

edited Jun 14 '19 at 08:49

answered Jun 14 '19 at 08:30

hotfix

3,376
20
36

Well, i can try full outer join but i'll kill db (milions of rows). Am i right? – andrea_sottolamontagna Jun 14 '19 at 08:56
i don't know, you should try it out. it is just a way you can do it. There is no general solution that always will work. – hotfix Jun 14 '19 at 09:20

score 0 · Answer 3 · answered Jun 14 '19 at 08:59

Are those Values unique in RES1 or RES2? Then you could try counting:

SELECT col
FROM (
    SELECT col FROM RES1
    UNION ALL
    SELECT col FROM RES2
)
GROUP BY col
HAVING COUNT(1) = 1

If it is not unique, you'd have to add a distinct on both sides of the union, which makes it a lot slower

How to compare two sets of rows in Oracle?

3 Answers3