Simple query hangs.... Any idea..?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Simple query hangs.... Any idea..?

  1. #1
    Join Date
    Jul 2002
    Posts
    205

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

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

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    How many rows are in the table, run explain plan.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What's the query plan?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Nov 2001
    Posts
    335
    Chris,

    Very well put!
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  6. #6
    Join Date
    Jul 2002
    Posts
    205
    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..

  7. #7
    Join Date
    Jul 2002
    Posts
    205
    Both the tables have around 1.5 Million records.

  8. #8
    Join Date
    Jan 2001
    Posts
    642
    Have you analyzed the tables? Table statistics are very important in this case.
    There is always a better way to do the things.

  9. #9
    Join Date
    Jan 2001
    Posts
    642
    Try to use the hint: /*+ USE HASH (a b)*/
    There is always a better way to do the things.

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width