July 4, 2012 at 2:49 am
Hi All,
There is a requirement where we need to do give a list of rows missing from one table form the other table.
Where as i tried with couple of options mentioned below, but all of them are taking around 20 to 30 mins....
The Data in the both the tables are around 2 to 3 lack's.
Please need your help to provide the best way to optimize this Data Retrieval...!
Q 1:
Select t1.c1,t1.c2
from t1 --ACT
full outer join t2 --IC
on t1.c1 = t2.c1
and t1.c2 = t2.c2
where (t2.c1 is null and t2.c2 is null)
Q 2:
SELECT MIN(TableName) as TableName, C1, C2
FROM
(
SELECT 'Table T1' as TableName, T1.C1, T1.C2
FROM T1
UNION ALL
SELECT 'Table T2' as TableName, T2.C1, T2.C2
FROM T2
) tmp
GROUP BY C1,C2
HAVING COUNT(*) = 1
ORDER BY C1
Q 3:
Select * from T1
EXCEPT
Select * from t2
July 4, 2012 at 3:10 am
Ifyou want to retrieve the data from table 1 which is not there in table 2 and similarly data which is there in table2 but not in table 1. Then except wont work as you have to write two excpet with union all.
If you want the data which is there in table1 but not in table 2 then you do not need a full outer join?
Related to perf.. Post the table's ddl,index ddl as well as if possible the plan generated (graphical,save it as .sqlplan) by your approaches say full outer join one.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 4, 2012 at 4:56 am
To get data from T1 that's not in T2 try:-
Select *
From T1
Left Join T2
ON T1.C1 = T2.C1
AND T1.C2 = T2.C2
Where T2.C1 IS NULL
to go the other way just flip the query around:-
Select *
From T2
Left Join T1
ON T2.C1 = T1.C1
AND T2.C2 = T1.C2
Where T1.C1 IS NULL
(or you could use a right join instead but I prefer to structure things as lefts - I just find it easier)
To get records that are in either one but not the other simply union the results of these two queries.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply