Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    29

    Unanswered: index to run query fast

    Hello

    i am running a query which is taking forever here is the query

    UPDATE FEALINKC_test f
    SET (cvgcode,cvgvalid,state2,company,product)=(SELECT MIN(v.cvgcode),'A',v.STATE,v.COMP,v.PROD FROM VIPCVG v
    WHERE f.polnum=v.polnum AND f.polren=v.polren AND
    f.polseq=v.polseq AND f.vehnum=v.vehnum AND
    f.lincvg=v.linecvg
    --and f.polnum=5019150
    group by v.STATE,v.COMP,v.PROD )
    WHERE (polnum IS NOT NULL) AND (cvgcode IS NULL);

    -----------------------------------------------------------------------
    -----------------------------------------------------------------------

    the above query is taking forver to complete ,i hvae index on

    Vipcvg table for these columns(POLNUM, POLREN, POLSEQ, VEHNUM, CVGCODE)

    and FEALINKC_test table for these columns (CLAIM#, LINCVG, FEAT#)


    and those fields which have index is Primary and secondary keys



    what else i can do to run the query fast?


    Thanks in advance
    Hasya

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >WHERE ( polnum IS NOT NULL )
    > AND ( cvgcode IS NULL );

    above forces Full Table Scan; since NULL values are not indexed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    >WHERE ( polnum IS NOT NULL )
    > AND ( cvgcode IS NULL );

    above forces Full Table Scan; since NULL values are not indexed.
    Not true. Only tuples where all columns are NULL are not indexed, but in a multi-column index null values are indexed if at least one of the columns is not null

    the above query is taking forver to complete
    So what does the execution plan say which part takes so long?

    Without the execution plan we cannot really help you, so please post it.

    If you post the plan (or SQL statements) please remember to format it using [code] tags.
    Details on how to use it are here: http://www.dbforums.com/misc.php?do=bbcode

  4. #4
    Join Date
    Jan 2010
    Posts
    29
    Hello shammat,

    Here is the explain plan:

    I have attached the screen shot:








    Thanks
    Hasya
    Attached Thumbnails Attached Thumbnails explian_plan_fealinkc.jpg  

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Geez. What's so hard posting plain text plans? Why aren't there any row estimates in the plan?

    But the plan is not really that bad. It shouldn't take "forever". How long is "forever"?
    Are there any locks involved? Some other session locking that table for some reason?

    How many rows are updated in the FEALINKC_test table?
    How many rows are in that table? What about the other table? How many rows are selected by the sub-selected? How many rows are in that table?

    You might want to rewrite the update into a MERGE statement, something like this:

    Code:
    MERGE INTO fealinkc_test f
    USING
    (
        SELECT MIN(v.cvgcode) as cvgcode,
               'A' as cvgvalid,
               v.STATE,
               v.COMP,
               v.PROD, 
               f.rowid as fid
        FROM VIPCVG v
          JOIN FEALINKC_test f 
            on f.polnum=v.polnum 
           AND f.polren=v.polren 
           AND f.polseq=v.polseq 
           AND f.vehnum=v.vehnum 
           AND f.lincvg=v.linecvg
        WHERE f.polnum IS NOT NULL
          AND cvgcode IS NULL
        GROUP BY v.STATE, v.COMP, v.PROD
    ) t ON (t.fid = f.rowid)
    WHEN MATCHED THEN UPDATE
      SET cvgcode = t.cvgcode,
          cvgvalid = t.cvgvalid,
          state2 = t.state,
          company = t.comp,
          product = t.prod
    This might be faster than the correlated sub-query in your update. But you will need to verify that it does the correct thing. I can't test this obviously.
    Last edited by shammat; 06-13-12 at 17:45.

Posting Permissions

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