Friday, July 1, 2011

Oracle's minus has a bug?

No, it doesn't.
But look at the following situation:
You have two tables that must be identical(must contain same data).
You shoot a minus between them and get some rows. Few, but they exists.

You choose one row from the result of minus, and run
select * from table1 where col=key1
minus
select * from table2 where col=key1
and you get one row.

you run

select * from table1 where col=key1
union all
select * from table2 where col=key1
and you get two rows.

you run 
select * from table1 where col=key1
union
select * from table2 where col=key1
and you get one row!
so, the rows are not different!

How can that be possible???
You run explain plan for every query from above and it does what you asked in query.
Think a bit, and if you can't find the answer, scroll down to see it.