Oracle sql最佳化樣本

標籤：緩衝   方法   dual   sele   select   col   最佳化   cut   ora   

1. 迴圈插入1到10w數值

 1 create or replace procedure proc_test  2 as 3 begin 4   for i in 1..100000 5     loop 6       execute immediate 7       ‘insert into t values(‘ || i || ‘)‘; 8       commit; 9     end loop;10 end;11 /

2. 使用變數綁定，減少sql解析

 1 create or replace procedure proc_test  2 as 3 begin 4   for i in 1..100000 5     loop 6       execute immediate 7       ‘insert into t values(:x) ‘ using i; ---使用變數，sql只需解析一次，而第一種寫法，需要解析1w次。 8       commit; 9     end loop;10 end;11 /

3. 使用靜態SQL，編譯過程即完成解析，而動態SQL是在執行過程中解析的

 1 create or replace procedure proc_test  2 as 3 begin 4   for i in 1..100000 5     loop 6       --execute immediate 刪除該行, execute immediate是一種動態SQL寫法, 常用於表名，欄位名是變數，入參的情況，但這裡表名是已知的，直接用靜態SQL即可，
         --靜態SQL會自動使用綁定變數, 而且是在編譯過程就解析好了，而動態SQL是在執行過程中解析的。 7       insert into t values(i);  8       commit; 9     end loop;10 end;11 /

4. 批量commit.

 1 create or replace procedure proc_test  2 as 3 begin 4   for i in 1..100000 5     loop 6       insert into t values(i); 7     end loop; 8     commit; --批量提交 9 end;10 /

5. 寫成一條sql，由原來過程一條一條插入，變成一個集合的概念，一整批寫入DATA BUFFER區。

1 insert into t select rownum from dual connect by level<=1000000;2 commit;

6. 直接路徑方式插入資料，insert into t select ...是將資料先寫入DATA BUFFER中，再刷到磁碟裡，而create table t ...跳過了資料緩衝區，直接寫入磁碟。

這種方式一般用於資料移轉。

1 create table t as select rownum x from dual connect by level<=1000000;

 7. 還有一種方法，在多CPU機器上，關閉日誌nologging，並且設定parallel 16 表示用到16個CPU。

不過該方法會佔用大量CPU資源，比較影響其他應用，使用時要三思而後行。

1 create table t nologging parallel 16 2 as select rownum x from dual connect by level<=1000000;

 

Oracle sql最佳化樣本