Re: Deadly sins againts database performance/scalability

"Holger Baer" <holger.baer_at_science-computing.de> a écrit dans le message news: bq79s1$o06$1_at_news.BelWue.DE...
>
> > Hello,
> >
> > Why
> > (3)
> > LOOP
> > EXECUTE IMMEDIATE some_sql;
> > END LOOP;
> > should be avoided ??
> >
> > OK, it's Dynamical SQL but it could be useful to write scalable batch
> > scripts.
> > Tell me if i'm wrong.
> >
> > Cheers,
> > Alkos
> >
>
> If you want it truely scalable, then you would do something like this
> (Pseudo code following, so don't trash me for inaccuracies or not
> existing function/procedures - it's just supposed to give the
> general idea)
>
> dbms_sql.open_cursor
> dbms_sql.parse
> LOOP
> dbms_sql.bind
> dbms_sql.execute
> END LOOP
> dbms_sql.close_cursor
>
> This way, you avoid soft parses (and why you should avoid them is
> something that you will know if you hang around Tom Kytes website
> for a few days at http://asktom.oracle.com )
>
> Personally, I use execute immediate only for administrative tasks.
> But then, I'm most of the time the DBA ;-)
>
> Cheers,
>
> Holger
>

Hi Holger,

Ok, the advice should be :
"don't use execute immediate within loops to execute DML queries"* what i understood first. I was wondering whether there was any hindrance using exec immdt for DDL in some cases. According to me, there is not example :
open a cursor to list all tables owned by the current user. loop throuh the cursor

    exec immdt to grant another user with s,i,u,d on this table end loop
(this is really pseudo-code,isn't it ? ;-)

Alkos Received on Fri Nov 28 2003 - 05:07:02 CST