Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    80

    Unanswered: Query Hangs in between

    Hi,

    the below query hangs after retreiving around 90% of the rows. Meaning, when I run the query in winsql then after returning the 90% records it hangs. I tried writing the output to a file as well. still the same results. I tried with the explain sql and I got below results. can some one help me understand what is going wrong?

    1 'Table Access Full' Table1_LEFT' 'Table' 287.39 ''
    2 'Table Queue' '' '' 296.65 ''
    3 'Table Access Full' 'Table2_RIGHT' 'DP' 52247.54 ''
    4 'Group By' '' '' 52689.96 ''
    5 'Table Queue' '' '' 54861.43 ''
    6 'Hash Join' '' '' 339664.50 ''
    7 'Table Queue' '' '' 339681.81 ''
    8 'Data Operation Complete' '' '' 339681.81 ''

  2. #2
    Join Date
    May 2009
    Posts
    258
    I think you forgot to post the query.

  3. #3
    Join Date
    Jan 2005
    Posts
    80
    here is the query.

    Code:
    SELECT 
    A.COLUMN1
    ....
    .
    .
    .
    .
    .
    A.COLUMN10
    B.COLUMN2
    FROM 
    SCHEMA.TABLE1 A 
    LEFT JOIN
    
    (SELECT MIN(COL_ID) as COL_ID_1,COL_DT as COL_DT_1,LTRIM(RTRIM(COL_A_ID)) AS COL_A_ID_1,
    COL_AMT AS COL_AMT_1
        FROM SCHEMA.TABLE2 WHERE COL_DT = '9999-12-31' GROUP BY COL_ID,COL_DT, COL_A_ID, COL_AMT) F
    ON
    A.COL_DT=F.COL_DT_1 and LTRIM(RTRIM(A.COL_T_A_ID))=F.COL_A_ID_1 and -1*DEC(A.COL_AMT,18,2)=F.COL_AMT_1
    
    with ur

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Do you really want every row from the LEFT table (table1)? Robert Catterall just posted a blog entry on this very topic today, I suggest you look it up. Also, keep in mind that some of your predicates are not indexable and require a bit more work out of the data due to the functions you are performing during the comparison.
    Dave Nance

  5. #5
    Join Date
    Jun 2009
    Posts
    3
    Quote Originally Posted by ahmedwaseem2000
    here is the query.

    Code:
    SELECT 
    A.COLUMN1
    ....
    .
    .
    .
    .
    .
    A.COLUMN10
    B.COLUMN2
    FROM 
    SCHEMA.TABLE1 A 
    LEFT JOIN
    
    (SELECT MIN(COL_ID) as COL_ID_1,COL_DT as COL_DT_1,LTRIM(RTRIM(COL_A_ID)) AS COL_A_ID_1,
    COL_AMT AS COL_AMT_1
        FROM SCHEMA.TABLE2 WHERE COL_DT = '9999-12-31' GROUP BY COL_ID,COL_DT, COL_A_ID, COL_AMT) F
    ON
    A.COL_DT=F.COL_DT_1 and LTRIM(RTRIM(A.COL_T_A_ID))=F.COL_A_ID_1 and -1*DEC(A.COL_AMT,18,2)=F.COL_AMT_1
    
    with ur
    In this small exampe you give I would try
    Code:
    WITH TMP AS
    (
    SELECT 
      MIN(COL_ID) as COL_ID_1,
      COL_DT as COL_DT_1,
      LTRIM(RTRIM(COL_A_ID)) AS COL_A_ID_1,
      COL_AMT AS COL_AMT_1
    FROM SCHEMA.TABLE2 
    WHERE COL_DT = '9999-12-31' GROUP BY COL_ID,COL_DT, COL_A_ID, COL_AMT) F	
    )
    SELECT 
    A.COLUMN1
    ....
    .
    .
    .
    .
    .
    A.COLUMN10
    B.COLUMN2
    FROM 
    SCHEMA.TABLE1 A 
    LEFT JOIN TMP F ON ( A.COL_DT=F.COL_DT_1 and LTRIM(RTRIM(A.COL_T_A_ID))=F.COL_A_ID_1 and -1*DEC(A.COL_AMT,18,2)=F.COL_AMT_1)
    with ur

Posting Permissions

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