Re: Procedure Performance Suggestion

In article <a7fke4$be6$1_at_reader1.panix.com>, "R says...
>
>I have a procedure that builds the SELECT and uses Native Dyn. SQL (execute
>immediate) to populate a table for report....
>1) if the table NOT exist, do CTAS...(nologging)
>2) if it exists, TRUNCATE it, and do INSERT...SELECT
>(table size about < 1000 rows)
>
>To get any performance gain....should I just DROP the table
>and do CTAS as opposed to #2 ??
>
>Thanks !
>Rob
>
>
>

create the table ONCE and reuse it over and over. My suggestion -- use a GLOBAL TEMPORARY table with on commit delete rows and

1. don't create it in the procedure, create it upon INSTALL of your procedure
2. don't truncate it -- just commit if you need, the data disappears

avoid DDL in a thing that is run regularly.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp