Re: SQL Optimizing!

In article <7uq0bo$26f$1_at_nnrp1.deja.com>,   Nandakumar <N.Kumar_at_rocketmail.com> wrote:
> Hi
>
> I have a pl/sql procedure that populates fact tables from a temporary
> table. But it takes about 40 minutes to populate 5 tables with 5000
> records from the temporary table. Some of the reasons that i could
think
> of for the procedure's taking this long,
> 1) procedure is using a cursor
> 2) manipulation of the records in temporary table
> 3) written to perform update in few records and insert in few others
> 4) update and insert are performed thru' a dynamic PL/SQL as the table
> names are decided in run time.
>
> If i want to optimize the SQL for better performance, i think of the
> hint APPEND in the insert statement. The oracle reference says it
should
> be used when a huge number of records are inserted. Could someone tell
> me what would be the huge number in this regard? And if the APPEND
hint
> is used, would it lead to more unused(wasted) disk space?
>
> I also remember if the indexes are enabled, the IMPORT will be very
> slow. Is it the same case when performing an INSERT? In other words,
> does indexing help a fast insert/update?
>
> Any information in this regard is appreciated.
> Thanks
> Nanda
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Hi.
 Are you using values or bind variables in your dynamic sql statements?  Use variables , perform PARSE only once per statement instead once  per execution.
 Commit once each 2000 - 3000 updates/insert.  APPEND hint causes inserts above high water mark and it's used for  10's thousands of inserts ( not in your case, I believe ).  Is it possible to post your PL/SQL source?

 HTH. Michael.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 22 1999 - 17:10:46 CDT