Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Question Unanswered: Query Optimization Help

    Hi,

    Is this best way to right this query

    update bigtable BT set qty = qty +
    (select tm.tmpqty from tmptable tm where tm.col1 = BT.col1 and
    tm.col2 = BT.col2 ) where BT.col1 || BT.col2 in (select tm1.col1 || tm1.col2 from tmptable tm1)

    bigtable : has 2.5 mil records and growing indexed o col1 and col2
    Col1 Col2 Qty
    A A1 10
    A A2 11
    B A1 12
    B A2 13

    tmptable : may have 100 records
    Col1 Col2 Qty
    A A1 1
    A A2 2
    B A1 10

    Result expected
    bigtable :
    Col1 Col2 Qty
    A A1 10+1
    A A2 11+2
    B A1 12+10
    B A2 13

    I am worried that my query will be slow because of || condition in where clause and i have indexes only on individual columns in big table. To simplify query above i only used col1 and col2 actually i will have 5 columns to concat.

    Thanks in advance

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This would work.

    WHERE (BT.col1, BT.col2) IN (SELECT tm1.col1, tm1.col2 FROM tmptable tm1)

  3. #3
    Join Date
    Nov 2008
    Posts
    48

    Thanks tonkuma it worked

    Thanks it worked, One more question

    In above example I need to update qty if the col1 and col2 exists else insert
    i am planning to these 2 steps

    update bigtable BT set qty = qty +
    (select tm.tmpqty from tmptable tm where tm.col1 = BT.col1 and
    tm.col2 = BT.col2 ) where (BT.col1 , BT.col2) in (select tm1.col1,tm1.col2 from tmptable tm1)

    >> Will update any matches

    Insert into bigtable BT (BT.col1, BT.col2, BT.qty) (select tm.col1, tm.col2, tm.qty from tmptable tm where (tm.col1 , tm.col2) not in (select BT1.col1,BT1.col2 from bigtable BT1 )

    >> Will insert non matches

    Does this look good?

    Regards

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use a MERGE statement to combile the UPDATE statement and the INSERT statement.
    Last edited by tonkuma; 12-20-08 at 00:29.

  5. #5
    Join Date
    Nov 2008
    Posts
    48
    Thanks again tonkuma

Posting Permissions

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