Oracle PLSQL 指令效能分析

以下是源自網路的一段建議，分享給大家，原本是有人問用Cursor寫比較方便，像底下table dump 是不存在

create table dummy ...

for cursor in (select * from t1)
loop

   select the_fields into dummy 
   from table2 b where a.fiels1 = cursor.field1;

end loop;

上面的sql是等於在loop裡多join 了無數次，但改成底下就只剩join一次了

create table dumy as
select  a.*, b.* 
from table1 a
left join table2  on a.field1 = b.field1

可是當你的SQL相當複雜的話，通常你就會偷懶想用cursor來達成，但好好思考一下是否造成效能問題，底下是一下建議：

• You should do it in a single SQL statement if at all possible.



• If you cannot do it in a single SQL Statement, then do it in PL/SQL.



• If you cannot do it in PL/SQL, try a Java Stored Procedure.



• If you cannot do it in Java, do it in a C external procedure.



• If you cannot do itn a C external routine, you might want to seriously

參考

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:73891904732164


http://stackoverflow.com/questions/2250196/select-into-using-oracle