Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SQL Optimizing!

SQL Optimizing!

From: Nandakumar <N.Kumar_at_rocketmail.com>
Date: Fri, 22 Oct 1999 15:37:28 GMT
Message-ID: <7uq0bo$26f$1@nnrp1.deja.com>


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. Received on Fri Oct 22 1999 - 10:37:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US