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

    sql query

    874273
      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
          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
            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
              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
                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
                  No Use Chanchal...!!! Thanks for ur support...!!!
                  • 6. Re: sql query
                    Chanchal Wankhade
                    hi,

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


                      Regards
                      Ar
                      • 8. Re: sql query
                        Mr Lonely
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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