ORACLE筆記之SQL語句的最佳化，oraclesql語句

ORACLE筆記之SQL語句的最佳化，oraclesql語句

  SQL語句的最佳化總結如下

 

sql語句的最佳化可以按照如下六個步驟進行：

1. 合理使用索引
2. 避免或者簡化排序
3. 消除對大表的掃描
4. 避免複雜的萬用字元匹配
5. 調整子查詢的效能
6. EXISTS和IN運算子

下面我就按照上面這六個步驟分別進行總結：

• 1-------------------------------------合理使用索引

  首先要明確建立索引的根本目的是:提高查詢效率；

  使用原則如下：

1.1、在條件運算式中經常用到的不同值較多的列上建立索引

1.2、在不值少的列上不要建立索引，或者位元影像索引

      例如：在員工表的性別列上，只有“男”和“女”兩個不同值，如果建立索引，不但不會提高效能

1.3、在經常進行串連，但是沒有指定為外鍵的列上建立索引

      根據我的經驗，在實際開發中，資料庫的設計是不會建立索引的，都是通過在外鍵上面建立索引進行關聯，這樣效能更好；

1.4.在頻繁進行排序或分組的列上建立索引

1.5.如果待排序的列有多個，可以建立複合式索引

         例如：order by D,E 那要注意：在建立索引時，注意D列和E列和排序後面的順序要相同，不然不管是那種資料庫都不會用到這個索引；

1.6.如果條件運算式中經常對某個列應用某個函數後指定查詢條件，則應建立函數索引

備忘：對於Oracle中怎樣建立索引，有多少索引，以及應用情境，在接下來的文章中我會加進來

 

• 使用索引需要注意的地方

 下面情況的SQL，不會用到列上的索引，應盡量避免使用：

 1.  存在資料類型的隱形轉換時，例如

 

Sql代碼  

1. select * from emp where empno = ‘7369’;  

 

    注意上面的empno列是number類型的，這樣轉化就不會用到這個索引

 

2. 列上有數學運算時，例如

 

Sql代碼  

1. Select * from emp where sal*2 <1000;  

 

  這是因為在索引上只有sal的值，沒有sal*2的值，應該改成

 

Sql代碼  

1. select * from emp where sal<1000/2 ;  

 

 

 3.使用不等於(<>)運算時 例如

 

 

Sql代碼  

1. Select * from emp where deptno <>10;  

 

 4.使用substr字串函數時，例如

 

 

Sql代碼  

1. Select * from emp where substr(ename,1,3)=‘SMI’;  

 5.‘%’萬用字元在第一個字元時，例如

 

 

Sql代碼  

1. Select * from emp where ename like’%th’;  

 

 6.字串串連( || )時，例如

 

 

Sql代碼  

1. <span style="font-size: 16px;">Select * from emp where ename || ‘abc’ = ‘SMITHabc’;</span>  

 

 

 

 

• 2-------------------------------------避免或者簡化排序

2.1 應當簡化或避免對大型表進行重複的排序,以下情況，oracle是會預設對他排序的情況

• SQL中包含Group By子句
• SQL中包含Order By子句
• SQL中包含Distinct子句
• SQL中包含Minus或Union子句
• in子句中的SQL子查詢

2.2 以下情況不能有效地利用索引

1.待排序列沒有全部建立索引,例如

 

Sql代碼  

1. order by D,E 而在表中只在D列上建立了索引  
2. Group by 或者 Order by 子句中列的順序與索引的列的順序不同  

 2.串連查詢時排序的列來自不同的表(索引不能跨表)

 

 避免或簡化排序的總結如下：為了避免不必要的排序，就要正確地增建索引，合理地合并資料庫表。如果排序不可避免，那麼應當試圖簡化它，如縮小排序的列的範圍等

 

 

• 3-------------------------------------消除對大表的掃描

3.1. 在串連查詢中，對錶的順序存取可能對查詢效率產生致命的影響。避免這種情況的主要方法就是對串連的列進行索引。例如有兩個表，學生表(學號、姓名、年齡…)和選課表(學號、課程號、成績)。如果兩個表要做串連，就要在”學號”這個串連欄位上建立索引。

 

 

3.2 使用並集來避免順序存取。儘管在所有的檢查列上都有索引，但某些形式的 where 子句強迫資料庫使用順序存取。例如

 

Sql代碼  

1. Select * from abc where a>10 or b<10;  

 

       儘管我們在B和C列上都建立了索引，但是在上面語句中，最佳化器還是使用順序存取方式掃描整個表。因為這個語句要檢索的是分離的行的集合，可改為如下語句

 

 

Sql代碼  

1. Select * from abc where a>10  
2. Union  
3. Select * from abc where b<10  

 

 

 

• 4-------------------------------------避免困難的萬用字元匹配

複雜的通配可能導致耗時的查詢。例如

 

Sql代碼  

1. Select * from customer where zipcode like ’98_ _ _’;  

 

 即使在zipcode欄位上建立了索引，在這種情況下也還是採用順序掃描的方式。應改成

 

 

Sql代碼  

1. Select * from customer   
2. where zipcode >=’98000’ and zipcode <‘ 99000’  

 

 

 

 

• 5-------------------------------------調整子查詢的效能

子查詢包括兩種，關聯子查詢、非關聯子查詢，下面分別對這兩種進行分析

5.1 非關聯子查詢

       非關聯子查詢時子查詢只會執行一次，而且結果集是已經排序完畢的，並儲存在一個Oracle的臨時段中，其中的每一個記錄在返回時都會被父查詢所引用。在子查詢返回大量記錄的情況下，將這些結果集排序，以及將臨時資料區段進行排序會增加大量的系統開銷。例如

 

Sql代碼  

1. select emp_name from emp_number where emp_id in (select emp_id from emp_func);  

 

 

 

5.2 關聯子查詢

解釋一下，什麼是關聯子查詢，就是子查詢中的條件用到了父查詢中的列，例如：

Sql代碼  

1. select emp_name from emp_number where emp_id in (select emp_id from emp_func where emp_number.emp_id = emp_func.emp_id);  

 

      對返回到父查詢的記錄來說，子查詢會每行執行一次。因此必須保證子查詢儘可能用到索引。關聯子查詢的系統開銷更高。

 對於子查詢我的應用程式原則就是：查詢嵌套的層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那以要在子查詢中過濾掉儘可能多的行。

 

• 6-------------------------------------EXISTS和IN運算子

6.1 帶IN的關聯子查詢是多餘的，因為IN子句和子查詢中相關操作的功能是一樣的。例如

Sql代碼  

1. Select emp_name from emp_member where emp_id in (select emp_id from emp_func where emp_member.emp_id = emp_func.emp_id);  

 

 

6.2 為非關聯子查詢指定EXISTS子句是不適當的，因為這樣會產生笛卡爾積）。例如

Sql代碼  

1. Select emp_name from emp_member  
2. Where exists(select emp_id from emp_func);  

 

 

6.3 盡量不要使用NOT IN 子句，雖然使用MINUS子句要進行兩次查詢，使用MINUS子句依然比 NOT IN 子句快，這種情況你應該這麼寫

Sql代碼  

1. Select emp_name from emp_member where emp_id   
2. in (select  emp_id from emp_member   
3. minus   
4. Select emp_id from emp_func where func_id like ’81%’ );  

 

 

 

 

最後測試索引的效能，下面這個例子是在網上看到的，我直接拿過來了，我覺得非常經典

1.建立dumpy表，然後向dumpy表中添加1000萬行記錄，其中ID列是有序的整數，Name是隨機的字串，Rand列是隨機的數值

測試環境Oracle，建立表的文法如下：

Sql代碼  

1. create table dumpy  
2. (  
3.    id       number(10),  
4.    name     varchar2(10),  
5.    rand     number(10,2)  
6. );  

 

添加資料的文法如下：

Sql代碼  

1. declare  
2.   recordcount integer := 10000000;  -- 1000萬條記錄  
3. begin  
4.   for i in 1..recordcount loop  
5.     insert into dumpy(id, name, rand)  
6.     values ( i, dbms_random.string('x', 8),  
7.              abs(dbms_random.random) / 100.0);  
8.     if mod(i, 1000) = 0 then  
9.       commit;                          -- 每插入1000條提交  
10.     end if;  
11.   end loop;  
12. end;  

 資料添加成功後，查詢Rand值介於1000到2000之間的行。添加索引，比較最佳化前後查詢速度的變化。

  提示:執行命令 SET TIMING ON,可以顯示每次語句執行時間

oracle SQL語句最佳化

如果你這個表的資料量很大，並且經常要做這樣的查詢建議你對該表做分區，並且建立分區索引。
你這個SQL已經算是簡化的了，資料量大了，基本無論怎麼調都會很慢。
這通常是設計表的時候沒有考慮到應用的需求。
 
oracle 資料庫怎最佳化sql 語句

Oracle內建一個SQL分析器，簡單相對獨立的單段SQL語句可以用它進行分析，然後它能給你一個分析結果，你可以根據結果適當添加索引。這個SQL語句最佳化的最有效果的方法之一。選中你要最佳化的語句F5快速啟動分析功能。

除此之外的最佳化方法比如減少視圖、比如在Where條件裡減少使用函數等，方法挺多的。