1. select count(*)
from T1 a , T2 b
where a.field1 = b.field2;
2. field1 is varchar2(20) and field2 is varchar2(20). There is index on field1 for the table T1 and field2 on T2.
3. The query specified in (1) hangs in Oracle.
4. The table T1 and T2 are new and I received the data from other system.
5. One SQL Server DBA he transferred these two tables from Oracle Database to SQL Sever. He runs the same query at SQL Server and get the error within 2 seconds as follows. Arithmetic overflow error converting expression to data type int.
My question is
a. Why Oracle doesnot come out with any error message..?
b. Why it hangs..?
c. Is there any way to find on which record it is hanging, or which record is having problem in data at Oracle side..?
As for the error - there are no ints, so why would there possibly be an error? Obviously, the person that converted it into SQLServer incorrectly defined one of the 2 fields as an integer. That must have worked because in one of the 2 tables, every value *was* an integer. However, much like Oracle, SQLServer probably then saw a WHERE clause: WHERE string = int and attempted to convert the string to an integer. At some point, it hit a non-number in that field and died.
Long story short - not your problem... his (hers?).
However, brings up another good point... why in the world do you have a 20-character string key???
But as everyone else said - show us the money! Uh, I mean PLAN.