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 -> Re: sql vs. pl/sql: speed issue

Re: sql vs. pl/sql: speed issue

From: <michael_bialik_at_my-deja.com>
Date: Sun, 13 Feb 2000 21:14:17 GMT
Message-ID: <8876r6$abh$1@nnrp1.deja.com>


Hi.

 In my experience its almost always better to use SQL instead of PL/SQL  ( when using PL/SQL you have to pay "penalty" of context switches from   SQL to PL/SQL and back ).

 Post yout UPDATE statement and it's EXPLAIN. It may be possible to  avoid FULL table scan.

 HTH. Michael.

In article <884g7d$i4k$1_at_nnrp1.deja.com>,   cosmin_ioan_at_msn.com wrote:
> hello all,
>
> I am trying to do a batch update (on a single table) on perhaps 5-10%
> data out of a 30 mill. rec. table and I am comparing two methods:
>
> 1) doing a straight update sql statement (the 'where' clause is a
bit
> complex) therefore a full table scan is performed; entire operation
> takes less then 5 minutes;
> 2) taking the same operation under a 'cursor c ... select
> rowid ....for update of ...' pl/sql statement and then updating the
> records in the body of the procedure. This operation took around 7
> minutes.
>
> a) Does that mean that one should, if at all possible, use pl/sql only
> when absolutely necessary!?
> b) Is there any way to do a large sql update and commit every x
records
> (and not use a huge rollback segment) or is that possible only in
> pl/sql!?
>
> thanks much,
>
> Cosmin
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Feb 13 2000 - 15:14:17 CST

Original text of this message

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