delete and truncate

[orajit]

Hello,

I have one concern , I have one table that contains huge data. I have created one procedure inside that procedure I am truncating that table . It takes very long time to execute that. When I saw the Dbms_profiler report I have observed that truncate statement took long time to execute around 20 minutes . Then I replace the truncate with delete . It executed within fraction of time with proper result. May I know why it happened . We all know that for better performance truncate works faster than delete.

Declare 
  v_state varchar2 (200);
Begin 
   Selec…..;

   Select …..;

   V_sate:= 'TRUNCATE TABLE XYZ;
    EXECUTE IMMEDIATE v_sate;
END:
/

Declare 
  v_state varchar2 (200);
Begin 
   Selec…..;

   Select …..;

Delete table xyz;

Commit;
END:

[amitpatel66]

what is the size of the table and does the DML operations performed frequently on the table?

[orajit]

Thanks for your reply ..that dml operation performed in every day. There are total 6000000 rows in table.

[madankarmukta]

Hi,

Try the truncate statement directly instead of making it as a dynamic query... i.e. call truncate statement the way you performed delete.

That may reduce the overhead.

Thanks!

[amitpatel66]

Truncate should be better compared to delete becuase as you say DML is performed very frequently on the table, you will need to rebuild the index very frequently in order to remove the deleted leafs from the index else the performance will be hampered.

Can you try truncate from SQL PLUS instead from PLSQL and check the performance?

[orajit]

Thanks . If I fired only truncate it works faster than delete. My Procedure contains many select statements and one truncate command (i used dynamic sql there ). I have ran DBMS_PROFILER and found that my truncate command which I have used in dynamic sql took more time to execute . Then I replaced the truncate with normal delete command. It executed in minimum time. Also thr are no such index on my table .

[orajit]

Please any updates on that ..thanks

[orajit]

Hi,

Try the truncate statement directly instead of making it as a dynamic query... i.e. call truncate statement the way you performed delete.

That may reduce the overhead.

Thanks!

Hi madankarmukta,

We canot call truncate statement directly in Plsql code. So I used dynamic sql for calling truncate statement ..thanks

[orajit]

any updates on this ... still have no idea? Please help me out ...thanks