Simple query hangs.... Any idea..?

[skdas]

I have the following query

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..?

[irehman]

How many rows are in the table, run explain plan.

[marist89]

What's the query plan?

[chrisrlong]

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.

Yeah, that's it... plan,

- Chris

[BV1963]

Chris,

Very well put!

[skdas]

1. Both the tables have around 1.5 (a little over 15 Lakhs of records.)

2. Explain plan is as follows.

1.2632 SELECT STATEMENT skd cost =2632
2.1 SORT AGGREGATE
3.1 HASH JOIN
4.1 INDEX FAST FULL SCAN I2 NON-UNIQUE
4.2 INDEX FAST FULL SCAN I1 NON-UNIQUE

3. These two tables on that particular two fields we need to use the where clause, becuae we need some statistics.

These two tables will not be be required after our work is done. So after taking statistics we will drop it.

4. Is it natural to hang, the query which is having varchar2(20) on which index is there and joind with where clause...?

If that is the case I will think of some other way, such as writing stored procedure etc..

[skdas]

Both the tables have around 1.5 Million records.

[badrinathn]

Have you analyzed the tables? Table statistics are very important in this case.

[badrinathn]

Try to use the hint: /*+ USE HASH (a b)*/

[pando]

Originally posted by badrinathn
Try to use the hint: /*+ USE HASH (a b)*/

er, dont you see it's already doing hash join....? it is USE_HASH anyways