5

we have customer who faced an issue with some inconsistent data and we gave them a query to fix it.

Now before running the query, the customers asks me for a query that will give the temp space required for running that query. This is actually important as this query could be affecting a lot of entries in the table.

Here is the query that I sent them to fix their issue:

declare
  cursor cur is select distinct SEQID from D_LEAD where SEQID IN( SELECT SEQID FROM D_LEAD WHERE CURR_FLAG = 1 GROUP BY
  SEQID HAVING COUNT(SEQID) >1);

  seq NUMBER; 

begin

  open cur;
  loop
    fetch cur into seq;
    update D_LEAD set CURR_FLAG = 0 where LEAD_ID IN (SELECT LEAD_ID FROM D_LEAD WHERE ((LEAD_ID != (SELECT MAX(LEAD_ID) FROM D_LEAD WHERE SEQID=seq)) AND SEQID=seq));
    exit when cur%NOTFOUND;
  end loop;
  close cur;
commit;
end;

Thanks for your help!

MozenRath
  • 9,652
  • 13
  • 61
  • 104

1 Answers1

3

Oracle's EXPLAIN PLAN might give you some idea of query costs.

david a.
  • 5,283
  • 22
  • 24
  • i can understand that but i need temp space specifically – MozenRath Feb 03 '12 at 11:47
  • any idea on how to get the approximate or exact temp space? – MozenRath Feb 04 '12 at 11:46
  • 2
    The explain plan includes an estimate for the amount of temporary space required. Perhaps the tool you're using to look at the explain plan doesn't display that column by default? But you can definitely see it like this: `explain plan for [your query];` and then `select * from table(dbms_xplan.display);`. Look for the TempSpc column. Note that temporary space does not necessarily mean temporary tablespace; if all of the sorting or hashing can be done in memory then no temporary tablespace is required. Unfortunately, predicting memory usage is extremely complicated. – Jon Heller Feb 05 '12 at 04:38
  • ok! i dint see that column but will try what you just told! Thanks – MozenRath Feb 05 '12 at 06:35