Whats wrong with my update
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Whats wrong with my update

  1. #1
    Join Date
    Jan 2003
    Location
    newton, ma, usa
    Posts
    15

    Whats wrong with my update

    I have correct indexes and this seems to be very simple query...but it takes soooo long to finish it...

    This is a select for this update

    SQLWKS> select nvl(sum(Exp_Project_Amount),0.0) , nvl(sum(Exp_Base_Amount),0.0) ,WorkCalcs_Expenses, WorkCalcs_Expenses_Base
    2> from mwebExpense , mwebworkcalcs
    3> where exp_work_id=mwebWorkCalcs.WorkCalcs_Work_ID and workcalcs_WORK_ENTITY_TYPE =4
    4> group by WorkCalcs_Expenses, WorkCalcs_Expenses_Base ;
    NVL(SUM(EX NVL(SUM(EX WORKCALCS_ WORKCALCS_
    ---------- ---------- ---------- ----------
    50 50 50 50
    4.2 4.2 4.2 4.2
    6.5 6.5 6.5 6.5
    13.6 13.6 13.6 13.6
    30.12 30.12 30.12 30.12
    843.49 615.21 843.49 615.21
    876.31 876.31 876.31 876.31
    899.44 899.44 899.44 899.44
    1096.06 1096.06 1096.06 1096.06
    1205.8 1205.8 1205.8 1205.8
    2443.3 2443.3 2443.3 2443.3
    13340.12 13340.12 13340.12 13340.12
    12 rows selected.
    Parse 0.01 (Elapsed) 0.00 (CPU)
    Execute/Fetch 0.08 (Elapsed) 0.02 (CPU)
    Total 0.09 0.02

    This is the update, it says updated 34458 rows , why ? Also it takes so long to do this..
    There is a index on exp_work_id, and WorkCalcs_Work_ID is primary key.

    SQLWKS> Update mwebWorkCalcs a
    2> set (a.WorkCalcs_Expenses, a.WorkCalcs_Expenses_Base) =
    3> (select nvl(sum(Exp_Project_Amount),0.0) , nvl(sum(Exp_Base_Amount),0.0)
    4> from mwebExpense
    5> where exp_work_id=a.WorkCalcs_Work_ID )
    6> where a.workcalcs_WORK_ENTITY_TYPE=4;
    34458 rows processed.
    Parse 0.00 (Elapsed) 0.00 (CPU)
    Execute/Fetch 180.40 (Elapsed) 0.00 (CPU)
    Total 180.40 0.00


    SQLWKS> select count(*) from mwebexpense;
    COUNT(*)
    ----------
    3802


    SQLWKS> select count(*) from mwebworkcalcs;
    COUNT(*)
    ----------
    46293


    Any help please ?

    Thanks
    Sonali

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    I'll bet
    Code:
    select count(*) from mwebworkcalcs where workcalcs_WORK_ENTITY_TYPE=4;
    is 34458.

    Why? You're only limiting on workcalcs_WORK_ENTITY_TYPE=4
    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."

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