Tuesday, March 2, 2010

ALL DIFFERENCES FROM TWO TABLE

CREATE TABLE T11
(
N1 NUMBER NULL,
N2 NUMBER NULL,
V1 VARCHAR2(215 BYTE) NULL
)
/


Insert into T11 (N1, N2, V1)
Values (1, 1, 'HALIM');

Insert into T11 (N1, N2, V1)
Values (0, 0, 'x');

Insert into T11 (N1, N2, V1)
Values (0, 0, 'x ');

COMMIT;



CREATE TABLE T22
(
N1 NUMBER NULL,
N2 NUMBER NULL,
V1 VARCHAR2(215 BYTE) NULL
)
/


Insert into T22 (N1, N2, V1)
Values (1, 1, 'HALIM');

Insert into T22 (N1, N2, V1)
Values (2, 2, 'x');

Insert into T22 (N1, N2, V1)
Values (3, 3, 'rubel');


COMMIT;


---ALL DIFFERENCES FROM TWO TABLE

Select * from
(
select 'HTEST.T11' "Row Source", a.* from
(
select /*+ FULL(Tbl1) PARALLEL(Tbl1, 4) */
N1, N2, V1
from HTEST.T11 Tbl1
minus
select /*+ FULL(Tbl2) PARALLEL(Tbl2, 4) */
N1, N2, V1
from HTEST.T22 Tbl2
) A
union all
select 'HTEST.T2', b.* from
(
select /*+ FULL(Tbl2) PARALLEL(Tbl2, 4) */
N1, N2, V1
from HTEST.T22 Tbl2
minus
select /*+ FULL(Tbl1) PARALLEL(Tbl1, 4) */
N1, N2, V1
from HTEST.T11 Tbl1
) B
)
Order by 1

No comments: