優化oraclesql
『壹』 oracle sql優化涉及哪些原理
B 樹索引（B-Tree索引）
B樹索引是我們在oracle資料庫中最常用的索引，在詳細介紹訪問方法之前，我們看一下B-TREE索引的結構（圖片來源網路）
oracle的B樹索引就好像一顆長到的樹，他包含兩種類型，一種是索引分支塊（根節點塊，分支節點塊）一種是索引葉子塊（葉子節點塊）。分節點用來搜索，葉子節點用來存儲數據。根節點存儲索引的低層分支節點的數據。 由於所有的葉子節點均會自動的存儲成相同的深度，所以稱為「平 衡樹索引」， 故此，從任何葉子處檢索數據消耗的時間都是相同的。
對於分支節點塊（包括根節點塊）來說，其所包含的索引條目都是按照順序排列的（預設是升序排列，也可以在創建索引時指定為降序排列）。每個索引條目（也可以叫做每條記錄）都具有兩個欄位。第一個欄位表示當前該分支節點塊下面所鏈接的索引塊中所包含的最小鍵值；第二個欄位為四個位元組，表示所鏈接的索引塊的地址，該地址指向下面一個索引塊。 比如從上圖一可以看到，對於根節點塊來說，包含三條記錄，分別為（0 B1）、（500 B2）、（1000 B3），它們指向三個分支節點塊。其中的0、500和1000分別表示這三個分支節點塊所鏈接的鍵值的最小值。而B1、B2和B3則表示所指向的三個分支節點塊的地址。在一個分支節點塊中 所能容納的記錄 行數由數據塊大小以及索引鍵值的長度決定。
對於葉子節點塊來說，其所包含的索引條目與分支節點一樣，都是按照順序排列的（預設是升序排列，也可以在創建索引時指定為降序排列）。每個索引條目（也可以叫做每條記錄）也具有兩個欄位。第一個欄位表示索引的鍵值，對於單列索引來說是一個值；而對於多列索引來說則是多個值組合在一起的。第二個欄位表示鍵值所對應的記錄行的ROWID，該ROWID是記錄行在表裡的物理地址。
當用戶創建索引時，Oracle 取得所有被索引列的數據並進行排序，之後將排序後索引值和與此值相對應的 rowid 按照從下到上的順序載入到索引中。例如，以下語句：
[html] view plain print?在CODE上查看代碼片派生到我的代碼片
CREATE INDEX employees_last_name ON employees(last_name);
Oracle 先將 employees 表按 last_name 列排序，再將排序後的 列及相應的 rowid 按從下到上的順序載入到索引中。使用此索引時，Oracle 可以快速地搜索已排序的 last_name 值，並使用相應的 rowid 去定位包含用戶所查找的 last_name 值的數據行。
在一個平衡樹索引中，最底層的索引塊(葉塊)存儲了被索引的數據值，以及對應的
rowid。葉塊之間以雙向鏈表的形式相互連接。位於葉塊之上的分支塊中包含了
指向下層索引塊的指針。
接下來介紹一個索引查詢的流程，從上往下，第一層為根節點，第二層為分支節點，第三層為葉子節點（包含了列值和rowid）。比如我們的條件為where=29，(補充說明如果被索引的列存儲的是字元數據，那麼索引值為這些字元數據在當前資料庫字元集中的二進制值)就從跟節點開始查詢，29在0-500中，指向分支節點最左邊第一個分支節點塊（也就是B1），就B1中去找，發現29在0-200中，指向葉子節點的L1，於是在L1中找到29的值和響應的rowid。如果只查找索引列的值，就不用根據rowid去表中查找了，如果還要查找值29這行的其他列的值就得根據rowid去表裡查查詢（這個過程叫做回表查詢）。葉子節點還有個雙向鏈表（如圖）。在通過索引進行范圍掃描時會起作用，比如要查找值29-700，如果當查找到值29的時候，不就會再從跟節點開始查找其他的值，而是根據本葉子節點鏈表的指向去查找其他的值。
『貳』 Oracle資料庫SQL語句優化
dml操作（insert
,
delete
,
update）之後，一定要使用commit或rollback命令來結束該事務，否則直接關閉sql
plus，資料庫默認rollback之前未提交的事務，所以今天你在select的時候
會顯示未選定行。
而手動輸入commit,rollback
這類的命令，是顯示的提交事務（完成事務）。
如果在dml操作之後未及時顯示的提交，而是又進行了ddl操作（create
alter...）,則資料庫會隱式的提交之前未完成的事務。
所以
下次一定要注意哦~~
『叄』 怎麼使用 基於oracle的sql優化
（1） 選擇最有效率的表名順序(只在基於規則的優化器中有效)：
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，FROM子句中寫在最後的表(基礎表 driving table)將被最先處理，在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.
（2） WHERE子句中的連接順序．：
ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
（3） SELECT子句中避免使用 『 * 『：
ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間
（4） 減少訪問資料庫的次數：
ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀數據塊等；
（5） 在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索數據量 ,建議值為200
（6） 使用DECODE函數來減少處理時間：
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.
（7） 整合簡單,無關聯的資料庫訪問：
如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關系)
（8） 刪除重復記錄：
最高效的刪除重復記錄方法 ( 因為使用了ROWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
（9） 用TRUNCATE替代DELETE：
當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 如果你沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況) 而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的信息.當命令運行後,數據不能被恢復.因此很少的資源被調用,執行時間也會很短. (譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)
（10） 盡量多使用COMMIT：
只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會因為COMMIT所釋放的資源而減少:
COMMIT所釋放的資源:
a. 回滾段上用於恢復數據的信息.
b. 被程序語句獲得的鎖
c. redo log buffer 中的空間
d. ORACLE為管理上述3種資源中的內部花費
（11） 用Where子句替換HAVING子句：
避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷. (非oracle中)on、where、having這三個都可以加條件的子句中，on是最先執行，where次之，having最後，因為on是先把不符合條件的記錄過濾後才進行統計，它就可以減少中間運算要處理的數據，按理說應該速度是最快的，where也應該比having快點的，因為它過濾數據後才進行sum，在兩個表聯接時才用on的，所以在一個表的時候，就剩下where跟having比較了。在這單表查詢統計的情況下，如果要過濾的條件沒有涉及到要計算欄位，那它們的結果是一樣的，只是where可以使用rushmore技術，而having就不能，在速度上後者要慢如果要涉及到計算的欄位，就表示在沒計算之前，這個欄位的值是不確定的，根據上篇寫的工作流程，where的作用時間是在計算之前就完成的，而having就是在計算後才起作用的，所以在這種情況下，兩者的結果會不同。在多表聯接查詢時，on比where更早起作用。系統首先根據各個表之間的聯接條件，把多個表合成一個臨時表後，再由where進行過濾，然後再計算，計算完後再由having進行過濾。由此可見，要想過濾條件起到正確的作用，首先要明白這個條件應該在什麼時候起作用，然後再決定放在那裡
（12） 減少對表的查詢：
在含有子查詢的SQL語句中,要特別注意減少對表的查詢.例子：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
（13） 通過內部函數提高SQL效率.：
復雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的
（14） 使用表的別名(Alias)：
當在SQL語句中連接多個表時, 請使用表的別名並把別名前綴於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.
（15） 用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在許多基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率. 在子查詢中,NOT IN子句將執行一個內部的排序和合並. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS.
例子：
（高效）SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND EXISTS (SELECT 『X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 『MELB')
(低效)SELECT * FROM EMP (基礎表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 『MELB')
（16） 識別'低效執行'的SQL語句：
雖然目前各種關於SQL優化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法：
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
（17） 用索引提高效率：
索引是表的一個概念部分,用來提高檢索數據的效率，ORACLE使用了一個復雜的自平衡B-tree結構. 通常,通過索引查詢數據比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE優化器將使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.。那些LONG或LONG RAW數據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.。定期的重構索引是有必要的.：
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
（18） 用EXISTS替換DISTINCT：
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足後,立刻返回結果. 例子：
(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
(高效):
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 『X'
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
（19） sql語句用大寫的；因為oracle總是先解析sql語句，把小寫的字母轉換成大寫的再執行
（20） 在java代碼中盡量少用連接符「＋」連接字元串！
（21） 避免在索引列上使用NOT 通常，
我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的影響. 當ORACLE」遇到」NOT,他就會停止使用索引轉而執行全表掃描.
（22） 避免在索引列上使用計算．
WHERE子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．
舉例:
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
（23） 用>=替代>
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄.
（24） 用UNION替換OR (適用於索引列)
通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = 「MELBOURNE」
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = 「MELBOURNE」
如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面.
（25） 用IN來替換OR
這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的．
低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);
（26） 避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引．對於單列索引，如果列包含空值，索引中將不存在此記錄. 對於復合索引，如果每個列都為空，索引中同樣不存在此記錄.如果至少有一個列不為空，則記錄存在於索引中．舉例: 如果唯一性索引建立在表的A列和B列上, 並且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入). 然而如果所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等於空. 因此你可以插入1000 條具有相同鍵值的記錄,當然它們都是空! 因為空值不存在於索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引.
低效: (索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
（27） 總是使用索引的第一個列：
如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優化器才會選擇使用該索引. 這也是一條簡單而重要的規則，當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引
（28） 用UNION-ALL 替換UNION ( 如果有可能的話)：
當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合並, 然後在輸出最終結果前進行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高. 需要注意的是，UNION ALL 將重復輸出兩個結果集合中相同記錄. 因此各位還是要從業務需求分析使用UNION ALL的可行性. UNION 將對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內存. 對於這塊內存的優化也是相當重要的. 下面的SQL可以用來查詢排序的消耗量
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
（29） 用WHERE替代ORDER BY：
ORDER BY 子句只在兩種嚴格的條件下使用索引.
ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序.
ORDER BY中所有的列必須定義為非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能並列.
例如:
表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
低效: (索引不被使用)
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
高效: (使用索引)
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0
（30） 避免改變索引列的類型.:
當比較不同數據類型的數據時, ORACLE自動對列進行簡單的類型轉換.
假設 EMPNO是一個數值類型的索引列.
SELECT … FROM EMP WHERE EMPNO = 『123'
實際上,經過ORACLE類型轉換, 語句轉化為:
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(『123')
幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變.
現在,假設EMP_TYPE是一個字元類型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
這個語句被ORACLE轉換為:
SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因為內部發生的類型轉換, 這個索引將不會被用到! 為了避免ORACLE對你的SQL進行隱式的類型轉換, 最好把類型轉換用顯式表現出來. 注意當字元和數值比較時, ORACLE會優先轉換數值類型到字元類型
（31） 需要當心的WHERE子句:
某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子.
在下面的例子里, (1)『!=' 將不使用索引. 記住, 索引只能告訴你什麼存在於表中, 而不能告訴你什麼不存在於表中. (2) 『||'是字元連接函數. 就象其他函數那樣, 停用了索引. (3) 『+'是數學函數. 就象其他數學函數那樣, 停用了索引. (4)相同的索引列不能互相比較,這將會啟用全表掃描.
（32） a. 如果檢索數據量超過30%的表中記錄數.使用索引將沒有顯著的效率提高.
b. 在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數量級上的區別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!
（33） 避免使用耗費資源的操作:
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎
執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的資料庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強
（34） 優化GROUP BY:
提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結果但第二個明顯就快了許多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = 『PRESIDENT'
OR JOB = 『MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = 『PRESIDENT'
OR JOB = 『MANAGER'
GROUP JOB
『肆』 Oracle SQL 優化
首先叢業務角度講，終端不注冊就不可能有結算記錄。
所以終端注冊日不為空（CREATETIME is not null）應該被體現
select t2.TERMINALID,t2.TERMINALNAME,T1.CREATEDATE,NVL(t1.AMOUNT,0)
from 商戶終端表 t2
join 結算記錄 t1
on t1.TERMINALID = t2.TERMINALID
where
t2.MERCHANTID = 'xxxx'
and
t2.CREATETIME is not null
這不就完了么，干嗎寫那麼多子查詢呢？
『伍』 oracle sql優化
這種union沒有優化的可能。
只能從具體的四個表的sql語句上優化了。
在每個表的emp，t.czsj上加聯合索引，然後改寫一下condition的寫法：
TO_CHAR(t.czsj, 'YYYY-MM-DD') >= '2014-12-01'
and TO_CHAR(t.czsj, 'YYYY-MM-DD') <= '2014-12-31'
改為
t.czsj > to_date('2014-12-01','yyyy-mm-dd') and t.czsj<= to_date('2014-12-30','yyyy-mm-dd')
『陸』 做過哪些oracle sql優化
一：使用where少使用having;
二：查兩張以上表時，把記錄少的放在右邊；
三：減少對表的訪問次數；
四：有where子查詢時，子查詢放在最前；
五：select語句中盡量避免使用*（執行時會把*依次轉換為列名）；
六：盡量多的使用commit；
七：Decode可以避免重復掃描相同的記錄或重復連接相同的表；
八：通過內部函數也可提高sql效率；
九：連接多個表時，使用別名並把別名前綴於每個欄位上；
十：用exists代替in
十一：not exists代替 not in（not in 字句將執行一個內部的排序和合並，任何情況下，not in是最低效的，子查詢中全表掃描了。為了避免使用not in，可以改寫成outer joins或not exists）;
十二：表連接比exists更高效；
十三：用exists替換distinct
『柒』 Oracle資料庫該如何著手優化一個SQL
SQL Profile是10g中的新特性，作為自動SQL調整過程的一部分。SQL Profile是一個對象，它包含了可以幫助查詢優化器為一個特定的SQL語句找到高效執行計劃的信息。這些信息包括執行環境、對象統計和對查詢優化器所做評估的修正信息。
它的最大優點之一就是在不修改SQL語句和會話執行環境的情況下影響查詢優化器的決定。SQL Profile中包含的並非單個執行計劃的信息，SQL Profile不會固定一個SQL語句的執行計劃。當表的數據增長或者索引創建、刪除，使用同一個SQL Profile的執行計劃可能會改變，而存儲在SQL Profile中的信息會繼續起作用。所以，經過一段很長的時間之後，它的信息有可能會過時，需要重新生成。
『捌』 Oracle游標sql語句代碼塊的優化
游標操作的優化：
-- 有一個表格，存儲的是用戶的名字，將 emp 表中所有的用戶名轉換成小寫，再寫入這個表格
create table ename_emp(
ename varchar2(50)
);
-- 下面這個游標的操作，需要 43s時間才能完成
declare
begin
for i in (select ename from emp_liebiao) loop
insert into ename_emp values(lower(i.ename));
commit;
end loop;
end;
-- 因為游標是以行為單位進行數據的操作的，所有游標的效率是比較慢的，而且游標需要消耗的內存也是比較多的，我們需要將游標以行進行操作的方式，修改成一次性操作所有數據的批量的方式。
批量操作在游標裡面 叫做 bulk collect
第一步，創建一個表類型
type 表類型的名字 is table of 表名.列名 %type;
變數名 表類型的名字;
第三步，創建一個游標，讀取某個查詢的結果
cursor 游標名字 is select 查詢語句 ;
第四步，打開游標
open 游標名字;
第五步，捕獲游標的數據，將內容給到表類型的變數進行保存
fetch 游標名字 bulk collect into 變數名字 ;
第六步，使用 forall 語句，對數據進行批量的操作
forall i in 變數 .first .. 變數 .last DML 語句操作 ;
第七步，關閉游標
close 游標名字 ;
declare
-- 創建表類型
type biao is table of emp_liebiao.ename%type;
b biao;
-- 創建游標
cursor m is select ename from emp_liebiao;
begin
-- 打開游標
open m;
-- 將游標的內容批量的給到變數
fetch m bulk collect into b;
-- 使用forall批量修改數據
forall i in b.first .. b.last insert into ename_emp values(lower(b(i)));
commit;
-- 關閉游標
close m;
end;
練習：批量修改用戶的編號，讓編號+工資等級+部門，形成一個新的編號，存入下面的表格中，使用 bulk collect 來實現。
2000以下是 C ，2000-3000是 B ，3000以上是 A ，
例如 SMITH ， 新編號應該是 7369_C_20
create table empno_emp(
empno varchar2(50)
);
declare
type biao is table of emp_liebiao%rowtype;
b biao;
cursor m is select * from emp_liebiao;
begin
open m;
fetch m bulk collect into b;
forall i in b.first..b.last
insert into empno_emp values(
b(i).empno||'_'||decode(sign(b(i).sal-2000)+sign(b(i).sal-3000),-2,'C',2,'A','B')||'_'||b(i).deptno
);
commit;
close m;
end;
『玖』 oracle sql 優化
優化需要考慮到各個表的數據量及是否有重復記錄。sql語句中表關聯的前後順序也可能會影響最終的查詢效率。因不知樓主提供的各個表的數據量情況。初步優化結果如下：
第一步：
create table new_temp as
select distinct s.seal_holder_id
from seal s, seal_key_bind skb, seal_key sk
where sk.del = 'Y'
and sk.key_state = '0'
and sk.is_format = 'N'
and sk.is_exist = 'Y'
and sk.is__integration = 'Y'
and st.del = 'Y'
and skb.del = 'Y'
and s.del = 'Y'
and s.seal_state = '0'
and s.seal_id = skb.seal_id
and skb.seal_key_id = sk.seal_key_id
and sk.hardware_id = '0A03180A0C232A01';
第二步：
select st.seal_type_id,
u.corp_ch_name,
sa.seal_apply_id,
sp.template_figure,
sp.seal_template_type,
sp.diameter,
sp.axis_long,
sp.axis_short,
sp.side,
sp.seal_content,
sp.seal_pattern_content,
st.seal_type_name,
sa.seal_index,
u.corp_en_name,
sp.seal_color,
sp.seal_font
from seal_parameter sp,
users u,
seal_apply sa,
seal_type st,
new_temp s
where sa.del = 'Y'
and sa.seal_open = 'Y'
and u.del = 'Y'
and u.is_auditing = '1'
and u.is_start = 'Y'
and sp.del = 'Y'
and u.users_type = 'D'
and u.users_id = sa.users_id
and sa.seal_holder_id = s.seal_holder_id
and sa.seal_apply_id = sp.seal_apply_id
and sa.seal_type_id = st.seal_type_id
第一步通過建立臨時表 new_temp，第二步以sa表為主表關聯其它的表，這樣解析的時候表之間的關聯關系為hash join可顯著提高查詢速度。