Can u suggest a way to improve this query performance?
I have 2 tables T1,T2.
T1 has two numeric fields n1,n2 which together form the primary key.
T2 also has the fields n1,n2 which together reference the values of T1.
char(T1.n1)||char(T1.n2) not in char(T2.n1)||char(T2.n2)
My problem is the this query takes a long time to complete as T1 has
over 5000 rows.
PS: I was told that there is a feature in db2 by which i can say
select (n1,n2) from T1,T2
(T1.n1,T1.n2) not in (T2.n1,T2.n2).
Can someone throw some light on that?
That was an interesting event! When I started to write the reply there was no replys, but when I sumited it then the Marcus_A had already posted the message. So the answers are almost the same... Funny!
As Marcus_A said, your database version does not suppot the above SQLs.
There is one more tip (I don't know if this is supported with your version of db2):
select n1, n2 from T1
select n1, n2 from T2
If this works, consider the Marcus_A tip about indexes.
DB2 has to find the column B.n1 anyway because it is part of the WHERE clause. The value is not returned to the application program because it is part of the EXISTS clause and is not included in the columns retrieved to the program in the main SELECT.
I don't think DB2 will be bothered by such an inclusion of the column in the SELECT clause of the sub-select. But that is just my opinion.