This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Oct 8, 2012 1:48 AM by 874273 RSS

sql query

874273 Newbie
Currently Being Moderated
Hi Masters,

I have an update statement with select query. But it is taking 45 mins. for executiion. Can we re-write the select query for better performance ? Please help me.
UPDATE stg_spt_supplier_direct  a
  SET vendor_name_cleansed =
  (SELECT DISTINCT b.vendor_name_cleansed
  FROM dim_spt_supplier  b
  WHERE b.all_vendor_list_vendor_name = a.all_vendor_list_vendor_name
  AND (b.total_amount, b.dim_spt_supplier_sk)  IN
  (SELECT MAX(c.total_amount), MAX(c.dim_spt_supplier_sk)
  FROM dim_spt_supplier  c
  WHERE c.all_vendor_list_vendor_name = b.all_vendor_list_vendor_name))
  WHERE vendor_name_cleansed = '';
 
I have re-write the query for the above update statement. But How to add this query to my update statement.
Please advise me..!!
SELECT distinct b.vendor_name_cleansed
FROM stg_spt_supplier_direct a,dim_spt_supplier  b
WHERE b.all_vendor_list_vendor_name = a.all_vendor_list_vendor_name 
AND (b.total_amount, b.dim_spt_supplier_sk)  IN
(SELECT MAX(c.total_amount), MAX(c.dim_spt_supplier_sk)
FROM dim_spt_supplier c
WHERE c.all_vendor_list_vendor_name = b.all_vendor_list_vendor_name) ;
Thanks in Adv.
Ar

Edited by: 871270 on Oct 9, 2012 12:53 AM
  • 1. Re: sql query
    sb92075 Guru
    Currently Being Moderated
    871270 wrote:
    Hi Masters,

    I have an update statement with select query. But it is taking 45 mins. for executiion. Can we re-write the select query for better performance ? Please help me.
    UPDATE stg_spt_supplier_direct  a
    SET vendor_name_cleansed =
    (SELECT DISTINCT b.vendor_name_cleansed
    FROM dim_spt_supplier  b
    WHERE b.all_vendor_list_vendor_name = a.all_vendor_list_vendor_name
    AND (b.total_amount, b.dim_spt_supplier_sk)  IN
    (SELECT MAX(c.total_amount), MAX(c.dim_spt_supplier_sk)
    FROM dim_spt_supplier  c
    WHERE c.all_vendor_list_vendor_name = b.all_vendor_list_vendor_name))
    WHERE vendor_name_cleansed = '';
    Thanks in Adv.
    Ar
    HOW To Make TUNING request
    SQL and PL/SQL FAQ
  • 2. Re: sql query
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    To improve the performance of the query you can apply index on the column which are in the where clause of the query.
  • 3. Re: sql query
    874273 Newbie
    Currently Being Moderated
    Hi

    Both tables having indexes on where clause columns. Is there any alternative way to write a select query.
    Please advise.

    Regards
    Ar
  • 4. Re: sql query
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    hi try this query
    UPDATE stg_spt_supplier_direct  a
      SET vendor_name_cleansed =
      (SELECT b.vendor_name_cleansed
      FROM dim_spt_supplier  b
      WHERE b.all_vendor_list_vendor_name = a.all_vendor_list_vendor_name
      AND (b.total_amount, b.dim_spt_supplier_sk)  IN
      (SELECT MAX(c.total_amount), MAX(c.dim_spt_supplier_sk)
      FROM dim_spt_supplier  c
      WHERE c.all_vendor_list_vendor_name = b.all_vendor_list_vendor_name))
      WHERE vendor_name_cleansed = ' ';
  • 5. Re: sql query
    874273 Newbie
    Currently Being Moderated
    No Use Chanchal...!!! Thanks for ur support...!!!
  • 6. Re: sql query
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    hi,

    alternate is you can use instate of
    WHERE vendor_name_cleansed = ' '
    try
    WHERE vendor_name_cleansed is null;
  • 7. Re: sql query
    874273 Newbie
    Currently Being Moderated
    yah...Tried like that also...But No use...!!! Dude


    Regards
    Ar
  • 8. Re: sql query
    Mr Lonely Newbie
    Currently Being Moderated
    Hi ,

    How many records are there in those table?

    Are you sure the select query is using the index? Can you check the execution plan? Also if possible try to avoid 'DISTINCT' clause.
  • 9. Re: sql query
    jeneesh Guru
    Currently Being Moderated
    871270 wrote:
    yah...Tried like that also...But No use...!!! Dude


    Regards
    Ar
    Did you see first reply to your question?

    {message:id=9360003}
  • 10. Re: sql query
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    hope this will help
    UPDATE stg_spt_supplier_direct  a
      SET vendor_name_cleansed =
      (SELECT b.vendor_name_cleansed
      FROM dim_spt_supplier  b,dim_spt_supplier  c
    where 
          b.all_vendor_list_vendor_name = a.all_vendor_list_vendor_name 
    and     c.all_vendor_list_vendor_name = b.all_vendor_list_vendor_name
    and      (b.total_amount,b.dim_spt_supplier_sk)  IN
      (SELECT MAX(c.total_amount), MAX(c.dim_spt_supplier_sk)  from dim_spt_supplier  c))
  • 11. Re: sql query
    Mr Lonely Newbie
    Currently Being Moderated
    Are you sure this will give the same output?

    SELECT MAX(c.total_amount), MAX(c.dim_spt_supplier_sk) from dim_spt_supplier c -- This will select the max value from the whole table.


    Anyway if you really want us to help you with tuning please provide the

    1. Explain plan
    2. Sql Trace.

    And don't ignore post of users like jeneesh and sb92075.
  • 12. Re: sql query
    874273 Newbie
    Currently Being Moderated
    Hi
    select count(*) from stg_spt_supplier_direct ; -- 21462 rows...taking proper index in the execution plan
    
    select count(*) from dim_spt_supplier ;  --788772 rows...taking  .. dim_spt_supplier_sk  column index in the  execution plan.
    Hope this info.is Ok..!!!

    Regards
    Ar
  • 13. Re: sql query
    Mr Lonely Newbie
    Currently Being Moderated
    With this much of count it should not take 45 minutes to execute the query.

    Please paste the execution plan here.

    explain plan for <your query>;
    select * from table(dbms_xplan.display);
  • 14. Re: sql query
    874273 Newbie
    Currently Being Moderated
    Hi
    SELECT MAX(c.total_amount), MAX(c.dim_spt_supplier_sk)   FROM dim_spt_supplier  c; 
    The above query giving the output..

    Total Amt Dim_spt_supplier_sk

    1619319000 806957
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points