Re: SQL Performance Question

Thanks Dan
for your comments...

Postscript: I tried tests both ways and in fact you were correct...even though a temp table was needed, the queries written without cursors and pl/sql tables were significantly faster (6 min 50 seconds down to 4 min 30 seconds).

I tried modifying the insert statements to use the APPEND hint, but got an oracle error:

  ORA-12838: cannot read/modify an object after modifying it in parallel   Cause: Within the same transaction, an attempt was made to add read   or modification statements on a table after it had been modified in   parallel or with direct load. This is not permitted.

I accomplished in 13 INSERT statements, about 40,000 inserts whereas in the pl/sql table model, with opening a cursor loop with other INSERTS inside the loop, I could have been performing hundreds or thousands of individual INSERTs. (I considered the temp table to be messy, but I guess in some circumstances, a purely "work" table is acceptable).

Thanks
Robert Proffitt

In article <19991019214129.02699.00000141_at_ng-cf1.aol.com>,   danhw_at_aol.com (DanHW) wrote:
> It has been my experience that if you can do a process completely in
a SQL
> statement, that it is usually fast to do it that way rather than try
to
> replicate the process in PL/SQL, but I imagine that it depends on the
number of
> rows that are duplicated (ie, are there as many rows in the
conversion tables
> as in MyTable?) If one is a lot smaller, it might be worth doing
tricks with
> PL/SQL tables
>
> What will probably increase the performance is to use the direct load
option.
> It is a hint in the insert statement...
>
> insert /*+append */ into table...
>
> Look in the docs for details on what it does.
>
> Dan Hekimian-Williams
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 20 1999 - 23:16:04 CEST